MICROSOFT EXCEL AND PIVOT TABLE DASHBOARD

Introduction

Pivot Table is an interactive worksheet table that provides a powerful tool for summarising large amounts of tabular data. In other words, it is a way to extract complex data from a long list of information and present it in a readable form to facilitate reporting.
Upon completing the data analysis using the Pivot Table, the next step is to visualize the data using charts available in Excel. Finally charting is performed by considering the best methods to present complex information and provide meaningful insight.

Duration

2 Days

Learning Outcome

At the completion of the training, candidates will be able to:

• Generate a Pivot Table
• Perform Calculations from Pivot Table
• Set Advanced Pivot Table Options
• Create Pivot Tables from different sources
• Create Pivot Chart
• Work Pivot Table with Macro
• Layout a dashboard
• Use Pivot Tables to analyse data
• Analyse data using Excel formulas
• Implement Dashboard Charting techniques
• Visually present data using traffic lights and slicers
• Include trend lines
• Incorporate scenarios to show different outcomes
• Create an interactive dashboard
• Use consistent scaling for dashboard elements
• Distribute a secure dashboard

Software Version

Microsoft Excel version 2013 and above

Training Course Outline

MODULE 1: PIVOT TABLE

• Creating Pivot Table
• Working with Pivot Tables
• Updating a Pivot Table
• Changing the Layout of the Pivot Table
• Adding and Removing Fields in a Pivot Table
• Pivot Table Formatting
• Pivot Tables Formatting Options
• Format Report Using Conditional Formatting inside Pivot Tables
• Add a conditional format
• Pivot Table Toolbar

MODULE 2: CALCULATIONS FROM PIVOT TABLES 

• Pivot Table Formula
• Adding Calculated Fields
• Adding Calculated Items
• Edit a calculated field formula
• Edit a single formula for a calculated item
• Edit individual formulas for specific cells of a calculated item
• Using VLOOKUP with the Pivot Tables

MODULE 3: ADVANCED PIVOT TABLES OPTIONS

• Pivot Table Summaries
• Pivot Table Field Options
• Changing Pivot Table Options
• Other Pivot Table Options Setting

MODULE 4: ADVANCED PIVOT TABLES OPTIONS

• Creating a Pivot Tables based on another Pivot Table
• Creating a Pivot Tables from External Data
• Consolidating Information Using Pivot Tables
• Create a Pivot Table Consolidate from multiple worksheets

MODULE 5: CREATING PIVOT CHART

• Formatting Chart Elements
• Changing the Series Order

MODULE 6: CREATING A DASHBOARD

• Dashboard layout
• Best Practice
• Effective use of colour

MODULE 7: ANALYSING DATA USING PIVOT TABLES 

• Building a Pivot Table
• Pivot Table totals
• Pivot Table fields
• External data sources

MODULE 8: ANALYSING DATA USING FORMULAS

• Lookup functions: VLOOKUP, INDEX, OFFSET
• Decision-making functions: IF, CHOOSE
• Aggregation functions: SUMIFS, COUNTIFS

MODULE 9: DASHBOARD CHARTING TECHNIQUES 

• Creating Charts
• Creating user-defined chart types
• Changing the default chart type
• Combining different chart types in one chart
• Creating custom markers
• Creating miniature charts suitable for dashboards
• Using Sparklines
• Target vs Actual charts
• Bubble charts

MODULE 10: VISUALLY PRESENTING DATA

• Conditional formatting to highlight data
• Traffic lights
• Using text boxes
• Using Slicers

MODULE 11: TRENDS

• Calculating a trend
• Inserting trendlines on charts

MODULE 12: SCENARIOS

• Creating scenarios
• Options for choosing different scenarios

MODULE 13: INTERACTIVE DASHBOARDS

• Drop-down lists
• Form controls
• Linking charts to form controls

MODULE 14: BRINGING IT ALL TOGETHER

• Using consistent scaling
• Moving and scaling graphic objects

MODULE 15: DISTRIBUTING YOUR DASHBOARD

• Hiding worksheets and ranges
• Security and protection

Pre-requisite

Candidates should have completed Excel Intermediate Level.