Instructor-led training -

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Advanced Excel DashboardsAdvanced Excel Dashboards

Face to face / Online closed & onsite training. Restaurant lunch included at STL venues.

Designed for Excel 365

  • 1 day Instructor-led

Free manuals

We are providing a range of our course manuals free of charge.

Why not share this resource with your friends and colleagues?

Training manual sample

Below are some extracts from our Excel training manuals.

1. Planning a Dashboard

What is a Dashboard?

Dashboards are a type of report. They help turn, often large amounts of data, into a digestible page that enables easy analysis. This form of data analysis can be part of Business Intelligence (BI).

Why use a Dashboard

Many companies have access to large amounts of data, perhaps in a data warehouse or spread across multiple file servers. Decision makers often want to cut through this data and look for patterns. Dashboards are an excellent way to turn large amounts of data into usable and actionable reports. 
 

2. Managing Dashboards

Dashboards need to be maintained and managed. You can create Dashboards where you will need to spend a lot of time maintaining and managing the dashboards and this is of course not a good idea. 

Amend and Update Dashboards

The source data can be based on a query in order to improve the efficiency of updating a dashboard. You may have the data from the last 36 months in the Dashboard. By writing a query which defines the number of periods you want to be shown in the dashboard, you will not need to spend time “cleaning” the source data when new periods are added to the source data.

  

Updating the Data Source

Have the source data linked to the dashboard workbook can be a good idea. This will keep the size of the dashboard workbook small.

 You will also be able to control the source data and the source data do not need to be updated in many workbooks. The source data

 could be in a shared folder and then each department could have a dashboard workbook with the specific information they will need. 

 

3. Source Data for Reporting

A major consideration you need to think of is which data source or sources you work with and would it be a good idea to consolidate the date in Excel or in an external data source and then link the source to your dashboard. You have different options and it is very important to use the one which fits your situation best to create a successful dashboard. If you have a single flat list in Excel containing all the data you need, it is easier to create the dashboard, but you still need to think about how you create the dashboard to make the updates of the source data as efficient and less time consuming as possible.  
 

Work with Single List Data Source (Flat List)

Many Excel users can make a mistake when they create a new worksheet. They want it to look nice and it must be easy to enter the data. 

This is not the best approach. You need to have the data structured so you can use the data. 

Excel needs the data structured in a flat list if you need to analyse the data. If not, the work creating a dashboard will be much more

time -consuming and complicated. 

 

4. Linking to External Data Sources

 

Creating Web Queries

Web pages often contain information that is perfect for analysis in Excel. Using a web query, you can retrieve data that is either static or refreshable. That is, the data in Excel will update with the latest data on the Web page.  

Importing Data from Other Sources

You can use Microsoft Query to import information from many database applications. The Query Wizard, part of Microsoft Query, guides you through the steps to create a query.

 

 5. Advanced Charts for Dashboards

Speedometer Chart

A Speedometer or gauge chart can be a way of showing progress, and how close something is to hitting a target.

 

 

Waterfall Chart

Waterfall charts, sometimes known as bridge or step charts, are popular ways of showing how values are distributed, or how amounts are broken down from period to period.

 

They are often used in budgets, showing how the amounts grew or shrank from one year to the next. It could also be used to show how sales or profits progressed across a financial year. 

 

6. Forecasts and Trend Analysis in Dashboards

Forecasting in Excel

Excel has a number of effective tools for forecasting. You can work with linear data, exponential data or seasonal data. Excel can forecast any source data as long as there is a pattern in your data. When you forecast you need to know the accuracy of your forecast to make sure that you are using the right forecasting model. 

Forecast Dashboard

 

 

7. Statistical Formulas for Dashboards

Statistical Excel functions can provide the audience with useful information. Many statistical functions can be used in dashboards. In this handbook, specifically Trendline and forecasting functions are used. 

The Slope Function

The Slope function is a trend function which calculates the average trend increase/decrease for known values for each period.

The syntax: =SLOPE(known_y's, known_x's)

The Known_y’s is the known values and the Known_x’s is the known periods.

In the example below the sales is known for January to September (E6:E26). The known periods 1 to 21 (B6:B26). The average trend increase here is 306 for each period.