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

EXCELZONE COMPENDIUM: Formatting

by
15th Jun 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

More than 400 Excel tips and queries are collected in the The ExcelZone Compendium. This article highlights some practical pointers on working with the various different formatting options in Excel. Our thanks to all of the contributors who have made this guide possible. If we have missed out any vital tips, feel free to add your own suggestions using the Post a Comment button at the end of the article.

Show numbers exactly how you want to see them with custom number format
THE Excel formatting issue for accountants is apparently how to properly format negative figures with brackets The question has been asked and answered dozens of times ' thanks to all those who have contributed their ideas and suggestions. The consensus seems to be to use a custom number format similar to the following:

#,##0_);(#,##0);-?

If you would like to display negatives in red, then just adapt the format to:

#,##0_);[red](#,##0);-?

The subject has cropped up so often it is worth going through it in detail.

To apply a custom format to a cell or range of cells first select the cells. Now choose Format, Cells and click on the Number tab. Select Custom at the bottom of the list of Categories. You can now look through the list of existing custom formats to see if there is already one set up to do what you want. If not, either start from scratch or find the nearest one and adapt it. You could just select the example above and use Control-C to copy it, and Control-V to paste it into the Type box.

Custom number formats have four parts, each separated from the next by a semi-colon. The four parts are used as follows:

First ' sets the format for displaying a positive number

Second ' sets the format for displaying a negative number

Third ' sets the format for displaying a zero

Fourth ' sets the format for text

Usually we only need to worry about the first three when formatting numbers.

When entering our custom number format '#' is used as a place holder, so #,##0 allows us to show that we want to use a comma as a thousand separator. The final character is entered as a 0 rather than a # so that .5 will be displayed as 0.5 if we choose to display decimal places.
The underscore '_' is followed by a character to cause the format to leave an amount of space equal in width to that character. So _) will include a space equal to the width of a closing bracket so that our negative numbers will line up exactly with our positive numbers.

'?' leaves a space equal to the width of a number and can be used to add spaces for insignificant zeros. In our case we just use it so that the dash we use for the display of zero values will be inset slightly from the right hand edge of each cell.

To format a section in a particular colour, just include the colour, surrounded by square brackets, at the beginning of the section.

A custom number format is only available in the workbook in which it was created. To make it available to all future workbooks, add it to the Excel default template. This is named 'Book.XLT' and lives in the XLStart folder.

More number formats
If you need to work with European number formats, anon suggested doing nothing in Excel, but instead relying on the 'Regional and language options' within Control Panel. Other respondents came up with alternative solutions using custom number formats overcoming the problem of using a full-stop as a thousand separator by surrounding it in double quotes.

Format numbers rounded to thousands by using the custom format: #,###,_);(#,###,);-?

The toolbar buttons showing a pile of money, a percentage sign and a comma, apply the currency, percentage and comma styles as defined in the Format, Style option.

Format the entire worksheet
Chris Bales showed how to format an entire worksheet in one go. Click the tile at the top left intersection between the vertical row numbers and horizontal column alphabetical headers to select the whole sheet (or use the Control-A keyboard shortcut), then make all the basic page selections, for example: font, font size, cell height, cell width, cell format, cell alignment, basic colour scheme, text wrap and others.

Using conditional formatting to highlight important data
Conditional formatting turned out to be the answer to several Excel issues. Nilesh Mandvia described how to use conditional formatting to highlight price increases or decreases by comparing one cell value to another. Alan Webb donated some very extensive chunks of VBA code for situations where Conditional Formatting's limitation of 3 conditions is too restrictive ' his examples show how to define whatever ranges and colours you need. Neil Eglintine showed how Conditional Formatting can be based on a formula rather than a straightforward cell value comparison. He suggested using the formula: =$A10=1 within a conditional format applied to a whole row, so that the whole row would be formatted according to the value in cell A10.

Andrew Banks showed another use of Conditional Formatting when he suggested 'Cell Value Is' 'less than' '=TODAY()+7' as a method of highlighting approaching (or elapsed) deadline dates.

