What are number formats?
Whenever you're working with a spreadsheet, it's a good idea to use appropriate number formats
for your data. Number formats tell your spreadsheet exactly what type
of data you're using, like percentages (%), currency ($), times, dates,
and so on.
Watch the video below to learn more about number formats in Excel.
Why use number formats?
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 types 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.
If you don't need to use a specific number format, the spreadsheet will usually apply the general number format by default. However, the general format may apply some small formatting changes to your data.
Applying number formats
Just
like other types of formatting, such as changing the font color, you'll
apply number formats by selecting cells and choosing the desired
formatting option. There are two main ways to choose a number format:
- 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.
You can also select the desired cells and press Ctrl+1 on your keyboard to access more number-formatting options.
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.
Using number formats correctly
There's more to number formatting than selecting cells and applying a format. Spreadsheets can actually apply a lot of number formatting automatically based on the way you enter data. This means you'll need to enter data in a way the program can understand, and then ensure that those cells are using the proper number format. For example, the image below shows how to use number formats correctly for dates, percentages, and times:
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 be applied to that cell automatically.
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):
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.
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.
Try entering the dates below into a spreadsheet and see if the date format is applied automatically:
- 10/12
- October
- October 12
- October 2016
- 10/12/2016
- October 12, 2016
- 2016
- October 12th
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 to display the date differently, like including the day of the week or omitting the year.
