9. Formatting Numbers
Formatting numbers can be frustrating unless you understand Excel's different number formats and how to apply them to your data.
What are number formats
- Each cell on your worksheet has an associated number category
- The number category determines how a cell is automatically formatted
- Category types include Percentage, Text, Currency, Date and Time
Useful keyboard shortcuts
CTRL + 1: Open Format cells dialog box
ALT + Underlined letter: Enter related field (e.g. Alt + S => Symbol)
ALT + E, S, T: Pastes the format of our copied cell
Quick access shortcuts for number categories
CTRL + SHIFT + %: Change to % format
CTRL + SHIFT + 1: Change to number format
CTRL + SHIFT + $: Change to currency format
ALT + H, 0: Add a decimal place
ALT + H, 9: Remove a decimal place
ALT + H, K Add thousand separators
Text and Custom categories
- The Text category type switches a number to text form
- This protects the number from mathematical formulas and allows trailing zeroes
- Custom: allows us to create our own category type
- Custom: also has some additional date and time formats worth checking out
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 of formats available in Excel before applying some new formats to our data set. Every cell in your workbook has a number category associated with it. which can be identified in the number part of the ribbon.
This number category determines the format of the text within the cell. By default, most cells are set to general, and if you scroll around the page, the number 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/03/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, and then leave the cell and then return to it, the category type is now percentage.
To view all of the formatting options available in Excel, let's use the shortcut: Ctrl + 1. And this brings up the format cells dialogue box. In the number tab, we 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 types, starting at the top with general. General is the default cell type, and is typically used for text or straight forward numbers. If we change our cell type to general in this case, 20% will be converted to 0.2.
The next category type is number and is 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 dialogue 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. As you can see, number is much more customizable than general.
Let's now move onto currency and accounting. I'll return to the 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 2, 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 drop down and we'll use the arrows to find US dollar. There we go, click 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 Ctrl + 1 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 OK, 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 a cell, and it also allows you to include trailing zeros.
I'll show this with an example. Let's escape from the dialogue box by pressing Escape, let's move to a new cell, and let's type 0070. And if we press Tab, we can see that Excel automatically chops off the trailing zeros and interprets the entry as 70. This time, let's change the format of the cell to text. So Ctrl + 1, Alt + C to enter category, we can press T twice to get to text. Press OK. Now let's retype 0070, When interpreted in text, the trailing zeros 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 that 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 the new 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 preformatted category types, that we will use in the next lesson to format some of our data.