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 ;
4: text
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:
"plus "#;;;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.
Press OK.
Now when I put in a positive number, 2, it shows it as ‘plus 2’.
-2 shows as ‘neg (2)’
0 appears as ‘hide’
Hiding the zero values
Press Ctrl-1, and edit the arguments to say this:
"plus "#;"neg "(#);;Keep the last argument empty.
Now, even though the cell value is 0, it’s not showing anything.
That’s the basics.
NOTE: If you want to add color, they should be added to arguments in this way:
[Red]Featured Course
Excel Essentials for the Real World
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.
Cell D6 =B6-C6
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:
[green]“▲”; [red]“▼”;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:
[color10]“▲”; [red]“▼”;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:
L6 =J6/K6-1Format this as a Percentage. Drag L6 down to L17.
While they are highlighted, press Ctrl-1.
Edit the arguments to say this:
[color10]0%“▲”; [red]-0%“▼”;Press OK.
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%“ ▼”;Press OK.
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.
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.
Check out THIS ARTICLE form Michael. He goes in a lot more detail on how custom formatting works. Make sure you download the file at the bottom of the post.
In addition, the VIDEO for THIS ARTICLE takes you through specific scenarios on how you can apply custom formatting to your Excel Reports.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
