Why make 100 charts when one will do?

Charts are a great way to explore data. Here is some American baseball data showing player salaries over a five year period.

Baseball salaries by team over time

Charting this data with a line chart would allow us to see trends in salaries by team. However, when we use Excel’s default chart, we get something that looks like this:

Excel’s default multiline chart

That’s quite a mess. It would be a lot easier if we could create one chart for each row.

The OFFSET function is going to help. In its simplest form the OFFSET function works like this:

OFFSET(anchor, rows from anchor, columns from anchor)

That is, OFFSET will start with the anchor cell, go down a number of rows from that anchor and over a number of columns and return the value it finds.

OFFSET function

We can use the OFFSET function to create cells that pull a single row of data out of the table dynamically. We create a new row atop of our data and create a series of OFFSET functions that all rely on a single cell (the big yellow one) for their row offset. So changing one cell will pull different rows of data into our fixed location.

Creating a dynamic row that doesn’t move

Now, chart the data that doesn’t move.

Charting the dynamic row

After fixing the chart, we’d like to make it easy to change the value in the big yellow cell.

We can use Excel Forms to build a lightweight user interface. Bring up the Excel forms toolbar by rightclicking on any toolbar and choosing Forms. Place a scrollbar beside the chart.

Excel Forms

Right clicking on the scrollbar allows you to Format Control. Link the control to the cell that is controlling all the row offsets. Now, moving the scrollbar will update the chart.

Chart with scrollbar
Selecting Format Control
Formatting the scrollbar control

Now, the scrollbar controls the chart. Here is the baseball spreadsheet for you to play with: Baseball_offset.xls Have fun!

On the way to 100 charts

Note: this post is adapted from a presentation I gave at eMetrics 2007 in San Francisco.