Discovering Dashboards in Excel 2007

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

enter some sample numerical data. Now select

the range from

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

, from

and from

. Save your work as this data will be the data

that we will extract into the dashboard.

This is the basic data on which the dashboard solution will summarize

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

and

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.

This data area extracts the data we want to see from our data store

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