We all had great fun the last couple of weeks giving the ribbon and the User interface disaster a richly deserved kicking.
So to regain a bit a of balance I thought this post should focus on the good stuff in Excel 2007.
To summarise: the Excel product team added some useful features, the ribbon team made them hard to use.
I’m not going to do a bunch of user guide style stuff here, its 2009 FFS use your search engine (or decisions support system) of choice to find the many good ones already written.
My favourite new features are:
- Remove duplicates – simple and effective, and a mild PITA before
- Pivot tables – they made them easier to use for nooBs and maintained the old skool for those that fear change. I didn’t like the way the task pain (sic) encroached on my screen – then I discovered you can tear it off and float it like a dialog – best of both worlds – neat.
- The new conditional formats are handy enough – I know some are underwhelmed, but the 2007 ones do make the 2003 ones look, well, so 2003.
- IFERROR and SUMIFS, not too many new functions, but these 2 are massively useful. The CUBE functions are okay too, if you have access to a suitable data source. They are so much neater than GETPIVOTs. Making the ATP stuff native is v useful actually – now you can be sure the user will not get #NAME? errors if you use EOMONTH etc (they may get #N/A data loss in .xls files but we already covered that way back.)
- The black colour scheme is easy on the eye.
- The status bar has loads of useful info now, including the zoom which is handy, if someone nicked your scroll mouse for example.
- COM add-ins now shows HKCU AND HKLM – no more sneaky COM add-ins messing things up. NOTE: if your Excel 2007, or other 2007 app, is acting flaky COM add-ins have proven to be the number one suspect.
- Sharepoint integration, I’ll cover this some more but the Office 2007 client + Sharepoint (even just sharepoint services) story is pretty damn good.
- Tables – these are actually worth using – unlike lists in 2003. Great as a pivot source – the table expands as new rows get added, and that is reflected in the pivots without needing to change the data source, just refresh.
- Structured reference in tables are neat too, especially once you have worked out the relative/absolute effect of dragging v copy pasting
- The sort and filter stuff seems a bit more obvious now, and is easier to use, and pretty smart (sort A-Z – Excel sees a columns of text, Sort largest to smallest, it sees numbers, earlierst to latest it’s dates – handy early warning when you mess up your data types).
- I think Find is a bit more obvious too, especially what used to be tucked away behind select special.
- The name manager is much better than the previous name editing dialog.
- Function autocomplete, I think will lead to less silly errors with things like using the 3 argument lookup when the table is not sorted.
- Trusted locations give the best of both worlds – I don’t get nagged about my own VBA but anything not in my safe directories gets a warning.
- being able to make the name box wide enough to see useful names is good.
- being able to extend the formula bar down to see other peoples dreadful long formulas without hiding column letters (or names if you’re in a table) is handy.
- Multithreaded calc, and larger memory addressing sound good, I havent really seen the impact yet, but I expect I’ll be working with those sorts of monsters soon enough. Shame MTC will never include VBA UDFs, better VBA UDF performance would be brilliant. 2010??
- What others have I missed?
Having read this far, I’m sure you are wondering if I can complete a post about Excel 2007 without a dig at the UI.
(3rd sentence doesn’t count surely?)