Formatting Numbers in Excel – Conditional Formatting Vs Custom Number Formatting

Accountants are always dealing with numbers. And its not always about highlighting positives. In some cases we have to make negative numbers standout and having a negative sign is just not enough. Similarly to show zero results as “Nill” or simply leave that field blank or being filled with a dash to replace boring “0” makes information much easier to read and understand.

To deal with such matters we have to step inside the reign of formatting.

When it comes to formatting in Excel. It can range from simple styling, shading and borders. And it can jump to a very complex application of features like conditional formatting and custom number formatting.

Both of the features i.e. conditional formatting and custom number formatting are capable of being bent to some really advanced formatting levels. But I will be explaining few of the simpler examples that will motivate you to learn advanced uses of these two features. My emphasize will be on negative numbers and zeros and in the process I expect that you will grab the basic idea and then apply it to your needs.

1 Conditional formatting

As the name itself suggests. This technique formats your data given the specific conditions. User is free to decide and make conditions and ask Excel to format the data accordingly. For example format cells in red that has values greater than 50 and so on.

To apply conditional formatting to separate negative, positive and zeros from data follow these steps:

Open the file you downloaded and head to worksheet named “Conditional Formatting”

Step 1: Select the data in variance column using mouse or keyboard. Just the numbers excluding heading.

Step 2: Go to Home tab > Styles group > click Conditional formatting drop down > Select New rule.

Step 3: A new dialogue box will open. Make sure the first option is selected. From format style drop down select 3-color scale.

Step 4: Now you will have three range boxes namely minimum, midpoint and maximum. Under each of the category from type drop down menu select number. In value field under minimum put -1, midpoint put 0 and under maximum put 1. You can change colors to suit your need or as you please. Once done click OK button.

This will color the cells based on the values contained in those cells. Negative values will be colored red, zeros will be yellow and positive values will be colored green.

Bonus tip: Add more conditions/rules

You might fancy that negative or positive numbers beyond certain number should be formatted even more differently. For example you are interested in separating such negative results that are lower than -300. Since this is an additional criteria, you can add this as an additional rule to existing formatting rule and it will override the original if condition specified is met i.e. results lower than -300. To do this follow these steps:

Step 1: Reselect the data of variance column.

Step 2: Go to Home Tab > Styles group > Click conditional formatting drop down and select Manage rules. Here you will be able to see the rule you defined above. Click New rule button and you will have the similar dialogue box as you had before.

Step 3: This time select the second option i.e. format only cells that contain

Step 4: Change the option in the second drop down from between to less than and in the value field mention -300.

Step 5: Click format button and it will open format dialogue box where you have tons of ways to format the cell differently. You can even change the text color and even do custom formatting if you like. However, I just shaded the box with a pattern. Once you are done. Click OK and now new rule is defined. Click Apply button and you are done 🙂

Bonus tip – Use Icon Sets

One of the good things about Excel is its ability to make data not just understandable but also visually readable. Conditional formatting allows you use to icons as well. With little arrows with numbers make things much easier to interpret. Like positive figure with arrow pointing upward, negative figure with arrow pointing downward and zeros with nothing or dash helps users to get through the data in a flash. To add icons to your data follow these steps:

Remember, you can add icons to already conditionally formatted data. Or you can also do it on a new data set. Procedure is same.

Step 1: Select the data. And go to conditional formatting drop down to add new rule by bringing the dialogue up.

Step 2: From the format style drop-down select icon sets. Afterwards you can select the icon style that feasts your eyes better 🙂

Step 3: In the bottom right section of dialogue box under the type options select number for all three ranges. Its important so don’t miss it.

Step 4: In the top value field type 1 and in the second value field type zero 0.

Step 5: Click OK and you are done. Now you have icons added with your numbers that can dance with the change in values.

2. Custom formatting

As you can see that conditional formatting is all very flexible and loaded with numerous possibilities which can you bend and trim according to your needs.

However, in some cases it slows you down unnecessarily and instead of helping it proves more of a obstacle in the way. In such cases, using custom formatting is a better option as it gets the picky jobs done much more easily and still capable of going serious about formatting. But still conditional formatting is much more easy. The reason is almost all the options of conditional formatting are CGU based whereas when you come for custom formatting you have to rely on strict syntax requirements and there is almost no help available on the fly. That is the reason custom formatting is left only for those who really know what it does and how it does.

