As Sandra Vogel has pointed out, Office 2010 has just been officially released. Whenever a new version of
Microsoft Office is launched, a handful of important new functions and features
tend to monopolise attention leaving lots of less significant minor
improvements to sink without trace. This is an attempt to trawl through a few
of the changes from 2003 to 2007 and 2010 that you might have missed.
Conditional formatting in PivotTables
The dramatic improvement in conditional formatting was one
of the Excel 2007 headline features, but it is also implicated in a less
obvious improvement, applying conditional formatting to a PivotTable. As an
example, if you have a PivotTable with a large data or values area, you might
want to apply a conditional format to help you identify significant values more
visually. Before applying the format it’s usually a good idea to turn off grand
totals so as not to skew any conditional formatting that relies on overall
values. Then just apply the chosen conditional formatting to any cell in the
data/values area. The result will be disappointing – only the selected cell
will have the conditional format applied, however you should see a smart-tag
that you can click on to choose to apply the format to the whole area. The
following example combines this technique with the ‘show symbol/bar only’
option that was covered as part of a previous conditional formatting article on IT Counts:
Positive news on negatives
The ability to apply conditional formatting to a PivotTable
was a change from Excel 2003 to 2007, Excel 2010 sees further improvements in
conditional formatting compared to Excel 2007. Keeping with our bars example,
in Excel 2007 if you were working with a set of values including both positive
and negative values then the minimum bar position would default to the lowest
value whether it was positive or negative. In Excel 2010 there is a new option
for working with conditional formatting bars in the ‘edit rule’ dialog:
‘Negative values and axis’. This lets you choose different colours for bars
representing negative and positive values and to decide whereabouts the ‘zero’
value axis should be positioned within the cell:
