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.

excel number format formula excel custom number format millions


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:



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:


Format 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.

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.

Free Excel Download

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

Use these techniques in your own reports

Unbeatable value!



  1. […] If you missed it, check out Part 1. […]

  2. Stephen November 17, 2017 at 11:11 am - Reply

    Brilliant! Very easy to understand and follow! Thanks Leila :)

    • Leila Gharani November 17, 2017 at 3:50 pm - Reply

      You’re welcome Stephen. Glad you like it.

  3. Hans December 8, 2017 at 2:05 am - Reply

    Hi Leila,
    You are simply amazing and demonstrates beauty of Excel with ease..


    • Leila Gharani December 11, 2017 at 7:54 am - Reply

      Thank you Hans.

  4. S. Rangasamy December 30, 2017 at 10:28 am - Reply

    Learnt the custom formating and found it to be useful. Learnt a lot of excel formulas and tricks after watching your videos and courses on Udemy. Thanks Leila.

    • Leila Gharani December 30, 2017 at 2:42 pm - Reply

      Thank you for your comment. Very happy to hear you find the tutorial useful.

  5. Peter G. Nganga May 30, 2018 at 2:47 pm - Reply

    Beautifully done. The revelation of Excel beauty. Thank you Leila Gharani.

    • Leila Gharani June 4, 2018 at 10:03 am - Reply

      You’re very welcome Peter.

  6. Dave Young September 28, 2018 at 5:48 am - Reply

    Thank you for all your videos, i am a small first time business owner and your videos are proving very helpful.
    you mention a friend (mark?) who is putting together a comprehensive list of custom format options.
    I want to know to change the color of the cell, as in the highlight color being rec or green or which ever?

  7. Asif Imam October 5, 2018 at 7:17 am - Reply

    Very easy and nice coustom formatting

  8. Yamini November 24, 2018 at 7:51 am - Reply

    “▼” Formula leaves symbol in asterics

    • Leila Gharani November 27, 2018 at 2:30 pm - Reply

      When you copy the symbol, make sure you copy it from font group “Arial”. This will ensure the symbol stays the same.

Leave A Comment

Share This