Microsoft Excel tip #1: reduce errors with conditional formatting

Here at Shooju we know first hand how stressful it is to find spreadsheet problems the night before a big project deliverable. Or worse: not finding those mistakes until they're too late. In our inaugural Excel tip, we'll provide examples of how to use conditional formatting to find and eliminate pesky Excel errors - saving time, money, embarrassment and most importantly, your sanity.

What is conditional formatting?

Conditional formatting allows you to format cells based on a set of specific parameters or conditions. For example: if the value in this cell is greater than 5, make it blue.

You can access conditional formatting on the "Home" tab in Excel. Once opened, selecting "new rule" will reveal a list of options that include "Format all cells based on their values" or "Format only cells that contain". Try them out and see how they work. Some examples are provided in this video.

Error reduction techniques

There are three ways to use conditional formatting to find errors: find binary (yes or no) errors, find duplicate values, or find outliers.

Find binary errors

Here, a rule was created to highlight any cells containing calculation errors bright red:

Examples of other binary errors include:

• letters (strings) where there should numbers (integers)
• values that should be true but are false (or vice versa)
• cells over a max value (or under a min value)

Find duplicate values

Below a rule was created to any value that appears more than once - also in red:

Find outliers

Establishing a color range allows you to detect values that are well outside of the parameters of the others. In this example below, the value highlighted in red is two orders of magnitude higher than the others in the sheet.

Get more on conditional formatting from Microsoft, Chandoo and GCFLearn.