Numbers are core to Excel, however their representation can be very different. In this section we will look at some basics of formatting numbers and get into of the basic formats excel supports
This article covers the following basics about formatting numbers:
- Format vs Value
- Comma Separator (for thousands)
- Quick formats
Most of this should be trivial for most Excels users but I wanted to cover this ground fist before going into more complicated formatting options
Format vs Value
It is important to distinguish a cell’s format versus it’s value: formatting only changes the visual representation of the cell, it does not modify the cell’s value. A clear example is with cell fill (the background), it should be obvious that even though we can change the background colour of the cell, the actual value doesn’t change. A 2 is still a 2. Same with the format of the number – whether we represent it as a whole number (no decimals), or a percentage the value of the cell doesn’t change. So if we choose to display 4.3256 rounded to 1 decimal place, we would see 4.3 but the value is still 4.3256:
As we saw above we can format how many decimals we want to see. For visual purpose it may be convenient to see only up to 1 decimal place, or just whole numbers.
There is two ways to set this, the first is you can right click on the selected cell to get to the Format Cells menu as such:
In the menu, you can select how many decimal places you want to have:
Alternatively you can simply use the buttons on the Home ribbon to increase or decrease decimal place one by one:
The button on the left increases the number of decimal places while the button on the right decreases the number of decimal places
The Comma Separator
It is often useful to represent large numbers split by commas, for example 12375 is a lot easier to read as 12,375. In Excel it is easy to do by simply clicking the check box in the Format Cells menu we saw before.
Follow the instructions to get to the Format Cells menu from the previous section, then on the screen where you selected how many decimal places to display, click the check box below:
One of the options we have in the Format Cells menu is percentage which automatically converts the number to a percentage.
Note that Excel multiplies the number by 100 before adding the % symbol, so 1 = 100% (so 100 would become 10000%). From this winow you can also control the number of decimals to display just like for a regular number.
Another alternative method to format the number as percent is clicking the percent button on the Home ribbon, found in the number section:
This will automatically set the percent format with no decimal places. From here you can increase or decrease the decimal places with the buttons like with regular numbers
Excel if often used for financial purposes, so when we want a number to be showed as currency (e.g. $), Excel has that built in just like with percentages.
Again back in the Format Cells window:
The default format for currency is two decimal places and $ sign. However you can always change the decimal places just like with regular numbers (either through this menu or through the buttons). Also you can choose your currency symbol if outside of a country that uses $.
Another thing to note is the ability to format the negative numbers in a specific way. The default is simply putting a minus sign in front, however you can also replace the minus sigh with brackets to indicate negative values. This format is more common in financial accounting, and there is actually a specific pre-built format for that just below currency, but outside of the scope for this article. In either case Excel gives you a quick option to highlight negative numbers as red. This option is also available for regular numbers as well.
Okay, so we covered some basic number formats. Pretty much all of it is accessible through the Format Cells menu. However often we just need to make a simple change, especially if it is a repetitive task it might be slow to do. Excel offers a quick number formatting window in the Home ribbon as we’ve seen before for decimals and percentages:
We are familiar with the % button as well as the two buttons on the right to increase and decrease decimal places. The two buttons to the left and right of the % button are currency and comma style. Unlike the currency and comma separator explored in this article these are both accounting formats. So while useful for specifically accounting tasks, they are not ideal for general use. Especially if you plan to put it into power point, I find this format introduces some empty characters that can mess up your slide format. So if you need to use them go ahead, but if you simply want to add $ in front of your number I suggest following the steps above or the drop down menu above the buttons
Where it says General is a drop down box that allows you to quickly select many formats as if through the Format Cells menu:
Here the currency and number formats behave like we are did above, as well as some other formats we have not discussed yet (but you may need)
For adding comma’s without accounting format you still have to go through the menu, but even that can be done quickly through the quick access buttons. The little arrow at the bottom left of the Number format section will let you quickly access the Format Cells window, instead of using right click:
This article covers some basics of formatting numbers, and should give you a good idea what formatting versus value means. Next time I will cover more format types and custom formatting, including using k for thousands and M for millions. If you have any questions or challenges feel free to post below and I will either cover in an article or just give you a quick tip how to do what you need.