

Excel 2016
Document formulas with the
FORMULATEXT function
 Extract and count unique entries from a
list with UNIQUE
1. Formula and Function Tips and Shortcuts  Use the XLOOKUP function
 Display and highlight formulas 4. Power Functions
 Use the auditing tools
 Use entire row/column references  Tabulate data using a single criterion
 Change formulas to values and update with COUNTIF, SUMIF, and AVERAGEIF
values without formulas  Tabulate data using multiple criteria
 Simplify debugging formulas with the F9 with COUNTIFS, SUMIFS, and
key AVERAGEIFS
 Enhance readability with range names  Use MAXIFS and MINIFS
 Create 3D formulas to tabulate data  Use the SUBTOTAL function to prevent
from multiple sheets double counting

2. IF and Related Functions 5. Statistical Functions

 Explore IF logical tests and use  Find middle and most common values
relational operators with MEDIAN and MODE
 Create and expand the use of nested IF  Rank data without sorting using RANK
statements and RANK.EQ
 Create compound logical tests with  Find the largest and smallest values
AND, OR, NOT, and IF with LARGE and SMALL
 Use IFS for multiple conditions  Tabulate blank cells with the
COUNTBLANK function
3. Lookup and Reference Functions  Use COUNT, COUNTA, and the status
 Explore the VLOOKUP and HLOOKUP bar
functions 6. Math Functions
 Find approximate matches with
VLOOKUP and HLOOKUP  Work with the ROUND, ROUNDUP, and
 Use VLOOKUP to find exact matches ROUNDDOWN functions
and search large tables  Use MROUND, CEILING, and FLOOR for
 Find table-like data within a function specialized rounding 3m 10s
using CHOOSE  Use INT, TRUNC, ODD, and EVEN for
 Use the SWITCH function for formula- specialized rounding
embedded selection  Use MOD to find remainders and apply
 Locate data with the MATCH function conditional formatting
 Retrieve information by location with  Explore practical uses for RAND,
the INDEX function RANDARRAY, and RANDBETWEEN
 Use the MATCH and INDEX functions  Convert a value between measurement
together systems with CONVERT
  Use the AGGREGATE function to bypass  Combine data with symbols (&) and
errors and hidden data CONCATENATE
 Use ROMAN and ARABIC to display  Use CONCAT and TEXTJOIN to combine
different number systems data from different cells
 Adjust alphabetic case with UPPER,
LOWER, and PROPER
7. Date and Time Functions  Adjust character content with REPLACE
and SUBSTITUTE
 Understand Excel date and time
 Use utility text functions: TEXT, REPT,
capabilities in formulas
VALUE, and LEN
 Use various date and time functions
 Use the new LET function
 Use the TODAY and NOW functions for
date and time entry 10. Information Functions
 Identify weekdays with the WEEKDAY
 Extract information with the CELL and
function
INFO functions
 Count working days and completion
 Explore various information functions
dates (NETWORKDAYS and WORKDAY)
 Use several error-checking functions
 Tabulate date differences with the
 Track and highlight formula cells with
DATEDIF function
ISFORMULA
 Calculate dates with EDATE and
EOMONTH

Conclusion

8. Reference Functions Next steps

 Get data from remote cells with the 34s

OFFSET function(In progress)
Excel: Advanced Formulas and Functions
 Return references with the INDIRECT
function Get data from remote cells with the OFFSET
 Use INDIRECT with Data Validation for function
multitiered pick lists

9. Text Functions Excel Advanced

 Locate and extract data with FIND,
SEARCH, and MID 1. Getting Started with Excel
 5m 11s
 Extract data with the LEFT and RIGHT  What is Excel used for?
functions  2m 14s
 3m 42s  Using the menu system
 Use the TRIM function to remove  5m 50s
unwanted spaces in a cell  Using the Quick Access Toolbar
 4m 48s  6m 49s
  Understanding workbooks and  Calculating year-to-date totals
worksheets  3m 49s
 4m 45s  Creating a percentage-change formula
 Using the Formula bar  7m 37s
 1m 37s  Working with relative, absolute, and mixed
 Using the Status bar references
 5m 20s  5m 32s
 Using navigation tools(In progress)  Using SUM and AVERAGE
 3m 24s  5m 18s
 Using shortcut menus and the Mini  Using other common functions
