Pivot Table Data Crunching and Dashboard Report in Microsoft Excel 

Duration: 2  Days

Course Objectives:

This is a 2-days Excel Pivot Table Data Crunching & Interactive Reports training for those who want to dive into company’s large data and look at the company’s KPIs for decision making purposes. If you have the basic knowledge of Excel charts, pivot tables, functions and formulas, you can proceed to this course to learn how to create dashboard out of your, example sales data, that allows you to interactively slice-and-dice within the data and gain greater insights out of your company’s business performance. 

Who Should Attend?

This course is intended primarily for advanced user of Microsoft Excel of any version.

Pre-requisite

This course is designed for current Microsoft Excel users who can build basic workbooks. 

View reference

Course Outlines

Module 1: Dashboard Design Principles

• What is a dashboard?
• Purpose and benefits of dashboards
• Understanding dashboard design principles
• Common mistakes when building dashboards

Module 2 : Creating a Pivot Table 

• Preparing Data for Pivot Table Reporting 
• How to Create a Basic Pivot Table 
• Understanding the Recommended Pivot Table Feature. 
• Using Slicers 
• Keeping Up with Changes in the Data Source 
• Saving Time with New Pivot Table Tools

Module 3: Grouping, Sorting, and Filtering Pivot Data 

• Automatically Grouping Dates 
• Using the PivotTable Fields List 
• Sorting in a Pivot Table 
• Using Filters for Row and Column Fields 
• Filtering Using the Filters Area

Module 4: Performing Calculations in Pivot Tables 

• Introducing Calculated Fields and Calculated Items 
• Creating a Calculated Field 
• Creating a Calculated Item 
• Understanding the Rules and Shortcomings of Pivot Table Calculations 
• Managing and Maintaining Pivot Table Calculations

Module 5: Data Visualization – Picking The Right Display

• The anatomy of an Excel chart
• Displaying trends with charts
• When to use a secondary axis
• Combining two chart types
• Adding icons and images to dashboards

Module 6: Using Sparklines & Conditional Formatting

• Creating sparklines – line, column and win/loss
• Sparkline formatting and options
• Sparkline tips and tricks
• Applying conditional formatting
• Data bars, color scales and icon sets
• Conditional formatting options
• Using symbols to enhance reporting

Module 7: Advanced Charting Techniques

• Step charts
• Actual vs budget (target) charts – Floating Markers
• Band chart – show performance against target range
• Conditional colors in column chart
• Frequency distribution
• Waterfall chart

Module 8: Named Ranges And Excel Table

• Naming cells and ranges
• Creating Excel tables
• Applying table names
• Using structured references in formula
• Refreshing tables with new data

Module 9: Excel Advance Formula & Function 

• Learn to nest functions together to create robust formulas
• Use IF, Nested IFs and IFS for logical test with single or multiple conditions
• Embed AND or OR function in IF for robust logical tests
• Trap and handle errors produced by other formulas or functions with IFERROR
• Use CHOOSE to return a value from an array based on index number
• Aggregate data with single criterion using SUMIF, COUNTIF, AVERAGEIF
• Tabulating information using multiple criteria with SUMIFS, COUNTIFS, AVERAGEIFS
• Lookup and retrieve data from a specific column in table using VLOOKUP
• Use MATCH to find the relative numeric position of an item in a range
• Use INDEX to extract a value from a table (or range)
• INDEX and MATCH – powerful combo that has more flexibility and speed
• Using ROW(S) and COLUMN(S) for indexing
• Using TEXT and CONCATENATE to string numbers 

Module 10: Building Interactive Charts And Dashboards Controls

• Using form controls: Button, Combo Box, Check Box, Spin Button, List Box, Option Button, Scroll Bar, Label and Group Box
• Set up dynamic data validation list
• Creating dynamic named range with OFFSET and COUNTA
• Creating dynamic named range with INDEX
• Using the INDIRECT function

View Other Courses
View All Upcoming Trainings