A couple of quick Excel 2010 discoveries
September 24, 2009
At the moment I’m revisiting pretty much all my course materials for my Microsoft Office training courses, partly to restructure them into different chunks, and partly to start work editing where necessary to include coverage of Office 2010 so that I will be ahead of the game when that gets released next year.
Along the way I’ve been finding out loads of cool things about major new features such as sparklines and slicers (more on that in a future post, as promised), and the ability to customise the Ribbon easily without writing code. There are also lots of tiny changes as well, which are easy to miss and may get drowned out in the sea of other news about the next version, so I thought I would mention a couple of them here – the status bar summaries and filters in Tables.
Subtotals ignore previous subtotals – so does the status bar in 2010
As you might already know, if you use a SUBTOTAL function to calculate a cell, and then use another SUBTOTAL to calculate cells including the first one, the original subtotal is excluded from the second one, which makes sense to avoid double counting / summing.
If cells A1,2,3 contain 1,2, and 3, and A4 is =SUBTOTAL(9,A1:A3) then A4 will have the sum: 6 (‘9’ is the sum sub-feature of the SUBTOTAL function). If cells A5,6,7 contain 5,6, and 7, and A8 contains =SUBTOTAL(9,A1:A7) then this will have the sum of A1,2,3, 5,6,7 and skip the value in A4 as it realises this would give a wrong result. This works even if you use different functions for the totals, for example you might use counts (function 2) for most subtotals, then use an average (1) and a sum (9) right at the bottom or top of the column.
Now, you can select the same 7 cells and take a look at the status bar – this should show you the sum, count, average or whatever functions you have chosen. In 2003 and earlier you can select one at a time, in 2007 and later you can choose to see multiple summary results at the same time – in all cases, right click where it has a summary at the moment (eg “Sum: 123”) and choose the functions you want to see.
If you have 2007 or earlier, the status bar shows the ‘wrong’ result of 30 – the sum of all 7 cells including the subtotal. While this is a correct sum, it is unhelpful, particularly if you select a large number of cells or a whole column, and may not realise there are one or more subtotals in there. Selecting cells A1:A8 is even worse, you now have two subtotals included for a whopping error of 54.
In Excel 2010 this behaviour has changed and these functions in the status bar ignore SUBTOTAL formulas in cells just like the function itself does, so the count shows 6 and the total 24. You can see the difference in the status bar of the two screenshots below, for Excel 2007 (left) and Excel 2010 (right) (click for larger images). The other features like Average and so on do the same, but I could not show too many at once on the same screenshot.
Now, on the one hand this is a great idea because in general I think this is the result most people would actually want and even expect. But on the other hand, there are thousands of existing users who already know and understand how this works, and might find the change undesirable, if for no reason other than the fact that it is not obvious. It does save time because you don’t have to select all those cells separately using CTRL-clicks or a GoTo > Special (if the numbers are really numbers and the only formulae are the subtotals).
Maybe a better way to offer this functionality would be to add “Subtotal” to the list of status bar features you can display, with the expectation set that this does the same as the function of the same name (unlike count, which is more like COUNTA; you have to use “Numerical Count” to get the same as COUNT. Don’t get me started on that one!).
Tables in Excel 2010 show filters when the headings row goes off the screen
If you have used Tables in 2007 at all, you may have been pleased at how the column labels A, B, C… get replaced with the headings from your table if you scroll down so the headings are no longer visible while you still have a cell in the table selected. Frustratingly though, the autofilters which are very helpfully added to Tables automatically are not available. you have to scroll all the way to the top to put a filter on, then scroll to take a look at your data and perhaps the totals, then up, down, until your scrolling finger aches.
Well, ache no more – in Excel 2010 the filters are also shown in the column headings so you can filter wherever you are in your table, as shown in the screenshot below. A very simple, but very useful additional feature this one, I am sure you will agree.
In the context of these two features, I should also point out for anyone not already aware, the Total row in a Table uses the Subtotal function rather than a sum, count, average etc, so this means it will show the total for the visible rows if you filter it (which makes sense), and will get ignored in the status bar in Excel 2010 but not previous versions. In the example above I have selected the whole of column C (sales) to include the total row, but the status bar shows the more useful result of 71,261, rather than double this as would be seen in Excel 2007.
Anyone else found any little nuggets that have changed? What are you favourite improvements to Excel 2010 (or the other Office apps, come to that)? Anything missing that you wish had made the cut this time round?