Save content
Have you found this content useful? Use the button above to save it to your profile.

ExcelZone Compendium: Formatting tips (Excel 2007 update)

4th Apr 2008
Save content
Have you found this content useful? Use the button above to save it to your profile.

Introduction: why formatting matters

Although accuracy and reliability may be far more important aspects of a spreadsheet to address than appearance, formatting if properly applied can make a spreadsheet easier to understand and serve to highlight important information or areas for concern and investigation. This compilation from Simon Hurst provides an overview of techniques that can help you in this department.

Clearly, for most spreadsheets, numbers are crucially important and formatting these correctly can make a tremendous difference to clarity and impact. Borders and colours when used sensibly can also be very useful – helping users understand how to use a spreadsheet, and thus aiding reliability.

More general formatting techniques such as wrapping and aligning text and dealing with column widths and row heights can also make it easier to identify and work with areas of data in a spreadsheet. Finally Excel's conditional formatting feature can highlight cells or ranges of cells automatically.

Solutions: Getting numbers to display exactly how you want to see them

This is an area that ExcelZone has explored often and the previous ExcelZone Compendium on formatting starts off with a detailed guide to the use of custom number formats and includes a suggested custom format to display negative numbers in brackets:
If you would like to display negatives in red, then just adapt the format to:

Custom number formats are accessed via the Number tab of the Format-Cells dialog (Excel 2007 – Home ribbon, Number section, Dialog button or 'More number formats' option from the format type dropdown). In all versions Format Cells from the right click menu might be a quicker way to get there. Custom is the bottom option in the Category List:

It is also possible to customise the format of the numbers available for selection from the Number and Currency items in the same category list. This is not done in Excel itself however, but via the Regional and Language Options in the Windows Control Panel. The Regional Options tab of this screen includes a 'Customize…' button. This in turn opens a further dialog that has tabs for Numbers, Currency, Time and Date. Perhaps rather confusingly, the Currency options are the ones you need to change to alter both the Number and Currency settings in Excel. So choosing one of the alternative displays for negative values (such as the use of brackets) or, for example, a different number of digits after the decimal point, will change the options in Excel:

Here we have changed two options in Control Panel and, after accepting the changes, the Excel Number category reflects the number of decimal places and negative number format set in Control Panel:

...and Dates

While we're on the subject of the Regional and Language settings in Control Panel, some entries in the 'Date' category are also affected by these settings – in this case those on the Date tab of the Customize Regional Options screen. Here in the Excel Format Cells screen you can see the dates that depend on the regional settings are marked with an asterix. So if Excel doesn't display the date format you need, you can either create a custom format, or go to Control Panel.

The formatting compendium also contains details of formats for displaying numbers rounded to thousands: #,###,_);(#,###,);-? and making a custom format available to new workbooks by including it in the Excel default template. This is named 'Book.XLT' and lives in the XLStart folder.

Solution: Allocate custom number formats to Format with Style buttons

A custom number format can also be allocated to one of Excel's styles. Three special styles - Currency, Percent and Comma have dedicated buttons, so allocating a number format to one of them will allow you to apply it with a single click. You can modify existing styles or set up new ones via the Format, Style option. (Excel 2007: Home ribbon-Styles section, then click on the drop down menu and right-click on the style you wish to change and click "Modify". The number styles have a section of their own towards the bottom of the drop down list).

Excel 2007 incorporates a wide range of cell styles that can be viewed and applied from the Styles section of the Home Ribbon. Helpfully, styles are already set up for a range of cell types that you might want to highlight - for example: Calculation, Check Cell, Warning Text:

Excel 2007 also has a set of themes together with Colour, Font and Effect schemes which can be applied to an entire workbook and which match with similar themes available in other Office applications. The Themes and schemes are found in the Themes section of the Page Layout ribbon.

Let Excel do the formatting for you

Prior to Excel 2007, the Format-AutoFormat option allowed you to apply a pre-defined format to a block of cells, automatically recognising heading and total areas. If you want to use AutoFormat in Excel 2007 you have to add the button to your Quick Access Toolbar - the option is not available via the ribbon. To add an item to the Quick Access Toolbar, rightclick on the toolbar and choose 'Customize Quick Access Toolbar', then choose commands from 'Commands Not in Ribbon' and search for your required tool in the alphabetic list. Apparently Excel styles have replaced AutoFormat.

The other way to get Excel to do the work is to use Format, Conditional Formatting – this can be used to apply formats to a range of cells based on their contents, or the contents of other cells in the workbook. Even before Excel 2007, conditional formatting could be very useful – particularly to introduce peril sensitive formatting into your workbook. Excel 2007 has dramatically improved all aspects of conditional formatting, including how easy it is to use. We have covered the new feature in detail already in Excel 2007 - Get ahead with conditional formatting.

Another 2007 addition is the Format as Table option from the Styles section of the Home ribbon. The important thing to note about this option is that it does a lot more than just change the appearance of a table. Again Excel Zone has covered the option in some detail: Introducing Excel 2007: Tables - Not just a pretty format.

Further reading
Colouring cells in an Excel spreadsheet dependent on a condition
Any Answers: Conditional formatting
Excel: colour coding tabs
Cell formatting in Excel (use of the camera tool)
ExcelZone Compendium Archive - more formatting queries and answers

About the author
Simon Hurst is a former chairman of the ICAEW IT Faculty and runs The Knowledge Base, a consultancy dedicated to helping practitioners make effective use of technology. He is also the author of '100 Time-saving Tips for Microsoft Office'. For more information, visit The Knowledge Base website. .

Subscribe to the ExcelZone NewswireSubscribe to the ExcelZone newswire
To keep up with all spreadsheet-related developments, click the button to subscribe to the ExcelZone newswire. You will return to the AccountingWEB home page when your name has been added to the mailing list.


Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.