• 
• 
• 
• 

 1 of 4

Gallery

The complete guide to Office 2010: Excel

Posted on 23 Feb 2010 at 17:37

Slicers, Sparklines and a range of other new features bring relief for Excel number crunchers

On the surface it doesn’t look as though there’s a great deal of difference between Excel 2007 and Excel 2010. A couple of additions to the Ribbon, the new Backstage view and a handful of bonus features such as Sparklines. And it’s certainly true that an “average” user could switch from 2007 to 2010 without batting an eyelid.

Anyone that handles data, however, will appreciate the additions made to Excel, many of which are designed to bring the power of data analysis to those unwilling to dedicate hours working out how to use what appear to be impenetrable tools. For instance, if you’ve steered clear of PivotTables because they look too complex, this could be the release for you.

SPOT & POLISH

Before we delve into the world of pivoting data, though, there are a few nice innovations in Excel 2010 that will make it easier for people to understand what the figures in front of them are really trying to say.

The first is the all-new Sparklines. Let’s say you have a simple row of data, such as the sales figures for the past month. Select that data, click on the Sparklines Line chart graphic in the Insert Ribbon, and you can place an instant line chart to the right of the data.

Alternatively, it can be used to display a simple bar chart or, if the data refers to a simple yes/no (for example, in-class attendance), then you can create a so-called Win/Loss graphic next to each row of results.

We should also mention Excel’s improved Conditional Formatting (as
an aside, it’s worth noting here that Microsoft’s marketing team has clearly become involved when naming new features: hangovers from previous versions of Excel, such as Conditional Formatting, sound dry and dull; compare that with the dynamic and exciting Sparklines, or the addition of Slicers, mentioned below). Introduced with Excel 2007, this allowed anyone to click on a table of results in a spreadsheet and apply formatting to the cells containing the data: for example, the greater the value, the “hotter” it becomes.

This worked in certain cases, but Microsoft received negative feedback about this feature. For example, look at the image on the right showing the profit increase of a company division by year. On the left-hand side, you can see the best Excel 2007 can do: a gradient colour from left to right. With Excel 2010, you can see negative values, and thus which division head to fire.

Regular users of Excel will also be pleased with Paste with Live Preview; this isn’t unique to Excel, but bearing in mind the problems formatting can cause in spreadsheets, it’s probably of most use in this application.

If you’ve copied a chunk of data from an email, for example, then you can choose whether to paste it in with the formatting inherited from the email, or with the formatting suited to the spreadsheet. If the data includes formulae, then you get extra options such as the ability to paste the values, the formulae or to transpose the data, and you see exactly how each choice will look as you hover over them.