9. Formatting Numbers

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Formatting numbers can be frustrating unless you understand Excel's different number formats and how to apply them to your data.

Summary

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. Text (03:56)

    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.

  6. 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.

Transcript

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 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, 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, you press Alt + C so that we can access the category dropdown. 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 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 is often used for numbers that need decimal places. Number offers us a number of ways to customize the format. For example, adding 1,000 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 on to currency and accounting.

I'll return to the category dropdown 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's 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.

Click return, and we now have the correct currency symbol.

Click okay 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 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 a cell, and it also allows you to include trailing zeroes. I'll show this with an example.

Let's escape from the dialogue box by pressing Esc.

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 zeroes, 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, and we can press T twice to get to text. Press okay, now let's retype 0070.

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 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 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.