Overview
Learn how to enter and organize data, create formulas and functions, build charts and PivotTables, and use other powerful Excel 2016 features.
Syllabus
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
- Goodbye
Conclusion