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
Lesson 1: Conditional Formatting and Custom
- Applying Conditional Formatting
- Changing Conditional Formatting
- Adding Conditional Formatting
- Deleting Conditional Formatting
- Applying Custom Number Formats
Lesson 2: Range Names
- Identifying range names
- Creating range names
- Working with range names
- Applying range names in formulas
- Using range names in 3D formulas
Lesson 3: Functions used for Dashboards
- VLookup
- HLookup
- Index & Match
- IF Function
- Nested IF
- CountIf
- SUMIF
- ISNA
- Array Formulas
- Database Functions
Lesson 4: Using Data Tables
- Using Data Tables
- Placing Formulas in Data Tables
- Creating a One-Variable Table
- Creating a Two-Variable Table
Lesson 5: Creating PivotTables
- 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
Lesson 6: Working with Dashboards
- Creating Excel Dashboards
