Conditional Formatting

Spotting particular values in a large table of numbers or text can be difficult – but fortunately Excel has a neat way to help you do this using a technique that’s been considerably enhanced in the 2007 version. It’s called Conditional Formatting and it enables you to apply specific formatting automatically to cells that meet certain conditions. So, for example, if you want all cells in a table that contain values that are above 1000 to have a bold red font, and a pale blue background, then the system will do this for you. You don’t have to identify the cells yourself. In previous versions of Excel you could have up to three conditions so particularly high values could be formatted in one colour, particularly low ones in another colour and in-between values in a third colour. Excel 2007 allows you to specify an unlimited number of conditions – not only that, but you now have greater control over how the rules are applied. In previous editions, if Excel found a condition that applied to a cell, then it wouldn’t look for other conditions. Now you can tell it to check other rules, and specify the order in which it does so. The new edition goes further by incorporating data bars, icon sets or color scales that appear in cells. These indicate how the value in a cell relates to others in a highlighted range. You can use conditional formatting to show duplicate entries in a list or dates that occur next week. The options are almost endless – just don’t get too carried away!

So what else is new?

Well, for one thing, you can now create much bigger worksheets. If you're one of those few people who've found the 256 column limit a bit restricting, then you'll be pleased to hear the Excel 2007 allows you to have up to 16,384 columns - with the right-hand one now being column XFD rather than IV as in previous versions. Not only that but you can now have up to 1,048,576 rows - 16 times the previous limit. The good news is that the new format means that the files take up less space. For example, one of Excel files that I use in my training courses is 157k in 2003 format, but occupies only 85k in the 2007 version.

Of course, size isn't everything - and there's a lot more for the new user to discover. In particular, if you're a conditional formatting freak then you're going to be very appreciative of the way that Microsoft have enhanced this widely-used and well-loved feature. I'll go into it in more depth in a subsequent post but, very briefly, you can now have as many rules as you like (rather than the limit of three on previous versions), you can change the order in which the rules are applied, and you can include data bars, colour gradients and icons. The new version also includes a mini toolbar which appears either above or below the short-cut menu that get's displayed whenever you click on something that can be formatted. This gives you instant access to the most frequently used formatting options. If you don't like it, you can turn it off.

A number of new functions have been added to Excel 2007. If you've used the SumIf function in previous versions, which enables you to add cells that meet a particular criterion, then you'll be pleased to hear that the new version has an AverageIf function which works in a similar way. Yes, I know you could use an array formula to get round this, but the new function is much more straightforward.

I'll just mention two more changes. One is that you're no longer restricted to the number of different colours you use in an Excel file. The other is that pressing the Alt key now displays the short-cut letters (to be used in combination with Alt) against the various items on the ribbon.

The Ribbon

You know the feeling. You visit your favourite supermarket (make that "most frequently used supermarket" as favourite suggests an element of pleasure!) to find they've moved everything round. Suddenly, your 5-minute shop is turning into something that will involve 25 minutes of trying to find the items on your list, and then 5 minutes actually putting them in your trolley. Users of earlier versions of Excel will have a very similar experience on the first few occasions that they use Excel 2007. They'll feel sure that the commands that they want to access are there - but where? In their attempt to make the various commands more accessible, and more visible, Microsoft have put them into groups, and assigned the groups to one of seven tabs. Some of the tab names - Formulas and View are good examples - give a fairly clear indication of the sort of commands that they host, but others are less helpful. The Home tab features commands that are used most often (according to Microsoft) and so here you'll find a diverse mix of items which include copy/paste, formatting and sorting/filtering. There are also a number of commands - Open, Save, Close, Exit and Print - to name but five, which are accessed not via a tab but via the Microsoft Office Button in the top left hand corner of the screen. To some extent this replaces the File menu in earlier versions.
Unfortunately, you can't (unless you resort to programming) customize the ribbon. What you see is what you're stuck with. It's not all bad news though. There's a "Quick Access Toolbar" (QAT) just next to the Microsoft Office Button - and this can be customized so that it contains the commands you use most frequently. To add any command, from any tab, to the QAT, just right-click on the command, and select "Add to the Quick Access Toolbar". There's also quite a lot of help for new users on the Microsoft Office website at www.office.microsoft.com. Look for How-to tips and training then How-to resources then Get started with the 2007 programs. There's a useful interactive guide which you can download. Using this, click on a menu item in Excel 2003 and the guide shows you where to find the equivalent in Excel 2007. For a more permanent resource, download the Excel Ribbon Mapping Workbook which comes in the form of an Excel template file.