Overview
This 2-day course is designed for students who already have foundational knowledge and skills in Excel 2016 and wish to begin taking advantage of some of the higher-level functionality in Excel to summarize table data in Excel by using functions, charts, and pivot tables and display those elements in a dashboard format. In this course, students will create advanced workbooks and worksheets that can be used to create dashboards.
Note:
- Training will be conducted in Excel 2016.
- Students who have been using Excel 2013 are recommended to take this course, as 2013 and 2016 version are very identical.
Course Outline
- Applying Conditional Formatting
- Changing Conditional Formatting
- Adding Conditional Formatting
- Deleting Conditional Formatting
- Applying Custom Number Formats
- Identifying range names
- Creating range names
- Working with range names
- Applying range names in formulas
- Using range names in 3D formulas
- VLookup
- HLookup
- Index & Match
- IF Function
- Nested IF
- CountIf
- SUMIF
- ISNA
- Array Formulas
- Database Functions
- Using Data Tables
- Placing Formulas in Data Tables
- Creating a One-Variable Table
- Creating a Two-Variable Table
- Creating a PivotTable Report
- Using PivotTable Report Fields
- Using a Report Filter Field Item
- Refreshing a PivotTable Report
- Changing the Summary Function
- Adding and Moving Fields in a PivotTable Report
- Applying Filtering to PivotTable Report Items
- Inserting Slicers
- Using Slicers to Filter Data
- Deleting PivotTable Report Fields
- Creating Report Filter Pages
- Formatting a Pivot Table Report
- Creating a PivotChart Report
- Creating Excel Dashboards
