Excel is about measuring and calculating but it is not only just numbers it can also help you make the information more understandable and user friendly. In calculations we often get a result 0 instead of any positive or negative digit or number. If you desire highlighting this result and instead of a digit 0 you want a text string like “ZERO”, “Nill”, “Nil” or “-” or just anything than excel can do this in the blink of any eye.
You can achieve this output in place of 0 using different techniques like formula with IF conditions but the easiest of all is to use custom number format
Custom Numbers Format – Where math obeys YOU!
Custom number format is one of the advanced features in Excel and has much significance in the hands of experts and this side kick has great impact when it comes to preparing reports and dashboards in excel.
Coming to the point, to turn 0 in your desired text do the following steps:
- In home tab > Number group > Click drop down menu arrow > click More Number Formats…
OR - In the same Number group you have small icon at the bottom right corner. Click it and the same dialogue box will open
From the dialogue box at the left select “Custom”. Once there you will be able to see your number, which is 0 in our example, in the “Sample” window. Just under Sample window you have the heading ‘Type:” and under that a field in which by default General is written.
Remove General and put this code instead:
0;-0;"ZERO"
This code actually means:
- Keep the positive numbers as they are
- Keep the negative numbers as they are and show minus sign at left
- If result is 0 then print ZERO.
If you desire to have something else in place of ZERO then replace it with whatever you desire. Snap easy!
One thing to remember however is that you have only formatted the digit 0 to ZERO and not actually replaced it with text. It is still a number in nature you have only given it a new face. As an evidence you can see that even the text is right aligned. To confirm further you can use the same cell in sum function and function will execute as normal because excel knows its a number.
If you have excelled at Excel then please like / share and spread this one minute tutorial on custom number formatting.
brilliant
It can also be so when you manage percentages:
0.00%;-0.00%;”ZERO%”
Excellent
Really helpful
good