toolbar  7m 44s
 4m 25s  Chapter Quiz
 Creating new workbooks  3 questions
 2m 37s
 Using Excel Help
 5m 28s 4. Formatting
 Chapter Quiz
 Exploring font styles and effects
 4 questions
 3m 17s
 Adjusting row heights and column widths
 5m 40s
2. Entering Data
 Working with alignment and Wrap Text
 Exploring data entry and editing techniques  6m 34s
 4m 29s  Designing borders
 Entering data with AutoFill  2m 55s
 3m 56s  Exploring numeric and special formatting
 Working with dates and times  8m 1s
 4m 28s  Formatting numbers and dates
 Using Undo and Redo  7m 18s
 4m 13s  Using conditional formatting
 Adding comments  8m 13s
 2m 52s  Creating and using tables
 Using Save or Save As  11m 18s
 4m 11s  Inserting shapes, arrows, and other visual
 Chapter Quiz features
 4 questions
9m 35s

Chapter Quiz
3. Creating Formulas and Functions
6 questions
 Creating simple formulas
 7m 11s
 Copying a formula into adjacent cells 5. Adjusting Worksheet Layout and Data
 3m 48s
 Inserting and deleting rows and columns 8. Adjusting Worksheet Views
 6m 19s
 Freezing and unfreezing panes
 Hiding and unhiding rows and columns
 5m 1s
 4m 33s
 Splitting screens horizontally and vertically
 Moving, copying, and inserting data
 5m 19s
 5m 35s
 Collapsing and expanding data views with
 Finding and replacing data
outlining
 5m 30s
 6m 22s
 Chapter Quiz
 Chapter Quiz
 2 questions
 1 question

6. Printing
9. Multiple Worksheets and Workbooks
 Exploring the Page Layout tab and Page
 Displaying multiple worksheets and
Layout view
workbooks
 7m 57s
 9m 44s
 Previewing page breaks
 Renaming, inserting, and deleting sheets
 5m 53s
 4m 13s
 Working with Page Setup and printing
 Moving, copying, and grouping sheets
controls
 6m 7s
 9m 24s
 Using formulas to link worksheets and
 Chapter Quiz
workbooks
 1 question
 8m 57s
 Locating and maintaining links

7. Charts 6m 50s

 Creating charts Chapter Quiz

 6m 45s
3 questions
 Exploring chart types
 9m 53s
 Formatting charts
10. IF, VLOOKUP, and Power Functions
 7m 12s
 Working with axes, titles, and other chart
elements
11. Security and Sharing
 6m 25s
 Creating in-cell charts with sparklines Unlocking cells and protecting worksheets
 7m 16s
8m 2s
 New charts in Excel 2016
 10m 19s Protecting workbooks
 Chapter Quiz
5m 50s
 4 questions
 Assigning passwords to workbooks
2m 52s 7m 14s

Sharing workbooks Using Solver

3m 57s 7m 3s

Tracking changes Using Scenario Manager

5m 17s 6m 38s

Chapter Quiz Using data tables

3 questions 8m 21s

Chapter Quiz

12. Data Management Features 3 questions

Sorting data

9m 44s 14. PivotTables

Inserting subtotals in a sorted list Creating PivotTables

8m 39s 12m 25s

Using filters Manipulating PivotTable data

6m 43s 11m 27s

Splitting data into multiple columns Grouping by date and time

8m 26s 10m 50s

Splitting and combining columnar data with Grouping by other factors

Flash Fill
4m 40s
6m 40s
Using slicers to clarify and manipulate fields
Removing duplicate records
7m 10s
2m 23s
Using PivotCharts
Using validation tools
4m 42s
10m 45s
Chapter Quiz
Chapter Quiz
1 question
3 questions

15. Introduction to Macros

13. Data Analysis Tools
Exploring the need for macros
Using Goal Seek
6m 14s
Creating a simple macro

10m 20s

Running a macro

13m 46s

Chapter Quiz

1 question