Use Custom Formatting instead of Conditional Formatting in Excel.
Smart Uses of Custom Formatting
We normally use custom formatting to show numbers with thousand separator, as percentage or even to show green for positive and red for negative values. But we hardly ever use custom formatting to show deviations with symbols.
I compared the custom to conditional formatting using 40,000 lines of data and the custom formatting method was three times faster than conditional formatting.
Custom formatting is also faster to implement – even if you want to build in thresholds.
In this guide, I’m going to show you four examples where you can use custom formatting instead of conditional formatting, starting with a simple way of showing variances using symbols – up/down arrows or any symbol of your choice.
Custom Formatting: The Basics
Custom formatting is really easy to use when you understand the rule behind it. Once it clicks, you see all the opportunities to improve the presentation of your reports.
To access Custom Formatting, right-click on any cell, click Format Cells…, and in the window that appears, in the Number tab, go to Custom.
The main logic is this – there are 4 arguments – separated by ; – defining how the following elements should be formatted:
1: positive numbers ;
2: negative numbers ;
3: zeros ;
In this case, we will just focus on the first three arguments, and ignore text.
The hashtag (#) sign and the 0 sign are basically just placeholders for numbers. The only difference is the hashtag sign is a variable placeholder and 0 is a fixed placeholder.
As an example, let’s say we want a show any positive number that is typed in, shown as ‘plus’ and then a number. The first argument should be as follows:
The second argument – formatting the negative number –
“plus “#;”neg “(#);;
Negative numbers should be shown in brackets.
Next, how zero values should be formatted.
“plus “#;”neg “(#);”hide”;
Zero values will be hidden.
Leave the final – text argument – empty.
Now when I put in a positive number, 2, it shows it as ‘plus 2’.
-2 shows as ‘neg (2)’
0 appears as ‘hide’
Up/Down Arrows for Deviations
To add up and down arrows, go to Insert > Symbol.
Find them under Font: Arial, Subset: Geometric Shapes.
Insert the Down and Up Arrow out of the way in an unused cell. You will use them later for custom formatting.
Essentially, Custom Formatting is about making numbers appear as whatever you want.
This is why it works incredibly well for showing deviations.
Make the values appear as arrow symbols
To start, you need numbers, so do the calculation: Actual – PY.
Highlight Cell D6 to D17, press F2, and Ctrl+Enter.
Copy the arrow symbols you previously inserted.
Select Cells D6-D17 – where you want to apply the custom formatting – and press Ctrl-1.
Go to Custom, and in General, for the first argument – how positive numbers are formatted – paste the arrow symbols.
Positive values will appear as ▲, negative as ▼, and zero values as nothing.
Press OK, and the values will appear as arrow symbols.
Giving the symbols color
With the values selected, press Ctrl-1, and edit the arguments to say this:
Excel’s colors are language-sensitive, so you’ll have to translate [green], [red], etc. to your language.
Press OK, and the arrows now appear like this:
However, I find the green used here too bright.
The colors available are listed here.
10 is gentler on the eye.
Highlight D6-D17 once more, and press Ctrl+1.
Edit the arguments to say:
Press OK. Remember it’s language sensitive, so translate ‘color’ to your language.
The positive arrows have now changed to the new green.
Up/Down Arrows & Percentage Deviations
In addition to showing the deviation as arrows (as in the previous chapter), now we’re going to show percentages too.
To get the percentages, use Actual divided by PY:
Format this as a Percentage. Drag L6 down to L17.
While they are highlighted, press Ctrl-1.
Edit the arguments to say this:
Now we have deviations shown as arrows and percentages.
Add breathing space between % and arrow symbol
Highlight Cell L6 to L17, press Ctrl+1.
Edit the arguments, inserting two spaces before each arrow symbol.
[color10]0%“ ▲”; [red]-0%“ ▼”;
This has added two spaces between% and the arrow symbol.
However, if you want this space to expand as you increase the width of the cell, edit the arguments to say this:
[color10]0%“* ▲”; [red]-0%“* ▼”;
We’ve added a *, followed by a space.
The *, or asterisk, is syntax to repeat whatever follows it until it reaches the edge of the cell.
So this *, followed by a space, repeats the space as the cell’s width increases, so the breathing space is proportional to the width of the cell.
Video and Workbook
Watch these two examples being built in the video below, as well as the introduction to the basics of custom formatting.
Feel free to Download the Workbook HERE.
Next in Part 2 of this guide, I will show you how to use any symbol of your choice for deviations, and up/down arrows for higher variances.
Excel Dashboards that Inform & Impress