What space number formats?

Whenever you're working with a spreadsheet, it's a great idea to use suitable number formats for your data. Number formats tell your spreadsheet precisely what kind of data you're using, favor percentages (%), currency ($), times, dates, and also so on.

You are watching: It is acceptable for dates to have a currency format

Watch the video clip below come learn more about number styles in Excel.

Why use number formats?

Number styles don't just make her spreadsheet easier to read—they additionally make it less complicated to use. When you apply a number format, you're informing your spreadsheet exactly what species of values space stored in a cell. For example, the date format tells the spreadsheet the you're entering specific calendar dates. This enables the spreadsheet to better understand your data, i m sorry can aid ensure that your data remains consistent and that your formulas space calculated correctly.

If friend don't must use a particular number format, the spreadsheet will usually use the general number format by default. However, the general layout may apply some little formatting transforms to your data.

Applying number formats

Just prefer other species of formatting, such as changing the font color, you'll apply number layouts by choosing cells and also choosing the desired formatting option. There room two key ways to choose a number format:

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


You can also select the preferred cells and also press Ctrl+1 ~ above your keyboard to access more number-formatting options.

In this example, we've used the Currency number format, which adds currency symbols ($) and also displays 2 decimal areas for any kind of numerical values.


If girlfriend select any cells through number formatting, you deserve to see the actual value the the cell in the formula bar. The spreadsheet will use this value for formulas and also other calculations.


Using number formats correctly

There's an ext to number formatting than selecting cells and also applying a format. Spreadsheets have the right to actually use a most number formatting automatically based upon the way you enter data. This method you'll need to enter data in a way the program can understand, and then ensure that those cells space using the appropriate number format. For example, the image listed below shows just how to use number layouts correctly for dates, percentages, and times:


Now that you know an ext about exactly how number layouts work, we'll look at a few different number styles in action.

Percentage formats

One the the most useful number layouts is the percentage (%) format. It screens values as percentages, such together 20% or 55%. This is especially helpful when calculating things favor the price of sales tax or a tip. As soon as you form a percent sign (%) after a number, the percent number format will it is in be applied to that cell automatically.

As you might remember from math class, a portion can additionally be composed as a decimal. Therefore 15% is the exact same thing together 0.15, 7.5% is 0.075, 20% is 0.20, 55% is 0.55, and also so on. You have the right to review this lesson from our mathematics tutorials come learn more about converting percentages come decimals.

There are plenty of times when percentage formatting will be useful. For example, in the images below, notification how the sales taxes rate is formatted in different way for each spreadsheet (5, 5%, and also 0.05):

As you deserve to see, the calculate in the spreadsheet top top the left didn't job-related correctly. There is no the portion number format, ours spreadsheet thinks we desire to main point $22.50 through 5, not 5%. And while the spreadsheet on the best still works without portion formatting, the spreadsheet in the middle is simpler to read.

Date formats

Whenever you're working v dates, you'll want to usage a date format come tell the spreadsheet the you're referring to specific calendar dates, such as July 15, 2014. Day formats also permit you to work with a an effective set of day functions that usage time and date details to calculate an answer.

Now the we have our date correctly formatted, we deserve to do plenty of different things with this data. Because that example, we might use the fill take care of to continue the days through the column, so a different day appears in each cell:

If the day formatting isn't applied automatically, it method the spreadsheet go not understand the data you entered. In the example below, we've typed March 15th. The spreadsheet go not understand that we were introduce to a date, for this reason this cabinet is still making use of the general number format.

Try start the dates below into a spreadsheet and see if the day format is used automatically:

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

Other date formatting options

To access other day formatting options, select the Number Format drop-down menu and also choose More Number Formats. These are options to display the day differently, like consisting of the job of the mainly or omitting the year.

The Format Cells dialog box will certainly appear. From here, friend can pick the desired day formatting option.

As you can see in the formula bar, a custom day format doesn't change the actual date in our cell—it just alters the method it's displayed.

Number formatting tips

Here room a couple of tips for obtaining the ideal results through number formatting:

Apply number formatting to an entire column: If you're plan to use one shaft for a certain form of data, like dates or percentages, you may discover it simplest to choose the whole column by clicking the shaft letter and applying the desired number formatting. This way, any kind of data you include to this column later will currently have the exactly number format. Note that the header row commonly won't be affected by number formatting.
Double-check your worths after using number formatting: If you use number formatting come existing data, friend may have actually unexpected results. For example, using percentage (%) formatting come a cell through a value of 5 will provide you 500%, not 5%. In this case, you'd need to retype the values properly in each cell.
If you reference a cell through number formatting in a formula, the spreadsheet may automatically apply the very same number formatting to the new cell. Because that example, if you use a worth with currency formatting in a formula, the calculated value will also use the currency number format.
If you want your data to appear exactly together entered, you'll have to use the text number format. This style is especially an excellent for numbers you don't want to perform calculations with, such as phone numbers, zip codes, or numbers that begin with 0, choose 02415. For best results, you might want to use the text number format before entering data into these cells.Increase and Decrease Decimal

The Increase Decimal and also Decrease Decimal commands permit you to control how countless decimal locations are displayed in a cell. These commands don't adjust the worth of the cell; instead, they screen the value to a set number of decimal places.

Decreasing the decimal will display screen the worth rounded to that decimal place, however the actual value in the cell will certainly still be displayed in the formula bar.

See more: Is It Wrong For A Senior To Date A Freshman, Access Denied

The Increase/Decrease Decimal commands don't work-related with some number formats, prefer Date and also Fraction.


In cell D2, kind today's date and press Enter.Click cabinet D2 and also verify that it is utilizing a Date number format. Try changing it come a different day format (for example, Long Date).In cell D2, usage the Format Cells dialog box to pick the 14-Mar-12 day format.Change the sales tax price in cell D8 to the Percentage format.Apply the Currency style to all of column B.In cell D8, usage the Increase Decimal or Decrease Decimal command to readjust the number of decimal locations to one. It must now display 7.5%.When you're finished, her spreadsheet have to look like this: