Excel 2019 (Part 7): Understanding Number Formats

What is number formatting?

Whenever you work with spreadsheets in Microsoft Excel , you should use the appropriate number formats for your data. The number formats tell the spreadsheet exactly what type of data you're using, such as percentages (%), currency ($), time, dates, etc.

 

Why use number formats?

Number formatting not only makes spreadsheets easier to read but also easier to use. When you apply a number format, you're telling your spreadsheet exactly what type of value is stored in a cell. For example, date formatting tells the spreadsheet that you're entering specific calendar dates. This allows the spreadsheet to better understand your data, helping to ensure that your data is consistent and formulas are calculated correctly.

Unless you need to use a specific number format, spreadsheets will typically apply a general number format by default. However, the general format may apply some minor formatting changes to your data.

Apply number formats

Just like other formatting options, such as changing the font color, you apply number formatting by selecting a cell and clicking on the desired formatting option. There are two main ways to select number formatting:

1. Go to the Home tab , click the Number Format drop-down menu in the Number group , and select your desired format.

 

2. Click on one of the quick number formatting commands below the drop-down menu.

You can also select the desired cells and press Ctrl + 1 on your keyboard to access additional number formatting options.

The example has applied the Currency format , which adds currency symbols ( $ ) and displays two decimal places for any numeric value.

If you select any cell that is formatted as a number, you can see the actual value of that cell in the formula bar. The spreadsheet will use this value for formulas and other calculations.

Use number formats correctly.

It's not simply a matter of selecting a cell and applying formatting. Spreadsheets can actually apply number formatting automatically based on how you enter the data. This means you'll need to enter the data in a way the program can understand, and then ensure that the cells are using the appropriate number formatting. For example, the image below shows how to correctly use number formats for dates, percentages, and times:

 

Now that you know more about how number formats work, let's look at some specific number formats.

Percentage format

One of the most useful number formats is the percentage (%). It displays values ​​as percentages, such as 20% or 55%. This is especially helpful when calculating things like sales tax or tips. When you enter a percentage (%) after a number, the percentage format is automatically applied to that cell.

Percentages can also be written as decimal numbers. So, 15% is equivalent to 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, etc. Percentage formatting is useful in many situations. For example, in the images below, notice how the sales tax rate is formatted differently for each spreadsheet (5%, 5%, and 0.05):

As you can see, the calculation in the spreadsheet on the left doesn't work correctly. Without percentage formatting, the spreadsheet assumes you want to multiply $22.50 by 5, not 5%. The spreadsheet on the right works fine without percentage formatting, and the one in the middle is easier to read.

Date formatting

Whenever working with dates, you'll want to use date formatting to let the spreadsheet know that you're referring to specific calendar dates, such as July 15, 2014. Date formatting also allows you to work with a powerful set of tools that include functions that use date and time information to calculate answers.

 

Spreadsheets cannot understand information in the same way as humans. For example, if you enter October into a cell, the spreadsheet won't know you're entering a date, so it will treat this content like any other text. Instead, when entering a date, you need to use a specific format that the spreadsheet understands, such as month/day/year (or day/month/year depending on the country you are in). The example below would enter 10/12/2014, which is equivalent to October 12, 2014. The spreadsheet will then automatically apply the date format to the cell.

Now that you've formatted the dates correctly, you can do various things with this data. For example, you can use the Fill Handle to continue filling the subsequent dates in the column:

If the date format isn't applied automatically, it means the spreadsheet doesn't understand the data you've entered. The example below entered March 15th . The spreadsheet doesn't understand that the author is referring to a date, so this cell is still using the generic number format.

On the other hand, if you enter March 15 (without the "th"), the spreadsheet will recognize it as a date. Because it doesn't include the year, the spreadsheet will automatically add the current year so the date part will have all the necessary information. It's also possible to enter the date in several other ways, such as 15/3, 15/3/2014, or 15/3/2014, and the spreadsheet will still recognize it as a date.

If you want to add the current date to a cell, you can use Ctrl + ; as shown in the video below.

Other date formatting options

To access other date formatting options, select the Number Format drop-down menu and choose More Number Formats . These are options for displaying dates differently, such as including the day of the week or omitting the year.

 

The Format Cells dialog box will appear. From here, you can select your desired date formatting option.

As you can see in the formula bar, custom date formatting not only changes the actual date in the cell but also how it is displayed.

Number formatting tips

Here are some tips for getting the best results with number formatting:

Applying number formatting to the entire column : If you intend to use the column for a specific type of data, such as dates or percentages, the easiest way to select the entire column is to click on the column character and apply the desired number formatting. This way, any data you add to this column in the future will have the correct number formatting. Note that the header row is usually unaffected by number formatting.

Carefully check your values ​​after applying number formatting : Applying number formatting to existing data can lead to unexpected results. For example, applying percentage (%) formatting to a cell with a value of 5 might give you 500% instead of 5%. In this case, you need to re-enter the values ​​correctly in each cell.

If you reference a cell with number formatting in a formula, the spreadsheet may automatically apply the same number formatting to new cells. For example, if you use a value with currency formatting in a formula, the calculated value will also use currency formatting.

If you want your data to appear exactly as you entered it, you'll need to use text number formatting. This is especially good for numbers you don't want to perform calculations on, such as phone numbers, zip codes, or numbers that start with 0, like 02415. For best results, you may want to apply text number formatting before entering data into these cells.

Increase Decimal and Decrease Decimal commands

The Increase Decimal and Decrease Decimal commands allow you to control the number of decimal places displayed in a cell. These commands do not change the value of the cell; instead, they display the value to a set number of decimal places.

Decrease Decimal will display the value rounded to that decimal place, but the actual value in the cell will still be displayed in the formula bar.

The Increase Decimal and Decrease Decimal commands do not work with some number formats, such as Date and Fraction .