Body
Number formats don't just make your spreadsheet easier to read—they also make it easier to use. When you apply a number format, you're telling your spreadsheet exactly what kinds of values are stored in a cell. For example, the date format tells the spreadsheet that you're entering specific calendar dates. This allows the spreadsheet to better understand your data, which can help ensure that your data remains consistent and that your formulas are calculated correctly.
Applying number formats
Just like other types of formatting, such as changing the font color, you'll apply number formats by selecting cells and then choosing the desired formatting option. Every spreadsheet program allows you to add number formatting, but the process will vary depending on which application you're using.
For Microsoft Excel 365, go to the Home tab, click the Number Format drop-down menu in the Number group, and select the desired format. You can also click one of the quick number-formatting commands below the drop-down menu.
In this example, we've applied the Currency number format, which adds currency symbols ($) and displays two decimal places for any numerical values.
If you select any cells with number formatting, you can see the actual value of the cell in the formula bar. The spreadsheet will use this value for formulas and other calculations.
Percentage formats
One of the most helpful number formats is the percentage (%) format. It displays values as percentages, such as 20% or 55%. This is especially helpful when calculating things like the cost of sales tax or a tip. When you type a percent sign (%) after a number, the percentage number format will be applied to that cell automatically.
As you may remember from math class, a percentage can also be written as a decimal. So 15% is the same thing as 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, and so on.
There are many times when percentage formatting will be useful. 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 didn't work correctly. Without the percentage number format, our spreadsheet thinks we want to multiply $22.50 by 5, not 5%. And while the spreadsheet on the right still works without percentage formatting, the spreadsheet in the middle is easier to read.
Date formats
Whenever you're working with dates, you'll want to use a date format to tell the spreadsheet that you're referring to specific calendar dates, such as July 15, 2014. Date formats also allow you to work with a powerful set of date functions that use time and date information to calculate an answer.
Spreadsheets don't understand information the same way a person would. For instance, if you type October into a cell, the spreadsheet won't know you're entering a date so it will treat it like any other text. Instead, when you enter a date, you'll need to use a specific format your spreadsheet understands, such as month/day/year (or day/month/year depending on which country you're in). In the example below, we'll type 10/12/2014 for October 12, 2014. Our spreadsheet will then automatically apply the date number format for the cell.
Now that we have our date correctly formatted, we can do lots of different things with this data. For example, we could use the fill handle to continue the dates through the column, so a different day appears in each cell:
If the date formatting isn't applied automatically, it means the spreadsheet did not understand the data you entered. In the example below, we've typed March 15th. The spreadsheet did not understand that we were referring to a date, so this cell is still using the general number format.
On the other hand, if we type March 15 (without the "th"), the spreadsheet will recognize it as a date. Since it doesn't include a year, the spreadsheet will automatically add the current year so the date will have all of the necessary information. We could also type the date several other ways, such as 3/15, 3/15/2014, or March 15 2014, and the spreadsheet would still recognize it as a date.
For other date formatting options, select the Number Format drop-down menu and choose More Number Formats.
A dialog box will appear. From here, you can choose the desired date formatting option.
As you can see in the formula bar, a custom date format doesn't change the actual date in our cell—it just changes the way it's displayed.
Fraction format
Excel has a fraction number format that will allow us to enter our values as fractions, so we'll select column B and then use the Number Format command on the Home tab apply the fraction number format.
Now that we've applied number formatting, we're ready to enter the fractions in column B.
Notice that it can also display mixed fractions, such as 2 3/4 (two and three-fourths). If you select any these cells, you'll see that Excel is actually treating the value like a decimal number in the formula bar—the fraction number format just changes the way the value is displayed in the spreadsheet. For example, 2 3/4 is the same as 2.75.
You can even use fractions in formulas and functions. For example, let's say this recipe yields about two dozen cookies. If you wanted to make four dozen cookies, you could use Excel to double the recipe. If we wanted to double the amount of salt in our recipe, we would multiply the value in cell B2 by 2; the formula for this would be =B2*2. We can then use the fill handle to add the formula to the other cells in column C.
Here are the new fractions for our doubled recipe! As you can see, this number format makes it easy to work with fractions in Excel, especially if you don't want to convert your fractions into decimals.
Formatting Tips!
Here are a few tips for getting the best results with number formatting:
- Apply number formatting to an entire column: If you're planning to use one column for a certain type of data, like dates or percentages, you may find it easiest to select the entire column by clicking the column letter and then applying the desired number formatting. This way, any data you add to this column in the future will already have the correct number format. Note that the header row usually won't be affected by number formatting.
- Double-check your values after applying number formatting: If you apply number formatting to existing data, you may have unexpected results. For example, applying percentage (%) formatting to a cell with a value of 5 will give you 500%, not 5%. In this case, you'd need to retype 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 the new cell. For example, if you use a value with currency formatting in a formula, the calculated value will also use the currency number format.
- If you want your data to appear exactly as entered, you'll need to use the text number format. This format is especially good for numbers you don't want to perform calculations with, such as phone numbers, zip codes, or numbers that begin with 0: for example, "02415". For best results, you may want to apply the text number format before entering data in those cells.