Creating Dashboards with Microsoft Excel Training Course

Participants are expected to meet the following requirements for the course: 

• Knowledgeable in basic Excel 

• Managing workbook and worksheets 

• Creating Formulas and Using Functions 

• Copying and moving data 

• Formatting data and cells 

• Working with columns and rows 

• Familiar with respective data analyzed 

OBJECTIVES 

• Plan an effective dashboard layout 

• Analyze data using Pivot Tables 

• Appreciate dashboard charting techniques 

• Visually present data using conditional formatting 

• Create an interactive dashboard 

• Distribute a secure dashboard  

COURSE OUTLINE

Module 1: Starting an Excel Dashboard 

• Considerations when starting an Excel Dashboard 

• Dashboard Design Best Practices 

• Dashboard Layout 

• Ideal Data and Workbook structure

Module 2: Data Structuring using Excel Tables 

• Creating Excel Tables 

• Using Common Excel Formulas used in Dashboards 

Module 3: Connecting external data source

• Using Microsoft Query 

Module 4: Analyzing Data using Pivot Tables

• Building Pivot Tables 

• Summarizing data in Pivot Tables 

• Using Calculated Field and Calculated Items 

• Customizing the Pivot Table Design 

Module 5: Visually presenting the data

Conditional formatting to highlight data 

Module 6: Dashboard Visualization using Charts 

• Creating Charts 

• Creating user-defined chart types 

• Combining different chart types in one chart 

• Customizing the chart design 

Module 7: Interactive Dashboard Control 

• Using Slicers for Tables, PivotTables and Pivot Charts 

Module 8: Consolidating and Distributing the Dashboard 

• Moving and Scaling 

• Hiding Worksheets