Formatting spreadsheets

Don’t save good formatting ONLY for the results of your work. Instead, focus on your spreadsheet as a whole. Deciding on a few rules for formatting will make your analysis more comprehensible to your team.

For example, shade raw input data cells one colour, calculations another, parameters a third; use consistent formatting for headings, units and annotations. You can do this with Excel’s built-in themes, but it’s not necessary and sometimes less transferable. It is more important that you get the rules straight in your head. Excel has an amazing tool to help you do this called Conditional Formatting.

Conditional Formatting

Conditional Formatting changes the format of a cell dependent on the content of that cell, or a range of cells, in the workbook. It helps users to focus quickly on important aspects of a spreadsheet, highlight errors and identify important patterns in data.

You can find the Conditional Formatting options in the Styles group of the HOME ribbon tab. This dropdown includes two sets of basic rules (Highlight Cells Rules and Top/Bottom Rules), three graphical forms of conditional formatting (Data Bars, Colour Scales and Icon Sets) and, at the bottom, more detailed options (New Rule, Clear Rules and Manage Rules).

Applying Rules

The Highlight Cells Rules cover a range of different cell types. In this example we have used a Duplicate Values rule to highlight all Unique Order IDs (slightly counter-intuitively, Duplicate Values includes the ability to highlight both Duplicate and Unique values). The Rules screens include the criteria for the rule to be applied and the format to use. There is a short list of suggested formats, but the Custom Format option provides access to the full range of cell formats. We have also used the Between rule to highlight all Order Dates in 2011.

A Text that Contains rule has been used to highlight all Company Names that include ‘mercado’. Finally, a Greater Than rule has been used in the Total column. Rather than use the suggested value or a typed in value, we have selected a cell containing our threshold value (in the below example cell G1 which has a value of 500). You can then see in column G any value above 500 is highlighted in Green. Having a separate cell for the threshold cell is also best practice as it makes it much easier to see, and change the conditional threshold value:

 Enjoying these top tips?  There is a whole host of posts on AAT Comment to help  you get up to speed here. 

Paolo Lenotti is the Head of Marketing & PR at Filtered.com.