After watching a particularly interesting Ted Talk by Jason Fried
on the benefit of ‘uninterrupted time’ I began to wonder what aspects of my market research role could be automated to provide myself with more uninterrupted time. I was aware very early on that refusing to open my emails was not an option. I started to look at requests I would receive that involve some repetitive action and it immediately became clear, assessing performance against our key performance indicators (KPIs).
The difficulty I was facing was that the time it takes to ask ‘how are the KPIs coming along?’ is remarkably shorter and easier than putting in the legwork to find out how the KPIs are coming along. The answer was to create a database that I could direct people to with the usual charts and measures typically requested of me.
Our team has since used dashboards for a range of different aspects, from social media tracking to measuring projects holistically, for example, measuring attendance across multiple corporate events. If you work in public events you may also be interested in my blog on maintaining attendance numbers
As you can see from the image, all of the charts I would use have been displayed with headings and figures which can be updated easily. Now when I’m asked for figures regarding this area of work I have everything I’m asked for ready and waiting.
Its important for me to stress that these dashboards require nothing more than a very basic knowledge of pivot tables in Microsoft Excel. I’ve since added in complex algorithms for predicting future trends in the KPIs but this required more mathematical skill and historical data than it did Microsoft Excel expertise.
So how do you do it?
A walkthrough guide for creating dashboards using Microsoft Excel.
Here’s my step by step guide to creating a simple dashboard. I would suggest initially making a back up save for your raw data. This file will be important to use as a reference point once testing the dashboard to ensure that your raw data has firstly not changed, and secondly, that your dashboard is providing the information you are expecting to receive.
1. Write out a list of all the information you want.
I suggest splitting this into two areas, the ‘must’ haves and the ‘nice’ to haves. When you’re designing a layout, it will be important to distinguish between these two. Before you continue make sure your list includes everything that will most likely be requested from you, missing something useful out now could mean a lot of time spent remodelling the layout later.
2. Draw each chart by hand and arrange into an A4 size.
Once you have an understanding of what you would like to have measured it is time to start looking at how we can turn this into a sleek, A4 sized design. Be aware of the charts that are available through Microsoft and try to keep them consistent with the work you typically provide. This will mean importing individual charts into your presentations, reports etc., will be easier for you as you don’t have to make further amends, saving you even more time.
Why have I suggested A4? It’s not a necessity, but one of the major benefits for me is being able to take this A4 sheet to a meeting when I expect to have questions around the topic area. It is also ideal if you would like to send this as a .pdf file too. 3. Create pivot tables for each piece of data
This step will be the most difficult part of the process in terms of using Microsoft Excel.
If you have never used a pivot table before, do not panic. Microsoft describe a PivotTable as “an interactive way to quickly summarise large amounts of data.” For more information on the benefits, click here
Firstly, ensure that each column has a short name and more importantly that the name will make sense to you. To create a pivot table in Excel, highlight all of the raw data you have (a little tip if you know you’re adding new rows to the data in the future, select more rows than you’ll need i.e. if you have 1300 rows of data, select 2500) and then in the banner above click Insert > PivotTable.
You’ll want to dedicate the resulting spreadsheet to all your pivot tables. To provide the figures for the PivotTable select the ‘field’ you want and drag it into the row box and then repeat the process for the values box. This will give you a table that will update with your data when you click the ‘refresh’ button. Once completed, copy and paste the original PivotTable next to itself, remove the data on the second PivotTable and repeat the process.
Continue until you have each figure needed to create the charts and tables for your dashboard. I would suggest at this point naming three spreadsheet tabs; raw data, pivot tables and dashboard. 4. Customising the raw data
Remember that you do not have to use each field individually, using your pivot tables you can now put the data in any arrangement you wish. For example, if sales/bookings for one product/event are split into different PivotTables you can now create a summary table using the ‘grand total’ feature in the pivot tables. I would suggest having a separate tab for this again. To create the table select the individual cell where you would like the data to be, click ‘=‘ and before you press enter, select the cell you wish to replicate. This grand total figure will now update when the pivot tables are refreshed with new data. Similarly this makes creating the charts easier than if you were to pull figures from multiple pivot tables. 5. Laying out the dashboard
You have your data, you have your pivot tables, now its time to create the dashboard. Remember to follow the design you set out in step two. When creating the dashboard there are essentially two possibilities, you can copy figures straight from the pivot tables using ‘=‘ formulas or you can create charts to display your data. Also note that if you make any changes to the chart axis, it could make future data look incorrect when the pivot tables are updated in the future. 6. Testing
Once you have a layout you are happy with, test the database to ensure that the figures are indeed correct, drawing data from the right pivot tables; and, they are updating when you are adding data. To refresh your pivot tables click on any pivot and there is a ‘refresh’ button, thanks to Microsoft Excel, this will update all of the pivots simultaneously. Bonus step - macro button.
If you are comfortable using Microsoft Excel I would suggest adding a macro button to your dashboard with the title ‘update’. This will save you ever having to see the pivot table spreadsheet again. The button will also helps make it easier for those who are less comfortable using the software to update the dashboard. If you are looking to push your Microsoft Excel knowledge to the limit try visiting chandoo.org
for a huge range of databases that use advanced Excel techniques.
These databases are as expansive as your creativity and Microsoft Excel capabilities. Speaking from experience, I have found several of my colleagues have also benefitted from having their own bespoke dashboards across various professional fields: human resources, social media management and event scheduling to name a few. These dashboards can also be used to interlink with multiple databases, providing the files and data cells are all stored in a consistent location. To find out more about how Alsea Research can help you with managing your data visit
or email Andrew.Harris@AlseaResearch.co.uk