Home Sample Files e-Books Books Wholesale Forums & Support Search Contact
Login
Basic Skills
Essential Skills Expert Skills
2016 2013 2010 2007 Apple Mac

Learn Excel 2016 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
Lesson 1-2: Change the Office Theme
Lesson 1-3: Apply a simple filter to a range
Lesson 1-4: Apply a top 10 and custom filter to a range
Lesson 1-5: Apply an advanced filter with multiple OR criteria
Lesson 1-6: Apply an advanced filter with complex criteria
Lesson 1-7: Apply an advanced filter with function-driven criteria
Lesson 1-8: Extract unique records using an advanced filter
Lesson 1-9: Add totals using Quick Analysis
Lesson 1-10: Add percentage and running totals using Quick Analysis
Lesson 1-11: Convert a range into a table and add a total row
Lesson 1-12: Format a table using table styles and convert a table into a range
Lesson 1-13: Create a custom table style
Lesson 1-14: Sort a range or table by rows
Lesson 1-15: Sort a range by columns
Lesson 1-16: Sort a range or table by custom list
Lesson 1-17: Name a table and create an automatic structured table reference
Lesson 1-18: Create a manual structured table reference
Lesson 1-19: Use special items in structured table references
Lesson 1-20: Understand unqualified structured table references
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
Lesson 2-2: Split delimited data using Text to Columns
Lesson 2-3: Automatically subtotal a range
Lesson 2-4: Create nested subtotals
Lesson 2-5: Consolidate data from multiple data ranges
Lesson 2-6: Use data consolidation to generate quick subtotals from tables
Lesson 2-7: Validate numerical data
Lesson 2-8: Create user-friendly messages for validation errors
Lesson 2-9: Create data validation input messages
Lesson 2-10: Add a formula-driven date validation and a text length validation
Lesson 2-11: Add a table-based dynamic list validation
Lesson 2-12: Use a formula-driven custom validation to enforce complex business rules
Lesson 2-13: Remove duplicate values from a table
Lesson 2-14: Use a custom validation to add a unique constraint to a column
Session 2 Exercise
Session 2 Exercise Answers
Session 3: Advanced Functions
Session 3 Introduction
Lesson 3-1: Understand precedence rules and use the Evaluate feature
Lesson 3-2: Use common functions with Formula AutoComplete
Lesson 3-3: Use the Insert Function dialog and the PMT function
Lesson 3-4: Use the PV and FV functions to value investments
Lesson 3-5: Use the IF logic function
Lesson 3-6: Use the SUMIF and COUNTIF functions to create conditional totals
Lesson 3-7: Understand date serial numbers
Lesson 3-8: Understand common date functions
Lesson 3-9: Use the DATEDIF function
Lesson 3-10: Use date offsets to manage projects using the scheduling equation
Lesson 3-11: Use the DATE function to offset days, months and years
Lesson 3-12: Enter time values and perform basic time calculations
Lesson 3-13: Perform time calculations that span midnight
Lesson 3-14: Understand common time functions and convert date serial numbers to decimal values
Lesson 3-15: Use the TIME function to offset hours, minutes and seconds
Lesson 3-16: Use the AND and OR functions to construct complex Boolean criteria
Lesson 3-17: Understand calculation options (manual and automatic)
Lesson 3-18: Concatenate strings using the concatenation operator (&)
Lesson 3-19: Use the TEXT function to format numerical values as strings
Lesson 3-20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions
Lesson 3-21: Extract text from delimited strings using the FIND and LEN functions
Lesson 3-22: Use a VLOOKUP function for an exact lookup
Lesson 3-23: Use the SWITCH function
Lesson 3-24: Use an IFERROR function to suppress error messages
Lesson 3-25: Use a VLOOKUP function for an inexact lookup
Lesson 3-26: Use a MATCH function for an exact lookup
Lesson 3-27: Use the INDEX function
Lesson 3-28: Use the IFS function
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
Lesson 4-2: Manually create single cell range names and named constants
Lesson 4-3: Use range names to make formulas more readable
Lesson 4-4: Automatically create range names in two dimensions
Lesson 4-5: Use intersection range names and the INDIRECT function
Lesson 4-6: Create dynamic formula-based range names using the OFFSET function
Lesson 4-7: Create table-based dynamic range names
Lesson 4-8: Create two linked drop-down lists using range names
Lesson 4-9: Understand the #NUM!, #DIV/0! and #NAME? error values
Lesson 4-10: Understand the #VALUE!, #REF! and #NULL! error values
Lesson 4-11: Understand background error checking and error checking rules
Lesson 4-12: Manually check a worksheet for errors
Lesson 4-13: Audit a formula by tracing precedents
Lesson 4-14: Audit a formula by tracing dependents
Lesson 4-15: Use the Watch Window to monitor cell values
Lesson 4-16: Use Speak Cells to eliminate data entry errors
Session 4 Exercise
Session 4 Exercise Answers
Session 5: What If Analysis and Security
Session 5 Introduction
Lesson 5-1: Create a single-input data table
Lesson 5-2: Create a two-input data table
Lesson 5-3: Define scenarios
Lesson 5-4: Create a scenario summary report
Lesson 5-5: Use Goal Seek
Lesson 5-6: Use Solver
Lesson 5-7: Hide and unhide worksheets, columns and rows
Lesson 5-8: Create custom views
Lesson 5-9: Prevent unauthorized users from opening or modifying workbooks
Lesson 5-10: Control the changes users can make to workbooks
Lesson 5-11: Restrict the cells users are allowed to change
Lesson 5-12: Allow different levels of access to a worksheet with multiple passwords
Lesson 5-13: Create a digital certificate
Lesson 5-14: Add an invisible digital signature to a workbook
Lesson 5-15: Add a visible digital signature to a workbook
Session 5 Exercise
Session 5 Exercise Answers
Session 6: Working with Hyperlinks, Other Applications and Workgroups
Session 6 Introduction
Lesson 6-1: Hyperlink to worksheets and ranges
Lesson 6-2: Hyperlink to other workbooks and the Internet
Lesson 6-3: Hyperlink to an e-mail address and enhance the browsing experience
Lesson 6-4: Embed an Excel worksheet object into a Word document
Lesson 6-5: Embed an Excel chart object into a Word document
Lesson 6-6: Link an Excel worksheet to a Word document
Lesson 6-7: Understand the three different ways to share a workbook
Lesson 6-8: Share a workbook using the lock method
Lesson 6-9: Share a workbook using the merge method
Lesson 6-10: Share a workbook on a network
Lesson 6-11: Accept and reject changes to shared workbooks
Session 6 Exercise
Session 6 Exercise Answers
Session 7: Forms and Macros
Session 7 Introduction
Lesson 7-1: Add group box and option button controls to a worksheet form
Lesson 7-2: Add a combo box control to a worksheet form
Lesson 7-3: Set form control cell links
Lesson 7-4: Connect result cells to a form
Lesson 7-5: Add a check box control to a worksheet form
Lesson 7-6: Use check box data in result cells
Lesson 7-7: Add a temperature gauge chart to a form
Lesson 7-8: Add a single input data table to a form
Lesson 7-9: Improve form appearance and usability
Lesson 7-10: Understand macros and VBA
Lesson 7-11: Record a macro with absolute references
Lesson 7-12: Understand macro security
Lesson 7-13: Implement macro security
Lesson 7-14: Understand trusted documents
Lesson 7-15: Record a macro with relative references
Lesson 7-16: Use shapes to run macros
Lesson 7-17: Run a macro from a button control
Lesson 7-18: Show and hide ribbon tabs
Lesson 7-19: Add custom groups to standard ribbon tabs
Lesson 7-20: Create a custom ribbon tab
Session 7 Exercise
Session 7 Exercise Answers
Session 8: Pivot Tables
Session 8 Introduction
Lesson 8-1: Create a one dimensional pivot table report from a table
Lesson 8-2: Create a grouped pivot table report
Lesson 8-3: Understand pivot table rows and columns
Lesson 8-4: Understand the pivot table data cache
Lesson 8-5: Apply a simple filter and sort to a pivot table
Lesson 8-6: Use report filter fields
Lesson 8-7: Filter a pivot table visually using slicers
Lesson 8-8: Add a timeline control to a pivot table
Lesson 8-9: Use slicers to create a custom timeline
Lesson 8-10: Use report filter fields to automatically create multiple pages
Lesson 8-11: Format a pivot table using PivotTable styles
Lesson 8-12: Create a custom pivot table style
Lesson 8-13: Understand pivot table report layouts
Lesson 8-14: Add/remove subtotals and apply cell styles to pivot table fields
Lesson 8-15: Display multiple summations within a single pivot table
Lesson 8-16: Add a calculated field to a pivot table
Lesson 8-17: Add a calculated item to a pivot table
Lesson 8-18: Group by text
Lesson 8-19: Group by date
Lesson 8-20: Group by numeric value ranges
Lesson 8-21: Show row data by percentage of total rather than value
Lesson 8-22: Use pivot table values in simple formulas
Lesson 8-23: Use the GETPIVOTDATA function
Lesson 8-24: Create a pivot chart from a pivot table
Lesson 8-25: Embed multiple pivot tables onto a worksheet
Lesson 8-26: Use slicers to filter multiple pivot tables
Session 8 Exercise
Session 8 Exercise Answers
Session 9: Data Modeling, OLAP and Business Intelligence
Session 9 Introduction
Lesson 9-1: Import tables from an external relational database (Excel 2016)
Lesson 9-2: Import tables from an external relational database (Excel 365)
Lesson 9-3: Understand primary and foreign keys
Lesson 9-4: Link primary and foreign keys using VLOOKUP
Lesson 9-5: Efficiently import data using a view (Excel 2016)
Lesson 9-6: Efficiently import data using a view (Excel 365)
Lesson 9-7: Understand linked tables
Lesson 9-8: Work with big data (Excel 2016)
Lesson 9-9: Work with big data (Excel 365)
Lesson 9-10: Create a simple data model
Lesson 9-11: Understand OLAP pivot tables
Lesson 9-12: Understand OLAP cubes and Business Intelligence
Lesson 9-13: Create an OLAP pivot table directly from a relational database (Excel 2016)
Lesson 9-14: Create an OLAP pivot table directly from a relational database (Excel 365)
Lesson 9-15: Understand many-to-many relationships
Lesson 9-16: Create an OLAP pivot table using a many-to-many relationship
Lesson 9-17: Understand MDX queries and OLAP pivot table limitations
Lesson 9-18: Use the CUBEVALUE function to query an OLAP cube
Lesson 9-19: Convert CUBEVALUE functions to include MDX expressions
Lesson 9-20: Create an asymmetric OLAP pivot table using Named Sets
Session 9 Exercise
Session 9 Exercise Answers
Session 10: 3D Maps
Session 10 Introduction
Lesson 10-1: Create a simple 3D Map
Lesson 10-2: Confirm the accuracy of geocoding
Lesson 10-3: Map using different location fields
Lesson 10-4: Apply filters to a 3D Map
Lesson 10-5: Set layer options and customize data cards
Lesson 10-6: Add a height field to a layer
Lesson 10-7: Apply different visualization types
Lesson 10-8: Visualize multiple categories
Lesson 10-9: Create a visualization with multiple layers
Lesson 10-10: Add annotations
Lesson 10-11: Create a video from temporal data
Lesson 10-12: Set scene options
Lesson 10-13: Create a tour with multiple scenes
Session 10 Exercise
Session 10 Exercise Answers
Session 11: Create Get & Transform queries
Session 11 Introduction
Lesson 11-1: Understand Get & Transform and ETL
Lesson 11-2: Create a simple extract and load web query
Lesson 11-3: Understand queries and connections
Lesson 11-4: Move, remove, rename, filter and sort columns
Lesson 11-5: Split delimited data
Lesson 11-6: Specify data types
Lesson 11-7: Understand steps and PQFL
Lesson 11-8: Remove empty, error and top and bottom rows
Lesson 11-9: Understand and work with null values
Lesson 11-10: Transform date and time columns
Lesson 11-11: Transform number columns
Lesson 11-12: Add a custom calculated column
Lesson 11-13: Create an aggregated data query
Lesson 11-14: Unpivot aggregated data
Lesson 11-15: Work with multiple queries
Lesson 11-16: Create an append query
Lesson 11-17: Understand normal and de-normalized data
Lesson 11-18: Create a simple two-table merged query
Lesson 11-19: Create a five-table merged query
Session 11 Exercise
Session 11 Exercise Answers