Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Build a dashboard with Excel 2010 Sparklines

by
15th Apr 2010
Save content
Have you found this content useful? Use the button above to save it to your profile.

In this article, Simon Hurst draws on recent advice in Excel Zone about creating KPI dashboards, and demonstrates how to create a dashboard component using Excel 2010 Sparklines and an external data range table.

The first thing we'll need to do is create the data that our dashboard will display. We've used the Crosstab Query Wizard in the Microsoft Access sample database 'Northwind' to create a query showing sales by salesperson by month for a chosen year:

MS Access Crosstab query
 
From the Excel 2010 menu, choose Data, Get External Data, From Other Sources, From Microsoft Query. We'll find our Access query and use all the fields. We won't return the data directly to Excel, but rather create a parameter query by choosing the option to ‘View data or edit query in Microsoft query’. From the Microsoft Query screen we choose Criteria, Add Criteria and add our OrderYear field – we set the value to [Year] – the square brackets make it work as a changeable parameter rather than a fixed value:

MS Query - Add Criteria dialogue
 
We'll then be asked for a 'real' initial value for our [Year] parameter – we'll use 2006. Remember to click the Add button, then close our query and 'Return Data to Microsoft Excel' as a table starting in cell A1 of our existing empty sheet. Our data should look something like this:

Excel 2010 Dashboard - initial data
 
Excel 2010 automatically creates our External Data Range as a table. We'll add a totals column immediately to the right of the existing table. If we enter the first SUM() formula in cell O2, the formula will automatically be copied to the other rows. Then we set the sort order to ‘Sort Largest to Smallest’ to give us a league table of salesperson results.

We now select the block of figures in the ‘month’ columns C2:N11 and choose Insert, then Column from the Sparklines group. We place the Column charts in the column next to our total:

Excel 2010 dashboard with sparkline charts
 
Now we select columns B:N and hide them. This leaves us with the Salesperson, Total and Chart columns. You may find you have an absence of charts. If so, click one of the cells where there should be a chart and then, from the Sparkline Tools, Design tab choose the Edit Data dropdown and the Hidden and Empty cells option. Turn on the ‘Show data in hidden rows and columns’ option:

Excel 2010 Sparkline Hidden and Empty Cell Settings
 
You should now have a set of charts next to your table:

Excel 2010 KPI sparkline chart
 
We'll now implement our 'Year' parameter. Select any cell in the External Data Range. From the Data ribbon, Connections group, choose Connections. Select the data source for the External Data Range and click on the Properties button, then the Definition tab and Parameters button.

Excel 2010 External Data Range Propertis - parameters
 
Rather than typing in a value directly, we choose to get the value from a cell. In order to update our table and charts automatically when a new year value is entered, we turn on 'Refresh automatically when cell value changes'.

We can now enter a different year, and our table and charts should change accordingly:

Excel 2010 KPI dashboard updated for different year
 
Other articles that deal in more detail with some of these techniques and features include:

Replies (2)

Please login or register to join the discussion.

avatar
By johnellis
22nd Apr 2010 18:01

Excel ? 2010

Sounds great but when will Excel 2010 be released?

Thanks (0)
Simon Hurst
By Simon Hurst
26th Apr 2010 10:19

Office 2010 release date

As I understand it, it's being released in stages starting from about now with the retail version due to be available in June

Thanks (0)