Dynamic Excel charts
Here’s a neat little trick to dramatically cut down the number of separate charts in a spreadsheet model.
Say you’ve got a monthly profit and loss statement with many rows of revenue and expense information. Once you’ve got all of your data in a worksheet you now want to analyse the information. Often you’ll want to select a particular line in the P&L and see a chart of the data. You could just create a nice looking chart of that row of data and position it somewhere on your worksheet. Then you could repeat the process for every row of data that you’re interested in. Well, this is lot of manual effort, plus it adds clutter to your worksheet. After all, you may only be interested in viewing one chart at a time. (Let’s assume for now that you don’t need to print these all out.)
One method to help reduce the clutter is to use a list box or combo box control on your worksheet and link this to your range of P&L line items. That way you can select the row you are interested in from the list box and see the relevant data displayed in the chart.
There are some problems with this technique though. You need to set up the List Box control and then make sure it continues to refer to the correct data when you change the structure of your model. It also requires that all of your row labels are in the same column – as the List Box control only looks in one column for its data. I like to use multiple columns from my row labels, to give a kind of nesting or hierarchy effect – so this List Box technique does not work all that well for me.
An alternative method is to get Excel to figure out which row you’re in as you move around your model, and then to use that row’s data in the chart. This is quite neat as it means I can just click on a cell to get the chart for that row of data. To do this you need to create a macro – but don’t worry, it’s probably the simplest bit of code you could write – in fact it is only 1 line of code. Here it is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) ActiveWorkbook.Names.Add Name:="xRow", RefersToR1C1:=ActiveCell.Row End Sub
All you need to do is go to the VBA editor (Alt-F11) find and double click the relevant worksheet in the navigation panel, and copy and paste this code. What it does is create a defined name “xRow” which stores the row number of the currently selected row in your worksheet. Every time the selection changes in your worksheet, this code will run and the row you’re in will be stored in the xRow defined name. You can then write a formula in your worksheet that refers to xRow.
Here’s the end result:
I also used xRow in conditional formatting to highlight in yellow the currently selected row.
One drawback of this approach is that this code to update the xRow defined name is running every time you move in the worksheet. This adds some overhead to your model, and may not be appropriate or desired for really big models.