Category Archives: Excel Training

Excel Dashboard

Creating an Excel Dashboard – Election Special Video

What is an Excel Dashboard?

Dashboards are essentially reports in Excel that help you visualise and analyse complex pieces of data.

In the following video we look at an example Excel dashboard to guide us through the mind boggling data being produced by the current UK general election.

A further look at Excel Dashboards

Why use dashboards?

We can store a huge amount of data in Excel but it is not very easy in a large worksheet to quickly get the information we need. This is where an Excel Dashboard can be a very efficient and time  saving option.  Once a dashboard is created in the right way it can be easily amended.

the whole dashboard2

Top 5 reasons for Dashboards

We need dashboards to:

  • Visualise data
  • Spot patterns and trends
  • Improve appearances
  • Make the numbers more digestible
  • Provide interactive selective reporting

How should an Excel dashboard be created?

Before creating a dashboard, you need to spend some time planning. What is the objective and who is your audience? Make sure that your audience can get the information they need from your dashboard.

Ask yourself: “What do I need to track in the dashboard”? Try to get a clear picture of what you are trying to accomplish.

When creating a dashboard, think about how the dashboard will be viewed to make it meaningful for your audience. Some may need a clean, compact dashboard with simple visualizations. Others may want to be able to use more complicated tools and more sophisticated visualizations to dig deeper into the source data.

Dashboards should always be created in a way that data can be added or removed from the source data without time consuming updating of ranges, functions and other Excel tools used in the Dashboard.

Interactivity is an important part of a dashboard.

excel dashboard

By adding interactivity such as drop down lists the dashboard can be very dynamic and feed us with even more useful information.

Excel has many statistical tools which can supply us with important information in  the dashboard such as trend analysis, forecasting, rank, correlation, average and much more.

stats

KPI’s are another very useful ingredients in a dashboard to visualize the performance.

kpi

Additional Resources

As you can see creating an Excel dashboard is really a combination of Excel skillsets. These include pivot tables, chart creation, filtering data, lookups and plenty more.

The following resources can help you on your way to creating Excel dashboards.

What is a Pivot Table?

Excel 2013: 3 New Ways To Customise Your Charts

Excel Dashboards 1 day training course

Create a drop-down list in Excel

What are the odds like for the UK General Election?

The UK general election is almost upon us and it’s a tight race! It’s the most unpredictable election in memory and has already produced some lively debates and political speed-dating as potential alliances are considered.

There are so many sources from which to gain the latest on likely election winners, and one such are the bookies. There are some great websites with up to the minute odds on all the political parties and it got us thinking wouldn’t it be great to capture and analyse this information in Excel….

After several cups of coffee and Excel wizardry one of our Excel trainers has created a rough but working Excel 2015 UK General Election spreadsheet.  You can download the spreadsheet here.

What it does:
  • Automatically updates with latest betting odds every hour (you can refresh at any time)
  • View a chart to analyse the betting odds
What you can do:
  • By having the latest data you have the ability to create more charts to your heart’s content!
  • Capture the live data so that you can view it historically. How did the odds change over the week? Which party gained/lost the most (from an odds perspective!).
  • With a bit of VBA pokery you could even create lookup reports focussing on specific parties.

There is a lot of opinion out there on the general election but you could do worse than follow the signals of those who are putting their money where their mouth is!

Learn more:

If you would like to learn more about how we produced this spreadsheet; you may wish to track other live web data like stocks, competitor pricing and more, then just click here.

Acknowledgements and useful resources

http://www.oddschecker.com/politics/british-politics/next-uk-general-election/most-seats

Election 2015: Poll tracker

http://www.ft.com/indepth/uk-general-election

Betting odds explained

Learn more about available Excel training courses