Contents
Every time you work in Excel, try to use the appropriate number formats for your data. Number formats tell the program what type of data you are using, be it percentages (%), currencies ($), time, date, and so on. Consider, for example, the following table, which uses various number formats:
What are number formats for?
Numeric formats are needed not only to make the data in the table easier to read, but also to make the table easier to use. By setting the number format, you tell the table what type of data is stored in the cell. For example, the date format says that you put a certain calendar date in a cell. All of this allows Excel to better understand your data and ensures that it stays relevant and formulas calculate correctly.
If you do not consider it necessary to set a specific number format, Excel uses the format by default General (General). The general number format also slightly alters the formatting of your data.
Setting number formats
You can apply a number format in the same way as other types of formatting, such as changing the color of text. To do this, select the cells and select the desired formatting options. Each spreadsheet application allows you to customize number formats, but the process can vary greatly from application to application.
- In Microsoft Excel 2007-2013, open the tab Home (Home), in the section Number (Number) click drop down menu Number Format (Number Formats) and select the desired format. Or, under the drop-down menu, you can click one of the shortcut icons.
- In Excel 2003 and earlier, open the menu Size (Format) > Cells. Cells. (cells).
- In Google Sheets, click the button More Formats (Other formats) on the left side of the toolbar – this button looks like numbers 123. To the left of this command there are icons Currency (money format) or Percent (Percentage Format) for quick access to these formats.
In most versions of Microsoft Excel, formatting options can be accessed by selecting the desired cells and clicking Ctrl + 1.
In this example, we will apply the number format Currency (Currency), which adds a currency symbol ($) and displays two decimal places for any numeric value.
If you select any cell with a number format, then in the formula bar you can see the actual number contained in it. This is the value that Excel uses when working with formulas and in other calculations.
Using number formats correctly
The theme of number formats does not end with selecting a cell and applying one of the formats to it. Spreadsheets can set many number formats automatically, depending on the data entered. This means that you must enter data in a way that the application understands and make sure that the cells are in the correct number format. For example, the picture below shows how to enter dates, percentages and times correctly.
Now that you know a lot about how number formats work, let’s see some of them in action.
Percent Format
One of the most useful formats is the percentage format (%). It displays values as percentages, for example, 20% or 55%, which is especially useful when calculating discounts or tips. If you type a percent sign (%) after a number, the percentage format is automatically applied to it.
Perhaps you still remember from a school mathematics course that percentages can be written as decimal fractions, i.e. 15% is the same as 0,15, 7,5% – is 0,075.
The percentage format will be useful more than once. In the images below you see an example of what is obtained in each table with different formatting of the discount size (5, 5% or 0,05):
As you can see, the calculations in the table on the left are not correct. Without using the percentage format, Excel thinks we want to multiply $ 22,50 on 5, not on 5%. The table on the right, although it copes with this task, is the easiest to read the middle version.
Date format
When working with dates, you will need a date format to tell Excel that you are referring to a specific calendar date, for example, July 15 2014. In addition, the date format provides a powerful toolkit of functions in the Date and Time category that use date and time information to calculate results.
Spreadsheets understand information differently than humans do. For example, if you enter the word “October” into a cell, Excel will not understand that you mean a date and will assume it is plain text. Instead, when entering dates, use a specific format that Excel understands, such as month / day / year or day month Year – depending on which country you are in and what localization of the program you use. In the example below, we will enter “10/12/2014” to get the date October 12, 2014. In this case, Excel will automatically set the date format for the cell.
Now that we have the correct number format for our dates, we can do a lot with them. For example, we can use the autocomplete token to continue the sequence of dates in a column and get a new date in each cell.
If the date format was not automatically applied, then Excel did not recognize the date you entered. In the following example, we entered “March 15th”. Excel did not recognize the date in this entry, so the cell format remained General (General).
On the other hand, if we enter “March 15” (without th), Excel will recognize the date. Since the year is not specified in this date, the current year will be automatically added and the date will contain all the necessary information. We can also write the date in several other ways, for example, 3/15, 3/15/2014 or March 15 2014, and the table will still understand that it is a date.
In localization 15/3, 15/3/2014 or 15 March 2014.
Other date format options
Some applications have a much larger selection of date formats. Accessing them is slightly different, depending on the application you are using. In Excel 2007-2013, open the drop down menu Number Format (Number formats) and select the item More Number Formats (Other number formats).
A dialog box will open Cell format, where you can select the desired date format.
By looking in the formula bar, you can verify that the custom date format does not change the value stored in the cell. Only the way the date is displayed changes.
Tricks when formatting numbers
Here are a few tricks to help you better deal with number formats:
- Apply number format to entire column: If you are going to use a column for a specific type of data, such as dates or percentages, the easiest way is to select the entire column at once by clicking on its letter and apply the desired number format. Now, any data entered in this column will already have the correct number format in advance. Note that the header row (if any) should generally not be affected by numeric formatting.
- Double check the values in the cells after applying the number format to them: If you apply a number format to existing data, the result may be unexpected. For example, applying a percentage format (%) to a cell with the value 5, You’ll get 500%And not 5%. In this case, you will have to re-enter the correct values in each cell.
- If you refer to a cell with a number format in a formula, Excel can automatically apply the same number formatting to the resulting cell. For example, if you use a value with currency format in a formula, the result of the calculation will also be in currency format.
- If you want the data in a cell to be displayed exactly as it was entered, you must use the text format. This format is especially good for numbers that you don’t want to do any calculations on, such as phone numbers, postal codes, or numbers that start with zero (02415). Apply text format to such cells before entering data.