Format cells correctly to work with data and time calculations
Chris Lloyd explained how to calculate the difference between two times as a decimal number. When you work with times and dates in Excel, you can subtract one time from another or one date from another, but the answer will be formatted as a time or date respectively. This can be confusing ' if you subtract 05/06/06 from 09/06/06 you will get the answer: 04/01/1900. Whilst at first glance this appears ridiculous, it is simply the number of days between the two dates, but formatted as a date. Because Excel works on the basis of 'serial numbers' for dates, the number 4 is treated as the 4th day since the beginning of the last century. To display the value as a number of days, or as a fraction of a day for times, simply choose Format, Cells and choose a number format. If you are working with times, and need a value in decimal hours, multiply the result by 24 to convert days to hours.

Text formats
Perversely, the text formats live in the Alignment tab of the Format, Cells dialog box. Here you can indeed set the horizontal and vertical alignment for your selected text cells ' and even make you text diagonal, but you can also set an indent, turn on text wrapping and shrink to fit and merge cells.

When entering headings, it's often a good idea not to use multiple rows, as this can complicate things like graphs and PivotTables ' instead enter the text in a single cell but use the 'Wrap text' option to make it flow onto additional lines within the cell. Alternatively, you can type Alt-Enter when you need to start a new line within your text, and this will automatically enter a line feed and turn on text wrapping.

To the right of the alignment buttons on the formatting toolbar is the 'Merge and Centre' toolbar button. This will merge a selected group of cells and centre a single item of text within the merged area. If the merged area includes more than one cell containing text, the top left hand entry only will be used.

AutoFormats
Use Format, AutoFormat to quickly apply one of a range of formats to a table of data. Incidentally, if you are careful not to include blank rows or columns within your table of data, you can just click on a single cell within the table and when you apply the AutoFormat it will automatically apply it to the whole table. At the bottom of the list of AutoFormats is a 'None' option which you can use to remove an existing format.

Copying formats
Use the 'Format Painter' toolbar button (a picture of a paintbrush) to copy the format of a selected cell to one or more other cells. Just click on the cell whose format you want to copy, click on the Format Painter, then click on the cell, or drag across the range of cells, to which you want to apply the format. Double-Click the Format Painter button to allow you to apply the format to multiple ranges of cells. Double-Click again to turn the Format Painter off again. The Format Painter will copy normal and conditional formatting. David Carter used the 'Format Painter' to format PivotTable drill-down worksheets, whilst Gail Perry showed how to copy the formatting of an entire worksheet.

A more complicated and slower way to copy formatting is to Copy the cell with the format you want to copy, and then use Edit, Paste Special and select the 'Formats' option.
To fill formats to adjacent cells, drag the cell fill handle with the right mouse button and choose 'Fill Formatting Only' from the menu, or for more recent versions of Excel, drag with the left mouse button then click on the Smart Tag and choose 'Fill Formatting Only'.

'and things you can't easily do
Steven Foster pointed out that whilst you can't fill a cell with different colours, you can achieve the same effect by using the drawing tools. Alan Thomas went on to add that you can change the font attributes of individual words and characters within a cell by selecting the word or characters within the cell then using the toolbar buttons or Format, Cells dialog to apply the required format. Lots
of people
suggested ingenious ways to achieve this effect where a formula is involved rather than just plain text. Michael Haig recommended using a cartridge of white ink to print white border lines in Excel'.

Related articles

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By ugdiv
04th Jul 2006 16:11

How do I colour cells containing calculated dates
Cell A1 contains a date.
Cell A2 = A1+1
Cell A3 = A2+1
etc.

How can I format Saturdays and Sundays in RED automatically, so that when I change the date in A1 it reformats the other cells?

Thanks (0)
avatar
By John Murphy C.A.
22nd Jun 2006 18:03

Bring Back Lotus 1-2-3
Oh for the days when you could show numbers with commas and brackets with virtually 1 keystroke using Lotus.

The same applied to printing a document - click on the print preview icon, select either fit all to page, rows to page, columns to page, or custom - hey presto, it was a doddle.

If Lotus is no longer sold, can't Microsoft use the same easy options or am I over simplifying things here?

But then again, I do still use Lotus because of the above - I can always convert Excel files to Lotus after all if I want to.

Thanks (0)
Simon Hurst
By Simon Hurst
24th Aug 2006 18:01

Conditional formatting
Hi Richard - sorry for the delayed response. Try applying conditional formatting to the range of cells using the 'Formula is' option rather than the 'Cell value is' option. Set the formula to something along the lines of =WEEKDAY(A1,2)>5 where A1 is the first cell in the selection. Hope it works.

Regards

Simon Hurst

Thanks (0)