I can hear the cries now. "But we just had to learn Excel 2007 a few years ago. Why do we have to learn something new now?" Because it's worth it, so simmer down and take a serious look.
There are three major reasons to ditch Excel 2007 for Excel 2010. The first only applies if you're using a computer running the 64 bit version of Windows (see the link that http://singletrackanalytics.info/?p=10 for description of what this means). If so, upgrading to the 64 bit version of Excel 2010 will give you much more room to work. All of your spreadsheets will fit into memory at the same time, so you won't have to wait around while they slowly update themselves from your disk. You can you can build larger models - not that that's always a good thing - but when you do you won't be constrained by memory limitations. For the serious Excel user, it's a much better working environment.
The other reason, though, is a free add-in called PowerPivot. PowerPivot offers several major enhancements to regular Excel pivot tables. First, you can do a "relational joins" like those in a database. This means you can link two sets of data together without using a VLOOKUP command. Once joined, both tables can be used in the pivot table as if they were a single table. You can join on multiple fields, too, which isn't possible with a simple VLOOKUP command. It's much easier and much less prone to error. You can also create calculated fields in the middle of the pivot table that take their value from formulas derived from other areas of the pivot table. For example, you can create a formula that computed each department's percentage of each month's total cost, which would be difficult in a simple pivot table. Or, you can compute the difference between the current year in the previous year using a PREVIOUSYEAR function, and can pivot the calculated value anywhere within the pivot table. These calculation can be done outside of the pivot table without using PowerPivot, but it's difficult to embed them within the table.
PowerPivot makes it easy, saves time and improves accuracy. The other reason to use PowerPivot is because of the massive amount of data that can be crammed into one spreadsheet. I've worked with the PowerPivot spreadsheet that included nine million rows of data, but that pivot instantaneously. Somehow, Microsoft figured out how to compress lots of data into a small space, yet retain amazing performance. This can be a game changer for organizations managing large datasets, but who still need to build models in Excel.
Another reason to look at Excel 2010 is the Data Mining add-in. (Yeah, I know it was available in 2007, but no one ever knew about it.) This is another freebie from Microsoft and it allows users to explore large sets of data, providing easy ways to obtain a high-level view of all of the data, identify and replace outliers, and then start looking for patterns in the data. You can evaluate whether certain events predict each other, where others often occur together or in sequence, or what types of data have similar characteristics. We've used it to analyze which diagnoses are usually reported together, which can create checklists that can help improve coding accuracy. It's cool, it's powerful - and it's free.
There are also pivot table slicers that make it easy to design dashboard-type reports, "sparkline" graphs that fit a line chart into a single cell, performance improvements and a bunch of other new features. The user interface is almost the same as in Excel 2007, so you won't have to re-live the pain of the mental upgrade that you did a few years ago.
A popular TV theme began "Some people stand in the darkness, afraid to go into the light". Those people need to move away from the old, familiar-but-timeworn versions of their most important professional tool and explore what's new out there. The serious professionals are already using this stuff. You should too.
