An alternative to conditional formatting: custom formatting

Conditional formatting is often used to apply styling to cells that satisfy certain criteria. By making important values stand out, it's great for analysing data quicker.

However, anyone familiar with the Conditional Formatting window will know how easily it gets clogged up with a gazillion different rules. Using it excessively can also slow down your workbook.

There is an alternative, though, which may suffice in certain situations: custom formatting.

Many people have found it more efficient and lightweight when applying formatting to a large range of cells.

In this example, we have a table of sales reps with their figures for last month and this month. We also have a column showing the changes as percentages.

We can improve the appearance of the column by carrying out the following:

1. Select the range
2. Open the Format Cells window (Ctrl + 1)
3. On the Number tab, choose the Custom category
4. In the Type field, input [Color10]0% "▲";[Red]-0% "▼";
5. Select OK

Positive values now display in green beside a ▲, negative ones as red beside a ▼, and zeros as blanks. [Color10] refers to one of 56 colours supported by custom formatting, and [Red] is one of eight standard names. Unfortunately, the limited colour palette is based on early versions of Excel, so we can't access the 16.7 million found in the main colour window of Excel 2007 and beyond.

The ▲ and ▼ symbols are featured in Insert > Symbols under fonts like Arial. Simply double-click on each to insert them in the active cell so they can be copied and pasted across.

For more Excel tips and tricks like this, check out our Video Tutorials page.