Charts are a great way to explore data. Here is some American baseball data showing player salaries over a five year period.
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:
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.
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.
Now, chart the data that doesn’t move.
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.
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.
Now, the scrollbar controls the chart. Here is the baseball spreadsheet for you to play with: Baseball_offset.xls Have fun!
Note: this post is adapted from a presentation I gave at eMetrics 2007 in San Francisco.