• 
• 
• 
• 

 1 of 4

Gallery

The complete guide to Office 2010: Excel

Posted on 23 Feb 2010 at 17:37

Finally, power users of Excel may well approve more than any others of the ability to customise the Ribbon (again, this feature is available throughout the suite). If you have a particular set of tools that you use, or want to share with all your staff, then it’s a good enough reason to upgrade to Excel 2010. It’s easy to do, too: right-click on the Ribbon, choose “Customize the Ribbon...” and then follow the interface.

ADVANCED ANALYSIS

It sounds like a horror movie, but Slicer is Microsoft’s name for the excellent new data analysis tool that brings the power of PivotTables to the masses. For anyone unaware of PivotTables, these allow you to analyse data in much more intuitive ways than staring at a flat table of results, and they’re designed so that you never interfere with the original data.

For example, you might keep track of invoices in Excel. Let’s say you have five freelancers who work for you, across a variety of different projects. When you enter that data, you’ll have a table with the date of the invoice, the fee, the worker’s name, the project to which it relates, and no doubt more besides. With PivotTables, you can slice and dice that information: see what each person contributed to each project, say. Then, using drag and drop, you can view the cost of the project per week.

However, PivotTables are complex enough to scare away the majority of Excel users. Even if someone else has already prepared the data, there’s a steep learning curve that will put off most people. Slicers bring the power of PivotTables into a much more accessible form, allowing non-Excel experts to split the data “live” in a way that will impress everyone in a board meeting.

In appearance, they’re like an array of buttons that float above the spreadsheet. So, in our example, we’d simply select the button for our chosen project to see the relevant results. We can then see exactly what each person contributed, in each week, by selecting those buttons.

We’re most impressed by the idea of a dashboard of results. This could sit on a new worksheet (as PivotTables do) and will allow the presenter to dissect results there and then before the audience, responding instantly to questions in a way that’s much more difficult with PivotTables. The dashboard could also include charts of results: essentially PivotCharts, which operate on precisely the same basis as PivotTables, but present results in a graphical view.

If your data analysis needs go several steps beyond even this – if you're using multiple sources of data from multiple offices, for example – then take a look at the PowerPivot plugin.

Finally in this section, a note for anyone who has ever hit the 2GB limit of Excel files. The 64-bit version of Excel 2010 doubles this limit to 4GB, which may well be the feature you’ve been dreaming of. However, note that SharePoint 2010 still limits file sizes to 2GB, so Microsoft has some work to do before convincing us that this is reason enough to upgrade.

THE WEB EFFECT

Excel is probably the biggest beneficiary when it comes to co-authoring. While Word 2010 users can simultaneously edit documents only if they’re sharing files via SharePoint, Excel 2010 users can co-author via Microsoft’s Web Apps.

Together with the Web Apps’ ability to retain formatting, formulae and advanced features such as Slicers, this brings the power of Excel out of the office and onto the net: no matter where your salespeople are, they can manipulate the data to meet the needs (and questions) of their audience.

Excel 2010's key features

• Sparklines
• Slicer
• Improved Conditional Formatting
• Paste with Live Preview
• PowerPivot
• Web-based co-authoring
• 64-bit support

Author: Tim Danton