We step through the process of creating a Dashboard chart and data display in Excel 2007 for more efficient analysis of and quick access to your critical information.
when you are working with data in Microsoft Excel the data you have to work
with won't be presented in a way that you need it to be. There are a number of
options for displaying your data and one of these is to use what is commonly
referred to as a dashboard.
Excel Dashboards are a front end for your data so that, instead of
looking at all your data, you look at the subset of the data that you are most
interested in. In an enterprise environment dashboards are often created by
technical specialists and provided to users so they can see just the data that
they need to work with. However, some techniques employed in creating a
dashboard are simple enough that you can use them to analyze and display your
own data.
In this article we will step through the process of creating a dashboard
chart and data display for some hypothetical data from a business. We suggest
that you create the data and follow, step by step, as the dashboard elements are
created so you get a feel for how the process works.
Once you have completed your first solution you will have the skills and
understand the workflow for creating your own dashboard elements. In the process
of creating the dashboard element we'll look at some interesting Excel features,
including using functions to extract data from a data store, capturing and using
pictures of your data, and learning how to create a custom drop-down list from
which you can select the data to view.
» Preparing
the Data
Step 1
To get started, open a new Excel 2007
workbook. You will need two sheets in the workbook, one called Dashboard and one
called Data. To name your sheets, double click on the Sheet tab for each and
type the new name for the sheet.
Step 2
So you have some data to work with type these entries into
these cells on the sheet called Data:
Cell: Value:
A1 Office
B1 San Francisco
A3 Product A
A4 Product B
A5 Product C
B2 Q1
C2 Q2
D2 Q3
E2 Q4
In the range
B3 to E5 enter some sample numerical data. Now select
the range from
A1 to E5 and copy that range to cell A8, A15 and A22.
Enter these values into these cells:
Cell: Value:
B8 Houston
B15 Phoenix
B22 Sacramento
You should have four identical sets of data down the left side of your
worksheet representing the four offices. Take a little time to create a
different set of numbers in the area from
B10 to E12, from
B17 to
E19 and from
B24 to E26. Save your work as this data will be the data
that we will extract into the dashboard.
» Extracting
the Data Step 3 We will extract the data to display on
the dashboard on this worksheet before continuing. So, to create an area for the
extracted data to appear, into these cells type these values:
Cell: Value:
H1 1
H2 Q1
I2 Q2
J2 Q3
K2 Q4
G3 San Francisco
G4 Houston
G5 Phoenix
G6 Sacramento
As you can see the data that we are trying to extract has a different
arrangement from the data in the main data area. To extract the data for this
new table we can use a formula. This is the formula that goes in cell H3:
=OFFSET($B$3,CHOOSE($H$1,0,1,2),0)
This data uses the
Offset and
Choose functions to extract
the Q1 values for the San Francisco office for Product A. These functions give
us the ability to fill the table using copied formulas rather than having to
build each individually.
To create the
remainder of the formulas, copy the formula from cell H3 into cells H4, H5 and
H6. Alter the reference to cell $B$3 in cell H4 to read $B$10, alter it in cell
H5 to read $B$17, and in cell H6 to read $B$24. You can now copy the range H3 to
H6 across the worksheet to column K to fill the table.
The data area now extracts the product details for Product A for all four
offices from the larger table.
Step 4
The dashboard will display a chart based on the data we
have extracted so, to do this, make a selection around the area from cell G2
to K6 and create a simple column chart based on this data. Format the chart
as you want it to look.
Step 5
Now that you have the data extracted on the data sheet you
can test the formulas by typing the number 2 in cell H1. Notice that the data
and chart change to reflect the result for Product B those offices and quarters.
If you change cell H1 to read the number 3 you will see data for Product C.
While this is useful we can add even more functionality to the solution by
placing the data and chart elsewhere in the workbook and not on a sheet which is
cluttered with other data. In addition, we can provide our user (or ourselves)
with a combo list from which to select the product to view.
Before we do this, add three more cell entries:
Cell: Value:
M2 Product A
M3 Product B
M4 Product C
»
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. You can learn more
about her at her Web site, HelenBradley.com
Adapted from winplanet.com.
| Do you have a comment or question about this article or other small business topics in general? Speak out in the SmallBusinessComputing.com Forums. Join the discussion today! |
