In this second part of this series Simon Hurst, FSN writer and spreadsheet guru will be looking at how some of these features exposed in last week’s article can be applied in practice. His book “Accounting spreadsheets to the MAX®” is due to be published by FSN early next year.
Dynamic references
Many Excel spreadsheets included references to blocks or lists of cells that need to adjust when further data is added. For example, you might be using Data, Validation, Allow List to enable your user to select items from an in-cell dropdown:
If your list source is a normal block of cells, then any new values added to the end of the list will not be included in your data validation dropdown. One of the most common solutions is to combine the Excel OFFSET() and COUNTA() functions to create a dynamic range – one which automatically adjusts to include any added cells. This method relies on ensuring the list is the only thing in a column and then using COUNTA() to count the number of non-blank cells in that column. As you add values, COUNTA() returns a higher number. OFFSET() then uses this value to determine how many rows to include in a rectangular region of cells. For example:
=OFFSET($G$9,0,0,COUNTA($G:$G)-1,1)
This form of OFFSET() starts at a certain cell – G9 in this case and then creates a rectangular block of cells. The second and third arguments indicate the distance from G9 in rows and columns of the top left-hand cell of the rectangle and the fourth and fifth arguments define the height and width of the rectangle in cells. So as COUNTA() gets bigger so does the height of our rectangle. We could use this formula as the source of our data validation list and, as we added countries, our dropdown choices would expand accordingly:
It works, but it is a little complicated to set up, and you have to be careful about how you enter other values in your column of countries – if you miss a cell for whatever reason then your dropdown will include the blank cell rather than the country.
As an alternative, in Excel 2010 you can just format your list as a table. Now our original, simple, formula will adjust to include new entries added to the bottom of the table:
Given that, at the end of Part 1 we were looking at the structured formulae available to refer to tables, you might wonder why we haven’t used such a reference for the data validation list. Strangely, there are several areas in Excel where you might expect to be able to use a table structured reference but can’t – the data validation list source is one of them.
The power of tables to allow references to them to adjust dynamically has many other uses. Consider a chart of company results where you want to add the current month’s figures as a new column each month. Once again, you could create dynamic ranges to use as the chart source data as described above, but turning the source data into a table would allow a ‘standard’ Excel chart to automatically incorporate new months as they are added:
Now we add the figures for April and our chart adjusts automatically:
More than text and numbers
Another feature of tables discussed in part 1 was the application of formats and conditional formats to new rows of data. Combining this with some of the new conditional formatting functionality in Excel 2010 can create a table with automatic graphics.
In this example we have added a column to show the difference between the generated amount and the estimate. We have then applied a ‘data bar’ conditional format to that column and turned off the display of the values by selecting ‘Show Bar Only’ from the Edit Rule dialog. A new feature in Excel 2010 allow us to work with negative values in a data bar conditional format. We have set the colour of the negative bar to black and fixed the axis at the cell midpoint:
As we enter new daily values, the Over/Under bar will be generated automatically. Our ‘Generated’ column also includes a conditional format to highlight the maximum value in the column – in this case the 21 on 1 April.
Finally, another combination of tables with a new Excel 2010 feature – the in-cell ‘sparkline’ charts. We have created a column in our monthly data table to show the trend over the year. We have selected our block of monthly figures, then used Insert, Sparklines, Line to create a series of sparklines showing the trend of our figures. Because we are in a table, as we enter further columns of data or even rows of data, out sparklines will update automatically:
Then:
