Home Sample Files e-Books Books Wholesale Forums & Support Contact
2016 2013 2010 2007 Apple Mac

Learn Excel 2013 Expert Skills With The Smart Method

Before you start:
When you need help:
Read: How to use the course Simple one-page guide. Search Find a lesson teaching a specific Excel feature
Download the entire e-book Download the entire e-Book.
Download the sample files Sample files save a lot of typing. Forums Ask one of our Excel experts.
Session 1: Tables and Ranges
Session 1 Introduction
Lesson 1-1: Check that your Excel version is up to date 3m 33s
Lesson 1-2: Change the Office Theme 2m 51s
Lesson 1-3: Apply a simple filter to a range 7m 40s
Lesson 1-4: Apply a top 10 and custom filter to a range 5m 48s
Lesson 1-5: Apply an advanced filter with multiple OR criteria 8m 2s
Lesson 1-6: Apply an advanced filter with complex criteria 4m 44s
Lesson 1-7: Apply an advanced filter with function-driven criteria 14m 24s
Lesson 1-8: Extract unique records using an advanced filter 4m 41s
Lesson 1-9: Add totals using Quick Analysis 3m 42s
Lesson 1-10: Add percentage and running totals using Quick Analysis 5m 10s
Lesson 1-11: Convert a range into a table and add a total row 5m 47s
Lesson 1-12: Format a table using table styles and convert a table into a range 12m 9s
Lesson 1-13: Create a custom table style 4m 49s
Lesson 1-14: Sort a range or table by rows 5m 5s
Lesson 1-15: Sort a range by columns 5m 18s
Lesson 1-16: Sort a range or table by custom list 2m 56s
Lesson 1-17: Name a table and create an automatic structured table reference 8m 52s
Lesson 1-18: Create a manual structured table reference 4m 53s
Lesson 1-19: Use special items in structured table references 4m 35s
Lesson 1-20: Understand unqualified structured table references 8m 0s
Session 1 Exercise
Session 1 Exercise Answers
Session 2: Data Integrity, Subtotals and Validations
Session 2 Introduction
Lesson 2-1: Split fixed width data using Text to Columns 9m 11s
Lesson 2-2: Split delimited data using Text to Columns 4m 11s
Lesson 2-3: Automatically subtotal a range 9m 30s
Lesson 2-4: Create nested subtotals 4m 44s
Lesson 2-5: Consolidate data from multiple data ranges 5m 44s
Lesson 2-6: Use data consolidation to generate quick subtotals from tables 6m 35s
Lesson 2-7: Validate numerical data 7m 26s
Lesson 2-8: Create user-friendly messages for validation errors 6m 10s
Lesson 2-9: Create data entry input messages 3m 13s
Lesson 2-10: Add a formula-driven date validation and a text length validation 6m 33s
Lesson 2-11: Add a table-based dynamic list validation 5m 54s
Lesson 2-12: Use a formula-driven custom validation to enforce complex business rules 6m 10s
Lesson 2-13: Remove duplicate values from a table 3m 44s
Lesson 2-14: Use a custom validation to add a unique constraint to a column 6m 58s
Session 2 Exercise
Session 2 Exercise Answers
Session 3: Advanced Functions
Session 3 Introduction
Lesson 3-1: Understand Excel order of precedence rules and Excel Formula Evaluate 10m 56s
Lesson 3-2: Create an Excel SUM function using Formula AutoComplete 7m 28s
Lesson 3-3: Create an Excel PMT function using the Insert Function Dialog 9m 27s
Lesson 3-4: Use the Excel PV function and Excel FV function to value investments 6m 47s
Lesson 3-5: Create an Excel IF logical function 8m 40s
Lesson 3-6: Create an Excel SUMIF function and Excel COUNTIF function 6m 50s
Lesson 3-7: Understand Excel date serial numbers and custom date formats 11m 10s
Lesson 3-8: Use the Excel YEAR function and other common date functions 6m 2s
Lesson 3-9: Use the Excel DATEDIF function and YEARFRAC function 8m 51s
Lesson 3-10: Excel project management using date offsets 8m 12s
Lesson 3-11: Use the Excel DATE function to offset days, months and years 4m 40s
Lesson 3-12: Enter time values and perform basic time calculations 8m 8s
Lesson 3-13: Perform time calculations that span midnight 5m 26s
Lesson 3-14: Understand common time functions and convert date serial numbers to decimal values 10m 2s
Lesson 3-15: Use the TIME function to offset hours, minutes and seconds 6m 16s
Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria 12m 27s
Lesson 3-17: Understand calculation options (manual and automatic) 11m 19s
Lesson 3-18: Concatenate strings using the concatenation operator 8m 21s
Lesson 3-19: Use the TEXT function to format numerical values as strings 11m 34s
Lesson 3-20: Extract text from strings using the LEFT, RIGHT and MID functions 6m 36s
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions 12m 17s
Lesson 3-22: Use a VLOOKUP function for an exact lookup 7m 36s
Lesson 3-23: Use an IFERROR function to suppress error messages 4m 37s
Lesson 3-24: Use a VLOOKUP function for an inexact lookup 5m 36s
Session 3 Exercise
Session 3 Exercise Answers
Session 4: Using Names and the Formula Auditing Tools
Session 4 Introduction
Lesson 4-1: Automatically create single-cell range names 5m 56s
Lesson 4-2: Manually create single cell range names and named constants 8m 13s
Lesson 4-3: Use range names to make formulas more readable 5m 37s
Lesson 4-4: Automatically create range names in two dimensions 6m 58s
Lesson 4-5: Use intersection range names and the INDIRECT function 8m 44s
Lesson 4-6: Create dynamic formula-based range names using the OFFSET function 13m 41s
Lesson 4-7: Create table-based dynamic range names 7m 38s
Lesson 4-8: Create two linked drop-down lists using range names 9m 30s
Lesson 4-9: Understand the NUM, DIV0 and NAME error values 5m 18s
Lesson 4-10: Understand the VALUE, REF and NULL error values 4m 15s
Lesson 4-11: Understand background error checking and error checking rules 6m 1s
Lesson 4-12: Manually check a worksheet for errors 6m 21s
Lesson 4-13: Audit a formula by tracing precedents 5m 26s
Lesson 4-14: Audit a formula by tracing dependents 3m 39s
Lesson 4-15: Use the watch window to monitor cell values 3m 56s
Lesson 4-16: Use Speak Cells to eliminate data entry errors 7m 52s
Session 4 Exercise
Session 4 Exercise Answers
Session 5: Pivot Tables
Session 5 Introduction
Lesson 5-1: Create a pivot table 11m 14s
Lesson 5-2: Create a grouped pivot table 6m 13s
Lesson 5-3: Understand pivot table rows and columns 4m 39s
Lesson 5-4: Use an external Pivot Table data source 6m 31s
Lesson 5-5: Refresh a Pivot Table and understand the Pivot Table data cache 7m 39s
Lesson 5-6: Apply a simple filter and sort to a pivot table 7m 23s
Lesson 5-7: Use report filter fields 4m 1s
Lesson 5-8: Filter a pivot table visually using slicers 9m 16s
Lesson 5-9: Add a timeline control to a Pivot Table 4m 21s
Lesson 5-10: Use slicers to create a custom timeline 9m 12s
Lesson 5-11: Use report filter fields to automatically create multiple pages 5m 26s
Lesson 5-12: Format a pivot table using PivotTable styles 5m 42s
Lesson 5-13: Create a custom Pivot Table style 5m 5s
Lesson 5-14: Understand pivot table report layouts 4m 6s
Lesson 5-15: Add and remove subtotals and apply formatting to pivot table fields 5m 49s
Lesson 5-16: Display multiple summations within a single pivot table 4m 21s
Lesson 5-17: Add a calculated field to a pivot table 7m 25s
Lesson 5-18: Add a calculated item to a pivot table 5m 41s
Lesson 5-19: Group by text 5m 23s
Lesson 5-20: Group by date 5m 11s
Lesson 5-21: Group by numeric value ranges 5m 18s
Lesson 5-22: Show row data by percentage of total rather than value 3m 55s
Lesson 5-23: Create a pivot chart from a pivot table 5m 35s
Lesson 5-24: Embed multiple pivot tables onto a worksheet 8m 47s
Lesson 5-25: Use slicers to filter multiple pivot tables 9m 54s
Session 5 Exercise
Session 5 Exercise Answers
Session 6: The Data Model, OLAP, MDX and BI
Session 6 Introduction
Lesson 6-1: Understand primary and foreign keys 11m 27s
Lesson 6-2: Create a simple data model 6m 31s
Lesson 6-3: Understand OLAP, MDX and Business Intelligence 10m 17s
Lesson 6-4: Use the GETPIVOTDATA function 4m 31s
Lesson 6-5: Use the CUBEVALUE function to query an OLAP cube 5m 40s
Lesson 6-6: Convert CUBEVALUE functions to include MDX expressions 5m 48s
Lesson 6-7: Understand OLAP pivot table limitations 10m 52s
Lesson 6-8: Create an asymmetric OLAP pivot table using Named Sets 4m 57s
Lesson 6-9: Understand many-to-many relationships 11m 5s
Lesson 6-10: Create an OLAP pivot table using a many-to-many relationship 12m 47s
Session 6 Exercise
Session 6 Exercise Answers
Session 7: What If Analysis and Security
Session 7 Introduction
Lesson 7-1: Create a single-input data table 10m 41s
Lesson 7-2: Create a two-input data table 6m 56s
Lesson 7-3: Define scenarios 7m 12s
Lesson 7-4: Create a scenario summary report 2m 52s
Lesson 7-5: Use Goal Seek 4m 17s
Lesson 7-6: Use Solver 8m 16s
Lesson 7-7: Hide and unhide worksheets, columns and rows 10m 41s
Lesson 7-8: Create custom views 5m 51s
Lesson 7-9: Prevent unauthorized users from opening or modifying workbooks 5m 32s
Lesson 7-10: Control the changes users can make to workbooks 4m 41s
Lesson 7-11: Restrict the cells users are allowed to change 8m 41s
Lesson 7-12: Allow different levels of access to a worksheet with multiple passwords 6m 54s
Lesson 7-13: Create a digital certificate 8m 59s
Lesson 7-14: Add an invisible digital signature to a workbook 5m 27s
Lesson 7-15: Add a visible digital signature to a workbook 4m 15s
Session 7 Exercise
Session 7 Exercise Answers
Session 8: The Internet, Objects and Workgroups
Session 8 Introduction
Lesson 8-1: Publish a worksheet as a single web page 8m 6s
Lesson 8-2: Publish multiple worksheets as a web site 5m 36s
Lesson 8-3: Hyperlink to worksheets and ranges 6m 36s
Lesson 8-4: Hyperlink to other workbooks and the Internet 6m 57s
Lesson 8-5: Hyperlink to an e-mail address and enhance the browsing experience 4m 39s
Lesson 8-6: Execute a web query 6m 16s
Lesson 8-7: Embed an Excel worksheet object into a Word document 5m 2s
Lesson 8-8: Embed an Excel chart object into a Word document 5m 11s
Lesson 8-9: Link an Excel worksheet to a Word document 7m 31s
Lesson 8-10: Understand the three different ways to share a document 5m 54s
Lesson 8-11: Share a workbook using the lock method 5m 1s
Lesson 8-12: Share a workbook using the merge method 9m 37s
Lesson 8-13: Share a workbook on a network 8m 13s
Lesson 8-14: Accept and reject changes to shared workbooks 6m 33s
Session 8 Exercise
Session 8 Exercise Answers
Session 9: Forms and Macros
Session 9 Introduction
Lesson 9-1: Add group box and option button controls to a worksheet form 16m 38s
Lesson 9-2: Add a combo box control to an Excel worksheet form 4m 19s
Lesson 9-3: Set form control cell links 8m 23s
Lesson 9-4: Connect result cells to a form 6m 32s
Lesson 9-5: Add a check box control to an Excel worksheet form 3m 44s
Lesson 9-6: Use check box data in result cells 9m 45s
Lesson 9-7: Add a temperature gauge chart to a form 12m 27s
Lesson 9-8: Add a single input data table to a form 7m 46s
Lesson 9-9: Improve form appearance and usability 29m 51s
Lesson 9-10: Understand macros and VBA 7m 19s
Lesson 9-11: Record an Excel macro with absolute references 9m 58s
Lesson 9-12: Understand macro security 5m 16s
Lesson 9-13: Implement macro security 10m 53s
Lesson 9-14: Understand Trusted Documents 6m 24s
Lesson 9-15: Record a macro with relative references 7m 0s
Lesson 9-16: Use shapes to run macros 9m 21s
Lesson 9-17: Run a macro from a button control 10m 45s
Lesson 9-18: Show and hide Ribbon tabs 4m 31s
Lesson 9-19: Add custom groups to standard Ribbon tabs 9m 24s
Lesson 9-20: Create a custom Ribbon tab 10m 27s
Session 9 Exercise
Session 9 Exercise Answers