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 dates and course fee may be subjected to changes.
Course Outline
Lesson 1: Conditional Formatiing 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
