Excel
02 Dec 2008
Dec
29
Microsoft Excel 2007. The best new features: Part 3 - Data
by David Harper, CFA, FRM, CIPM
Tools |
In Part 1, I briefly reviewed three usability features in the new Excel 2007 (user interface, big grid, file operations). In Part 2, I looked at three styling features (rich conditional formatting, smart tables and easier illustrations).
In this third post, I will review my three favorite data-related features in the new Excel: pivot tables, easier calculations, and better connections.
Data #7: Pivot tables
Few users took advantage of pivot tables in previous versions of Excel. Excel 2007 improves on pivot tables by making them much more user-friendly. Creating a basic pivot table is very easy. The previous mechanism of dragging custom fields into the pivot table is replaced with a cleaner idea: four “drop zones.” You drag the desired fields into one of four drop zones: a Report Filter, Column Labels, Row Labels, and Value. As long as the dataset is properly constructed, it is very intuitive to slice-and-dice your data dynamically with the drop zone interface.
Data #8: Calculations
David Gainer gives a handy update on new functions at Microsoft's Excel 2007 blog. The Analysis ToolPak is integrated as “first-class citizens”; i.e., financial functions like correlation, covariance, and regression are now built-in. Microsoft added five new functions, including IFERROR() which simplifies error checking.
But my favorite additions are structured references, nested functions and the ability to evaluate functions. Structured references are a clean way of referencing ranges within a table, so that you might refer to =SUM(SALES[north]) to sum the cells of a column within a table. Nested functions are functions that use other functions as arguments. In combination with evaluate function, you have the ability to create cleaner, more compact function calls. Finally, I really like the evaluate function tool. This lets you step through a formula, one step at a time, to see how it evaluates to the final result. This is helpful for debugging.
Data #9: Connections
Better connections to external data are important because, as I’ve written many times, Excel is the most popular distributed database on the planet (even if that means at lot of ad hoc, flatfiles). Therefore, Excel is the natural front-end to a enterprise business intelligence system. I rely on a web services connection to an external server of XBRL data, and I expected Excel 2007 to give me a few hiccups in connecting. But it was seamless. Mapping to XML data is basically unchanged, but accessing web services is more intuitive.
I admit I have not used the ability to share workbooks as interactive reports, by uploading them to the Microsoft SharePoint server. But I included in my list of favorite features because, if it works as described, then it’s a killer feature because your Excel worksheet/workbook can basically be “served” as the host to other clients who can browse its contents dynamically. I can’t wait to see this in action!
That concludes a list of my nine favorite new features. Their my favorites so it’s a positive review, of course. I imagine that as I work with it more, I’ll find things that irritate. But so far, as I run it alongside Office 2003, I much prefer the new version.
Comments
Be the first to leave a comment!
Leave a Comment