Excel Dashboards and Management Reporting
Excel Dashboards and Management Reporting
This course will cover how to use MS Excel to create meaningful dashboards and management reports.
It is assumed you know the basics about Pivot Tables and Charting (other courses available covering the basics).
- Time Req – 16 hrs
- Skill Score >5
- FAQ
- All Online Courses
- Live Course Options
- Ask a Question
The course will focus on areas that apply to dashboards and will include practical aspects of Pivot Tables, Charting, Conditional Formatting and Sparklines along with other critical dashboard tips and tricks using Microsoft’s Excel.
Modules
What is a dashboard
Lessons
Getting the data right for a dashboard
Lessons
- Getting the data right for a dashboard Free Preview
- Data Cleanup Ideas Free Preview
- Standardise the entries e.g. Yes and No only- Once off method Free Preview
- Standardise the entries- Data Cleanup Template Free Preview
- Fixing Dates Free Preview
- Group Data into useful Bands Free Preview
- Enhance the data
- Data Cleanup Exercises
Useful Excel Tools for dashboarding
Lessons
Conditional Formatting for Dashboards
Lessons
Pivot Table Dashboard Tricks
Lessons
- Pivot Table Tips for dashboards
- Compact your pivot report to fit into a dashboard
- Stop the Pivot Autofitting the columns on refresh
- Repeat the row labels on long reports
- Custom sort and Top/ Bottom 10 Pivots
- Stop Pivot Reports overwriting themselves
- Use Slicers to create custom dashboards
- Choose criteria for all the Pivots at once
- Conditional Format your Pivot Table
- Auto Group your Pivot tables into weeks, months, quarters or years
- Extract key numbers out of a pivot
- Create charts out of a pivot
- Pivot Table- Exercises
Dashboard Charting Tips and Tricks
Lessons
- Charting Tips for Dashboarding
- Create a format painter for charts
- Remove Zeros from chart labels
- Combine Charts
- Clustered, Stacked and 100% Stacked
- 2D versus 3D charts
- Which chart to use
- Hiding chart series (and why you would want to do it)
- Hiding a series by overlapping
- Hiding a series with NA()
- Hiding a series using No Fill
- Hidden, Common Series for common axis scaling
- Changing a single series size with error bars
- Changing a single series size with the secondary axis
- Customise the series marker to your own image
- Add linked commentary directly to the chart
- Add commentary to the axis
- Add commentary to labels and call outs
- Add commentary to labels for older versions of Excel
- Chart Examples 1- Conditional bar charts, Chart builder
- Chart Examples 2- Combo Charts
- Chart Examples 3- Background shading, bullet charts and risk rankings
- Chart Examples 4- Gantt Charts
- Chart Examples 5- Conditional Formatting
- Chart Examples 6- Add Commentary
- Chart Examples 7- Multiple Charts and common axis
Putting the dashboard together
Lessons
- Putting the Dashboard Together
- Fit your dashboard onto the page
- Make all the charts (exactly) the same size
- Move Charts and Objects in small increments
- Group Charts together to move them at the same time
- Align your charts exactly with the gridlines
- Stop charts stretching when the column changes
- Paste Linked Pictures into your dashboard
