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:
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:
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 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:
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:
You should now have a set of charts next to your table:
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.
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:
Other articles that deal in more detail with some of these techniques and features include:
- New Excel 2010 features: Sparklines
- Presenting financial figures with Excel Part 1 - an introduction
- Presenting financial figures with Excel Part 2 - charting choices
- Presenting financial figures with Excel Part 3 – delivery techniques
- Excel Zone Compendium: Working with accounts data (Excel 2007 update)
Replies (2)
Please login or register to join the discussion.
Excel ? 2010
Sounds great but when will Excel 2010 be released?
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