Excel || Number Formatting
Number formatting in Excel
Have you ever wondered how it's possible that Excel has not given you the right logical answer after applying a specific formula? That's very strange for one of the most powerful spreadsheets program in the world. Isn't it? Well, the fact is,it isn't a bug in Excel. The real problem is NUMBER FORMATTING.
What you see is NOT what you get!
Wow. Although the meaning of WYSIWYG (What You See Is What You Get) is that you really will print the data which is on your screen, I use this as a nice phrase to prove that this is not the truth in Excel. At least when you believe that your screen is telling the truth!
Metaphor
I always compare my Excel sheets with a house. It can look beautiful. Everything well painted, a proper garden, clear windows and so on. But when you open the front door you can come across the greates rubble heap you have ever dreamed of.
Practical Example
Comment: I translated this blog from my Dutch version so the figures/formulas have Dutch description. If necessary I will translate them.
In figure one you see a simple balance sheet which seems to be in balance. Both sides have a balance of € 110.750.
- Figure 1 (click on the figure to enlarge)
The question is if this is right or wrong. That's why we always have to check this kind of situations.
And one of the things you can do is applying a logical formula.
The outcome of a logical formula is alsways TRUE of FALSE.
So we gonna compare the debit (cell B8) with the credit (cell D8). To do this you can type in any other cell (e.g. F8) the formula:
=B8=D8 with a outcome of FALSE (ONWAAR = FALSE)
- Figure 2 (click on the figure to enlarge)
FALSE?
What happened. We have a balance sheet with a debit / credit of 110.750 and Excel is telling me that that is FALSE?
In such situations we need to do more specific investigation. And when we study the numbers we'll see in the formula bar that the description 'Bank' with an amount of 2,500 EURO is not 2,500 EURO but 2,499.57
- Figuur 3 (click on the figure to enlarge)
The number formatting without decimals is misleading us and gives us visual two numbers which seems to be equal. But nothing could be further from the truth.
WHAT YOU SEE IS NOT WHAT YOU GET
Changing number format
In figure 4 you will see the balance sheet again but with the two decimals
- Figure 4 (click on the figure to enlarge)
Concluding
You always (always) have to compare the content of a cell with the content of the formula bar (fig. 3) and have to apply a logical formula if you want to be sure that What You See Is What You Get.
In this example I have choosen to work with two decimals but in fact it can also be the 7th or 8th one.
Did you like this article? Please let me know. And if you do have any questions, please feel free to contact me.
And of course you can always visit my Dutch website ExcelXL.nl
Cheers and have fun
Jan Bolhuis
Recent Comments
2
Thank for sharing, Jan. I use Excel occasionally. And I think I can never master Excel. I got it now.
Hello Nam, You're welcome. Glad to read that this post give you a litte more insights.