One more thing to remember is that custom formatting comes with little more limitations than conditional formatting. For example in custom formatting you cannot change cell color easily, but to some extent are able to change the color of text/value itself. So lets have a look at few examples and you will understand why custom formatting is often over looked feature as compared to conditional formatting.

Show negative numbers in brackets

This one is easy and does not require much to do. Follow these steps:

Step 1: Select the data containing numbers using mouse or keyboard. And hit a shortcut Ctrl+1. This will open formatting dialogue with Number tab active.

Step 2: From the list select “custom”

Step 3: In the type field remove “general” and put the following syntax and click OK:

0;(0)

Above we are actually mentioning three separate criteria for positive, negative and zero values. Each condition is separated by colon “;” starting with positive then negative

You can see that condition meant for negative values is wrapped in brackets.

Now if you look at your figures, you will see that negative numbers will be represented with brackets.

Important

Remember one thing regarding formatting that it only changes the look of the value and not the value itself. For example after formatting you have dash in place of 0. It does not mean that zero is replaced with a dash. Instead it is still a zero and only front end presentation is changed. You can confirm it by selecting the cell and peeking at a formula bar. Formula bar will tell what the number actually is. Therefore, you can still add, subtract the number as normal even after formatting.

Hiding 0 to show “Nil” or Dash

To get the word “Nil” or a “-” in place o zero values. We need to change the syntax above a little by adding third condition meant for zeros. So follow these steps:

Step 1: Reselect the data and hit Ctrl+1 to bring the number formatting dialogue. In type field remove the current syntax and put the following in place:

0;(0);”Nil”

If you want dash in place of the word Nil then just replace that word with dash.

You can see that condition meant for negative values is wrapped in brackets whereas third condition which is for zero values contain a dash/Nil in quotes. So again, the first one is for positives, second is for negatives and third one is for zeros.

Now if you look at your figures in place of zero dash is reported. You can notice how quick it is as compared to conditional formatting and it also very much too the point.

Important

Remember one thing regarding formatting that it only changes the look of the value and not the value itself. For example after formatting you have dash in place of 0. It does not mean that zero is replaced with a dash. Instead it is still a zero and only front end presentation is changed. You can confirm it by selecting the cell and peeking at a formula bar. Formula bar will tell what the number actually is. Therefore, you can still add, subtract the number as normal even after formatting.

Show negatives in red color

Sometimes to make negative number appear more clearly you like to show them in different color, usually red. To get this done via custom formatting, again it is very easy and you need to make one small change. And this time change the syntax as follows:

0;[Red](0);"Nil"

So the only change is the addition of [Red] to the syntax in the second condition. To mention colour names you need to mention them in square brackets with the format criteria of value itself. One thing to remember is that you need to mention the colour name inside the criteria for which you want color to be different from default colour.

Show with arrows up and down

Yes this surely is crossing the boundaries now and putting the custom formatting feature to test and no wonder it is completely capable of it. Follow these steps to have arrows with numbers just like we can get using conditional formatting’s icon sets.

This time it is a little tricky so follow along 🙂

Step 1: Select the data using mouse or keyboard or a combination of both and bring up custom number formatting dialogue.

Step 2: If there is no syntax provided so far then provide give the following syntax:

0;(0);”Nil”

Step 3: Now have active cursor before 0 (i.e. the positive condition) and press and hold Alt key and on the number pad of your keyboard press 3 and 0 key and release the Alt key. This will insert a symbol of arrow pointing upward. Remember you have to press the keys 3 and 0 on the number pad or Num pad on the right of keyboard. The number keys on the top of keyboard won’t work for this purpose.

Step 4: Now move the cursor in negative condition and having it before the criteria for value is mentioned press and hold Alt key and this time punch 3 and 1 keys of number pad and it will insert the arrow symbol pointing downwards.

Click OK button and you can see the arrows with numbers. Where positive values have arrow up and negative values have arrow down.

Bonus Tip – Making arrows move to extreme left and numbers to the extreme right

OK until now we have learned some sweet stuff in custom formatting application. But the last technique learnt lacks aesthetics. Arrows are so close to numbers that it is cluttering the data instead of adding any value. To get this corrected we need to make a little adjustment.

Select the data again and open the custom number formatting dialogue box again. Put a cursor just after arrow symbol and put asterisk followed by space. Do this with both symbols.

What asterisk does is that it asks Excel to repeat the character mentioned after asterisk which in our case is space so arrow will be pushed to the left of cell and then cell is filled with space until the value starts. This makes the data look much better and visually easy to understand.

You can mention the color codes as well to make it look even better.