Excel 2016 Essential Training

Learn how to enter and organize data, create formulas and functions, build charts and PivotTables, and use other powerful Excel 2016 features.

Introduction

• Welcome
• Using the exercise files

1. Getting Started with Excel

• What is Excel used for?
• Using the menu system
• Using the Quick Access Toolbar
• Understanding workbooks and worksheets
• Using the Formula bar
• Using the Status bar
• Using navigation tools
• Using shortcut menus and the Mini toolbar
• Creating new workbooks
• Using Excel Help

2. Entering Data

• Exploring data entry and editing techniques
• Entering data with AutoFill
• Working with dates and times
• Using Undo and Redo
• Adding comments
• Using Save or Save As

3. Creating Formulas and Functions

• Creating simple formulas
• Copying a formula into adjacent cells
• Calculating year-to-date totals
• Creating a percentage-change formula
• Working with relative, absolute, and mixed references
• Using SUM and AVERAGE
• Using other common functions

4. Formatting

• Exploring font styles and effects
• Adjusting row heights and column widths
• Working with alignment and Wrap Text
• Designing borders
• Exploring numeric and special formatting
• Formatting numbers and dates
• Using conditional formatting
• Creating and using tables
• Inserting shapes, arrows, and other visual features

5. Adjusting Worksheet Layout and Data

• Inserting and deleting rows and columns
• Hiding and unhiding rows and columns
• Moving, copying, and inserting data
• Finding and replacing data

6. Printing

• Exploring the Page Layout tab and Page Layout view
• Previewing page breaks
• Working with Page Setup and printing controls

7. Charts

• Creating charts
• Exploring chart types
• Formatting charts
• Working with axes, titles, and other chart elements
• Creating in-cell charts with sparklines
• New charts in Excel 2016

8. Adjusting Worksheet Views

• Freezing and unfreezing panes
• Splitting screens horizontally and vertically
• Collapsing and expanding data views with outlining

9. Multiple Worksheets and Workbooks

• Displaying multiple worksheets and workbooks
• Renaming, inserting, and deleting sheets
• Moving, copying, and grouping sheets
• Using formulas to link worksheets and workbooks
• Locating and maintaining links

10. IF, VLOOKUP, and Power Functions

• Using IF functions and relational operators
• Getting approximate table data with the VLOOKUP function
• Getting exact table data with the VLOOKUP function
• Using the COUNTIF family of functions

11. Security and Sharing

• Unlocking cells and protecting worksheets
• Protecting workbooks
• Assigning passwords to workbooks
• Sharing workbooks
• Tracking changes

12. Data Management Features

• Sorting data
• Inserting subtotals in a sorted list
• Using filters
• Splitting data into multiple columns
• Splitting and combining columnar data with Flash Fill
• Removing duplicate records
• Using validation tools

13. Data Analysis Tools

• Using Goal Seek
• Using Solver
• Using Scenario Manager
• Using data tables

14. PivotTables

• Creating PivotTables
• Manipulating PivotTable data
• Grouping by date and time
• Grouping by other factors
• Using slicers to clarify and manipulate fields
• Using PivotCharts

15. Introduction to Macros

• Exploring the need for macros
• Creating a simple macro
• Running a macro

Conclusion

• Goodbye