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

Learn Excel 2016 for Mac 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: Configure your mouse and display ribbon group names
Lesson 1-2: Understand update models
Lesson 1-3: Check that your Excel version is up to date
Lesson 1-4: Change the Office Theme
Lesson 1-5: Apply a simple filter to a range
Lesson 1-6: Apply a top 10 and custom filter to a range
Lesson 1-7: Apply an advanced filter with multiple OR criteria
Lesson 1-8: Apply an advanced filter with complex criteria
Lesson 1-9: Apply an advanced filter with function-driven criteria
Lesson 1-10: Extract unique records using an advanced filter
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
Lesson 3-2: Use common functions with Formula AutoComplete
Lesson 3-3: Use the Formula Builder task pane 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 an IFERROR function to suppress error messages
Lesson 3-24: Use a VLOOKUP function for an inexact lookup
Lesson 3-25: Use a MATCH function for an exact lookup
Lesson 3-26: Use the INDEX 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
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
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: Embed an Excel worksheet object into a Word document
Lesson 6-4: Link an Excel worksheet to a Word document
Lesson 6-5: Embed an Excel chart object into a Word document
Lesson 6-6: Understand the three different ways to share a workbook
Lesson 6-7: Share a workbook using the lock method
Lesson 6-8: Share a workbook using the merge method
Lesson 6-9: Share a workbook on a network
Lesson 6-10: 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
Lesson 7-12: Understand macro security
Lesson 7-13: Use shapes to run macros
Lesson 7-14: Run a macro from a button control
Lesson 7-15: Show and hide ribbon tabs
Lesson 7-16: Add custom groups to standard ribbon tabs
Lesson 7-17: 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: Use slicers to create a custom timeline
Lesson 8-9: Use report filter fields to automatically create multiple pages
Lesson 8-10: Format a pivot table using pivot table styles
Lesson 8-11: Create a custom pivot table style
Lesson 8-12: Understand pivot table report layouts
Lesson 8-13: Add/remove subtotals and apply cell styles to pivot table fields
Lesson 8-14: Display multiple summations within a single pivot table
Lesson 8-15: Add a calculated field to a pivot table
Lesson 8-16: Add a calculated item to a pivot table
Lesson 8-17: Group by text
Lesson 8-18: Group by date
Lesson 8-19: Group by numeric value ranges
Lesson 8-20: Show row data by percentage of total rather than value
Lesson 8-21: Use pivot table values in simple formulas
Lesson 8-22: Use the GETPIVOTDATA function
Lesson 8-23: Embed multiple pivot tables into a worksheet
Lesson 8-24: Use slicers to filter multiple pivot tables
Session 8 Exercise
Session 8 Exercise Answers