Good Stuff in Excel 2007

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:

  1. Remove duplicates – simple and effective, and a mild PITA before
  2. 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.
  3. 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.
  4. 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.)
  5. The black colour scheme is easy on the eye.
  6. The status bar has loads of useful info now, including the zoom which is handy, if someone nicked your scroll mouse for example.
  7. 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.
  8. Sharepoint integration, I’ll cover this some more but the Office 2007 client + Sharepoint (even just sharepoint services) story is pretty damn good.
  9. 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.
  10. Structured reference in tables are neat too, especially once you have worked out the relative/absolute effect of dragging v copy pasting
  11. 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).
  12. I think Find is a bit more obvious too, especially what used to be tucked away behind select special.
  13. The name manager is much better than the previous name editing dialog.
  14. Function autocomplete, I think will lead to less silly errors with things like using the 3 argument lookup when the table is not sorted.
  15. 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.
  16. being able to make the name box wide enough to see useful names is good.
  17. 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.
  18. 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??
  19. 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.

Yes

Cheers

Simon

(3rd sentence doesn’t count surely?)

Advertisements

11 Responses to “Good Stuff in Excel 2007”

  1. Harlan Grove Says:

    I like tables, but there’s SO MUCH MORE that could be done to make them really useful. For instance, something Lotus 123 was able to do since 1991: define a table by an ODBC query. Corollary to this, make the resulting table read-only, so it’d be impossible to alter it via editing cells, but allow structured referencing into it in outside cells. Some added syntax to filter on table fields would be nice too.

    As for long formulas in Excel, the longest type of formulas I use are of the form

    =CHOOSE(MATCH(SomeWorksheetName,ListOfWorksheetNames,0),
    ‘worksheet 01′!X99,’worksheet 02′!X99,’worksheet 03′!X99,’worksheet 04’!X99,
    ‘worksheet 05′!X99,’worksheet 06′!X99,’worksheet 07′!X99,’worksheet 08’!X99,
    …)

    I realize Excel isn’t a 3D spreadsheet and is likely never to come close to the 3D functionality Lotus 123 achieved 2 decades ago, but wouldn’t it be nice to have a new INDEX-like function that took a 3D reference as 1st argument, then row and column index as 2nd and 3rd arguments, then SHEET index in the 3D reference as 4th argument, and maybe an optional 5th argument for result orientation when the 2nd and/or 3rd arg (row and column) are/is positive integers but the 4th arg is 0? That is, give it the capability of returning 0D (single value, aka scalar), 1D and 2D slices from 3D references? With a 3D index function, I could change the formula above into

    =INDEX3D(‘worksheet 01:worksheet 30’!X99,1,1,
    MATCH(SomeWorksheetName,ListOfWorksheetNames,0))

    which would go a long way to shortening many of my formulas.

    As for color schemes, not sure I’d include that as a benefit of Excel 2007. Lots of people pissed off in newsgroup posts due to Office 2007 ignoring OS-level color settings. Basically this is MSFT telling users that MSFT know how to make things look better than the users themselves. Maybe so, but it’s just a wee bit arrogant.

  2. Charles Says:

    All good stuff if you can find them in that damn ribbon :-)

  3. Adam Vero Says:

    I agree!
    Remove dupes is great, even just having a simple built-in conditional format for highlighting them is great too (no more ugly countif formulas to do this).

    Pivot Tables already rocked, hopefully the changes to the interface to interact with them make them more accessible so more people can feel the rapture (I do really feel passionate about how simple and brilliant pivot tables can be for chewing data or providing semi-self-service reports)

    Quick and easy conditional format tools make them nice and easy and discoverable, but can lead to some poor practices further down the line – for example icon sets which break at percentage values of the current range being used to compare from one report to next months, when they have different meanings because the underlying set of values changed. Customising these to have fixed value meanings is not obvious to beginners.
    It’s also really nice that you can now have multiple layered conditional formats, not needing to use stop if true if you don’t want to, but again I find that some users are adding new layers when they really meant to replace the previous one (or extend its range).

    Tables! Give me more tables! They do great things for beginners and advanced users. Copying formulae down in a consistent manner, copying conditional formats (so you don’t have to worry about how to extend that range, see above). Instant total rows.
    Showing table column headings in place of normal worksheet A-Z labels when you scroll down is genius!.
    Wonderful and simple, easy to use, once built they become fairly robust so you can trust people not to break them too easily.
    Wishlist: shortcut key to insert table row above, or at bottom of table (maybe there already is one I just can’t discover). Fed up having to go to bottom right cell and hit tab.
    Wishlist 2: table-specific subtotalling functionality. Maybe not vital because so easy to pivot a report off, or use filters, but sometimes inline subtotals would be nice. In this context of a very user-friendly table they could be great. Personally I don’t use subtotals in normal data ranges at all)

    Default formatting of tables and pivot tables however leaves a bit (a lot?) to be desired. Too much ink, colour for decoration not clarification. When will Excel get some zen minimalism by default, and formatting only when I ask it to? (yes I know I can make my own custom formats for tables and save them and have them as the default in my templates, but I should not have to know this or how to achieve it, it should be better practices by default).
    Makes me think of clippy: “You look like you are creating a table of data, would you like me to add some technicolour jazz, add dark backgrounds and reversed-out text to use up your ink/toner more quickly while making this more unreadable and less professional looking? How about zebra striping _everything_ regardless of context? Maybe use pastel coloured text for your most important data? Have you gone blind and/or mad yet?”

    I love the “smart filtering” – just changing the language used to describe “A to Z”, “smallest to largest”, “oldest to newest” is simple but effective to help people relate to their data.
    Adding better filtering for dates was a great idea – drill down by year, month etc rather than hand-picking dates or going for a custom query (how many users hit “cancel” in that dialogue in panic? or accidentally ommitted the end dates of a range?)
    Being able to choose “this year”, “last month” and other natural-language options is a real bonus and much overlooked I fear. Same goes for above average, top 10 etc, and text filters like “begins with” and “contains”, right there off the menu. Simple tools that hopefully will gain ground as user experience beds in.

    A couple you didn’t include that I find useful:
    Protect a worksheet on the worksheet right click context menu. The most logical place for it as far as I can see.

    Exposing some of the current page setup choices on the ribbon (OK, that’s ribbon, not other functionality). Having a proper “automatic” setting for scale to fit, rather than use-a-big-number kludge.

    Making headers and footers look and feel a bit more like a document and less like a report-writing tool. Users transitioning from familiarity with Word into using Excel can make more sense of this. Page layout view that is closer to print preview (without the driver / accurate rendering overhead until you actually need it).

    Making it a bit more obvious how to create worksheet local defined names by adding the “scope” picklist to the New Name dialogue, rather than having to know you add this as part of the name. This won’t get in the way of people who only want workbook global names, but it handy for those of us who want something out of the ordinary.

    Default colour palette for charts is at the very least less gaudy and horrible (what the hell was that plum colour all about?), at best actually quite pleasant and balanced (no more grey background either!). Lots of other things wrong with charts and manipulation of them (cue Jon…) but out-of-the-box, insert-chart-with-defaults produces reasonably good looking results.

    Not forgetting the file format – way smaller thanks to various optimisations (re-using strings makes a big difference to some of mine where I have thousands of identical formula based on implicit ranges) and zipping of course. Just getting to grips with pulling the xml apart and hacking it back to ge

  4. Dick Moffat Says:

    I don’t have any more features to add – looks like you have them all IMHO.

    My vote for favourite new feature has to be the “Tables” and their attendant “Structured References” that are far and away my favourite new features. Although I agree with the comment that there were Table capabilities that Lotus included years ago that would be nice to have, this is still a great step forward.

    On the other hand I fear that in our world of no training or discipline of any kind in the use and management of spreadsheets, not many will ever stumble on this new stuff. And I mean “stumble” because that’s how people find out about things in Excel. It always amazes me how few have ever “stumbled” on Pivot Tables after all these years :-).

    Biggus

  5. sam Says:

    I know I am being a bit picky but

    Tables don’t expand automatically.
    Add a new line below the table and it expands.

    Leave a gap of one row, add data , delete the blank row and it does not expand…

  6. Simon Says:

    Sam, yeah it is a bit picky, but its still a good point. This new ‘sort-of automatic’ stuff has crept in all over the product and its not always clear when certain features pick up changes.
    Data validation is the same

  7. Jon Peltier Says:

    Tables in 2007 are okay, but I don’t find much benefit to them over those lists in 2003 (which was the killer feature that got me to upgrade to 2003). They format automatically, which is good or bad depending on how much you like any of the formats. The table referencing is at first a blessing, then a major curse when it becomes clear that they behave much differently than you’d ever expect. Probably when I’ve used them more, I’ll think it’s great again.

    Much of the rest of Simon’s list is good.

    And Adam, you’ll have to poke me with a sharper stick to get me going, at least under this post.

  8. dougaj4 Says:

    One significant additional benefit (in my experience) is that large worksheets (in one of the new formats) open quicker, and save much quicker than in earlier versions, in addition to being much smaller. I also find that 2007 is more stable with large worksheets. Many people on the Web report the opposite, but on my system that is what I find.

    I could comment about the performance of VBA UDFs, but since we are being positive this week, I won’t.

  9. Charles Says:

    Excel 2007 SP2 seems to have fixed the VBA write-to-a-cell slowdown.
    The benchmark
    http://www.DecisionModels.com/downloads/Variant_Benchmark.zip
    shows Excel 2007 SP2 to be up to ten times faster writing from VBA to Excel than unvarnished 2007.

  10. dougaj4 Says:

    Thanks for that Charles. I had a very quick look and concluded that there was no improvement. I’ll take another look at it.

  11. Charles Says:

    The version of 2007 I tested is
    12.0.6504.5001 SP2

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: