Exploring Office 2007: Discovering Dashboards in Excel 2007 Capturing the Dashboard Images and Creating a Combo Box Helen Bradley
»
Capturing the Dashboard Images
Step 6
To create the images to display on the dashboard you will need to use the Camera tool. This is not displayed by default in Microsoft Excel 2007 so you will need to add it to your Quick Access toolbar. To do this click the Office button, choose Excel Options > Customize, from the Choose commands from dropdown list select Commands not in the ribbon and locate the Camera icon. Click Add to add it to the Quick Access toolbar and click Ok.
Step 7
Select the area from cell G2 to K6 and click the Camera icon to take a picture of those cells. Click the Dashboard tab to activate the dashboard worksheet and click once on the worksheet to add the picture to the sheet. Click outside that picture so that it is no longer selected and return to the Data sheet.
This time make a selection around the edge of your chart so that you select all the cells over which the chart appears. You need to do this because you cannot select a chart and take a picture of it using the camera icon but you can select a range of cells under and around a chart and take a picture of the chart by taking a picture of them.
Click the camera icon once to take a picture of the cells and the chart, click the dashboard sheet to activate it and click once on the sheet to paste in the picture of the chart. Arrange both pictures neatly on the sheet. These pictures are linked to the data area and chart on the data sheet and when the data sheet values and chart change, these 'pictures' do too.
»
Creating a Combo Box
Step 8
The Combo Box tool is another tool that is not on the Excel 2007 toolbar. To find it, click the Office button, choose Excel Options > Customize, from the Choose commands from dropdown list select Commands not in the ribbon and locate the Combo Box (Form Control) icon. Click Add to add it to the toolbar and click OK.
Click in a cell on the dashboard worksheet and click the Combo Box (Form Control) button that you added to the Quick Access toolbar. Click and drag a Combo Box onto your worksheet. Right click and choose Format Control.
In the Format Control dialog's Control tab, set the properties for the Combo Box. Set the Input Range to the range M2:M4 on the Data worksheet, set the Cell Link cell to cell H1 on the data worksheet and set the Dropdown lines value to 3. Click OK. The data picture will be filled with errors and chart will now be empty, this is because nothing is selected in the combo box and is to be expected.
Click outside the Combo Box, select the arrow to the right of the combo box and select a product from the list. When you do this, the data and chart will immediately change to show the data and chart for the product that you selected.
You can select any of the three products from the combo list and the chart and data will alter automatically because selecting a name in the combo list changes the value in cell H1 on the data sheet and accordingly changes the data in the table and chart.
If you format the data area on the Data sheet, the images captured by the camera will appear formatted on the Dashboard. You can also do add formatting and headings to the Dashboard itself if desired.
»
Creating Your Own Elements
This process shows the basics of creating dashboard elements that display a neat and tidy front end to somewhat complex data stored elsewhere in a worksheet.
Your dashboards do not have to summarize data stored in the same worksheet, as the data and the data that you display could come from queries drawn from an Access database, from web queries, or Excel lists.
The benefit of using a dashboard solution is that it can summarize extremely complex data into a simple display which shows only the information you want or need to work with. While our example is very simple it illustrates the power of creating dashboard solutions for extracting complex data and displaying it in a user friendly way.
Helen Bradley is a respected international journalist writing regularly for small business and computer publications in the USA, Canada, South Africa, UK and Australia. She blogs at http://www.projectwoman.com/blogger.html.
Be sure to check out all of Helen's articles in the Exploring Office 2007 series:
