Sign in or start a free trial to avail of this feature.
10. Formatting Numbers
Formatting numbers can be frustrating unless you understand Excel's different number formats and how to apply them to your data.
To explore more Kubicle data literacy subjects, please refer to our full library.
Number Categories in Excel (00:18)
Every cell has an associated number category, which can be seen in the ribbon. The category determines the format of the text in the cell.
Most cells have the type General, however dates and percentages will be automatically recognized and given these categories by Excel. You can view the available categories, and change a cell’s category, using the format cells dialog box. This can be opened using the shortcut Ctrl + 1.
General and Number (01:26)
General is the default type, used for text or simple numbers.
Number is often used for numbers with decimal places. You can customize the number of decimal places, the thousand separator, and the format of negative numbers.
Currency and Accounting (02:20)
These types are used when dealing with money. Currency adds a currency symbol, which can be customized. Accounting is similar, but aligns the currency symbol to the left of the cell.
More Category Types (03:24)
Date provides various different formats suitable for dates, as does Time. Percentage is a commonly used type that’s easy to understand. Fraction and scientific are rarely used in business contexts.
Text can be useful when you want to format numbers as text, for example, if your number has trailing zeros, which are removed by the Number type.
Special and Custom (04:48)
Special provides options that are dependent on your locale or region. Custom formats can be defined by the user, and contains various preformatted category types.
Formatting numbers can be quite challenging and frustrating in Excel, unless you fully understand how the software deals with number formatting. In this lesson, we're going to review all of the number formats available in Excel before applying some new formats to our data set. Every cell on your workbook has a number of categories associated with it, which can be identified in the number part of the ribbon. This number category determines the format of texts within this cell. By default, most cells are set to general and if you scroll around the page, the number of category doesn't change. However, when we get to the date column, the number category does change to date. This is because from the text entered 21 forward slash zero three forward slash 2012. Excel can identify this as a date and change its number category accordingly. Excel also performs this for percentages. Let's say, for example, I enter 20% in a cell, then leave the cell and then return to it. The category type is now percentage. To view all the formatting options available in Excel, let's use a shortcut Control + One, and this brings up the format cells dialogue box. In the number tab, you press Alt + C so that we can access the category drop down. Now we can scroll through the categories, using the arrows and change the category type, if we'd like to. Let's review the category tapes starting at the top with General.
General is the default cell type. And it's typically used for text or straightforward numbers. If we changed our cell type to General in this case, 20% would be converted to 0.2.
The next category type is number and it's often used for numbers that need decimal places. Number offers us a number of ways to customize the format. For example, adding 1000 separators with a comma or increasing the number of decimal places. To get into the decimal places dialog box, we can see that the D in decimal is underlined, which means if we press Alt + D we can now change the number of decimal places. I'm going to change it to three. And now our 20% will become 0.200.
Note that decimal places are indicated with a point and thousand separators are indicated by a comma as is the convention in most English speaking countries.
In some parts of the world, this convention is reversed. and the comma is used as the decimal separator, while the point is used 4,000 separators.
If you're in a part of the world where this is the case, the numbers in your spreadsheet may look different than the numbers you see in our videos, but they will still work in the same way in calculations. Let's now move on to currency and accounting.
I'll return to this category, drop down by pressing Alt + C and then press the down arrow to go to currency. Currency adds a symbol and specifies decimal places to a particular number. Let's change the decimal places to two, Alt + D and then press the down arrow. And we can also change the symbol Because my company is American, I want to change it to dollar. So Alt + S to get into the symbol dropdown, and we'll use the arrows to find US dollar. There we go.
Quick return, and we now have the correct currency symbol. Click OK, to see how this looks on our sheet. Let's now return to the format cells dialogue box with Control + One, and we'll move down to accounting. Accounting is very similar to currency. The basic difference is in how it aligns its symbol. To show you I'm going to press okay. And we can see how the currency symbol is aligned on the left, and the number is aligned on the right.
Let's return to our format cells dialogue box again, and look at date and time.
As you can see, date offers a number of different formats that we can use, but since our input is currently 20%, the sample doesn't make any sense. The same is the case for time. Percentages, you can imagine is quite common, particularly in financial modeling, where metrics such as profit margin are very important. Fraction and scientific are rarely used for business purposes, so we'll skip through these.
Text can be useful if you want to treat numbers as text. This aligns numbers on the left hand side of the cell, and it also allows you to include trailing zeros. Let's escape from the dialog box by pressing escape. Let's move to a new cell and let's type zero zero seven zero. And if we press Tab, we can see that Excel automatically chops off the trailing zeroes and interprets the entry at 70.
This time, let's change the format of the cell to text. So Control + One, Alt + C to enter category, and we can press T twice to get to text. Press OK. Now let's re type zero zero seven zero.
And when interpreted in text, the trailing zeroes remain, and the number is aligned on the left hand side. Let's return to our initial cell and wrap up our look at number types with special and custom.
Special actually depends primarily on the locale or region you're in. I currently have my locale set to Ireland, and there's no special formats available. Let's change the locale by pressing Alt + L and then going to the US and we can see that a number of different types are available, including zip code, phone number, and social security number. Typically, we won't use this special option too often. And finally, we have custom. Custom enables us to create our own category types and contains a list of very useful pre formatted category types that we will use in the next lesson to format some of our data.