This document is, with the introduction of conditional formatting partially outdated, as for simple tasks there is now a more accessible dialogue; however if you are not using the most recent version of EXCEL or another spreadsheet this is the only simple solution.

Custom cell formats are a simple yet powerful tools to visualize specific aspects of a variable or table, i.e. focusing on a specific question of interest.

Excel offers many predefined cell formats. (Number group in Excel 2007, menu item in earlier versions) Most of them do not matter here, as we are mostly dealing with numerical data. As the most common use for spreadsheets are balance sheets, one of the most common format is the one that shows negative numbers in red. You can select that format in the "number" category from the "number" tab. In the same dialog box you can also modify the number of decimals.

Make sure to understand: Formats are only dealing with the presentation of the data ("text attributes"), not the data (cell content) itself; cells still contain the original value, even if you do not see it or only part of it...

In order to create your own custom format, click the small arrow at the right bottom corner of the Number group to display this dialogue, then select Custom.

The format can have up to four parts separated by a semicolon, namely

in the format below for e.g. Urbanization

only two sections are specified: The first part is for positive numbers, the second for negative ones. The actual numerical format for both is 0.0. The two elements in brackets modify the format for positive numbers. [RED] is of course the colour; [>50] is a conditional expression modifying in fact the definition of a positive number, i.e. only values >50 are to be considered positive numbers.

• A second example [>50]0.0;"." has the same conditional expression; the negative numbers however will appear as simple ".", i.e. the number is actually not shown. Note that you have to use "." as " " is not a valid number format.
• Instead of using ".", you might also use [WHITE]0.0, i.e. the result would be white numbers, i.e. numbers you cannot see as long of course as the background is also white.
• [>50]"+";"-" produces a "+" for numbers >50 a "-" for the others.
• " " Positive numbers are shown as space, i.e. no value is displayed. Can also be useful with e.g. conditional formatting where a particular background is used and you do not wish to see the numbers.
• You could used [WHITE]0.0 that paints positive numbers white, i.e. on a white background you do not see them, however when highlighted the numbers are shown.
• Find out more about Custom Formats, using Help, you will find quite many subtle possibilities. to customize the display of cell contents.

Please note that these are just examples meant to stimulate your creativity. You however should note that cell formats cannot do much more than what we have shown here, besides requiring a rather cumbersome notation.

If you are however prepared to go into a bit of programming using the built in Visual Basics for Applications language (preferable to the now old fashioned macro language) you would be able to build simple applications with buttons. You could then perform tasks like hiding parts of the observations completely from the displayed table, colour certain values based on information typed in through a menu and so on.

The limit is your imagination and the investment you are prepared to make into learning some more advanced features of EXCEL.