Guide to full Excel 2016 (Part 8): Learn about Number Formats

Number format in Excel 2016

1. What is I. Number Formats?
2. II. Why should I use the digital format?
   1. Apply number format
3. III. Use the correct number format
   1. 1. Percentage formats (Percentage formats)
   2. 2. Date formats (Date formats)
   3. 3. Other date format options
4. IV. Tips on number format
   1. Increase and decrease the number of digits after the comma

What is I. Number Formats?

Whenever working with spreadsheets, it is better to use number formats that match your data. Number formats indicate the exact type of data you are using in the spreadsheet, such as percentage (%), currency unit ($), number of times, date, etc.

See the video below to learn more about Number Formats in Excel:

II. Why should I use the digital format?

The digital format not only makes your spreadsheet easier to read but also makes it easier to use. When applying a numeric format, you are telling the worksheet exactly the type of value stored in a cell. For example, the date format (date format) tells the spreadsheet that you are entering a specific calendar date. This allows the spreadsheet to better understand the data, helping to ensure data integrity and your formula is calculated correctly.

1. If you do not 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 format changes to your data.

Apply number format

Just like other types of formats, such as changing the font color, you will apply numeric formats by selecting the cell and selecting the desired format option. There are two main ways to choose a digital format:

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

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 1

1. You can click on one of the quick number format commands under the drop-down menu.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 2

1. In addition, you can also select the desired cells and press Ctrl + 1 on the keyboard to access other number format options.

In this example, we applied the Currency number format to add a currency symbol ($) and displayed two decimal places for any numeric value.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 3

1. If you select any cell with a numeric format, you can see the actual value of the cell in the formula bar. The spreadsheet will use this value for other formulas and calculations.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 4

III. Use the correct number format

There are many ways to format a number and select a cell and apply a format. Spreadsheets can actually apply multiple number formats automatically based on how you enter data. This means that you will need to enter the data in a way that the program can understand and then make sure that the cells are using the appropriate number format. For example, the image below shows how to use numeric formats for dates ( percentages ), percentages (percentages) and times (times):

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 5

Now that you know more about how digital formats work, we will look at a number of different number formats.

1. Percentage formats (Percentage formats)

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

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 6

In mathematical theory, one percent can also be written as a decimal. So 15% is equivalent to 0.15; 7.5% is 0.075; 20% is 0.20; 55% is 0.55 and more.

There are many times when the percentage format will be useful. For example, in the images below, note how the sales tax rate is formatted differently for each spreadsheet (5, 5% and 0.05):

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 7

As you can see, the calculation in the worksheet on the left does not work correctly. Without the percentage format, the spreadsheet thinks we want to multiply $ 22,50 by 5, not 5%. And even though the spreadsheet on the right still works without a percentage format, the spreadsheet in the middle is easier to read.

2. Date formats (Date formats)

Whenever you work with dates, you'll want to use the date format to tell the spreadsheet you're referring to specific calendar dates, such as July 15, 2014. The date format also allows You work with a set of date functions (Date functions) that use time and date information to calculate answers.

Spreadsheets do not understand information in the same way as humans. For example, if you enter October in the box, the spreadsheet will not know if you are entering a date to process it like any other text. Instead, when entering a date, you'll need to use the specific format that your spreadsheet understands, such as month / day / year (or day / month / year depending on the country you're in). In the example below, we will enter 10/12/2014 on October 12, 2014. The worksheet will then automatically apply the date format to that cell.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 8

Now that we have the date formatted correctly, we can do many things different from this data. For example, we can use processing handles to continue the days through the column, so another day will appear in each cell:

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 9

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 10

If the date format is not applied automatically, it means that the spreadsheet does not understand the data you entered. In the example below, we entered on March 15. The spreadsheet doesn't understand we're talking about a day, so this box is still using the general number format.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 11

On the other hand, if we enter March 15 (March 15) (without "th"), the spreadsheet will identify it as the date. Because it does not include years, the spreadsheet will automatically add the current year, so the date will have all the necessary information. We may also enter dates in a number of other ways, such as 3/15; 3/15/2014 or July 15, 2014 and the spreadsheet will still identify it as that day.

Try entering the dates below into the spreadsheet and see if the date format is automatically applied:

1. December 10
2. October
3. October 12
4. October 2016
5. December 10, 2016
6. October 12, 2016
7. 2016
8. October 12

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

3. Other date format options

To access other date format options, select the Number Format drop-down menu and select More Number Formats . These are different date display options, like including day of the week or skip the year.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 12

The Format Cells dialog box will appear. From here, you can choose the desired date format option.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 13

As you can see in the formula bar, the custom date format does not change the actual date in the cell - it only changes the display.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 14

IV. Tips on number format

Here are some tips for getting the best results with the digital format:

1. Apply a number format to the entire column : If you plan to use each column for a certain data type, such as a date or a percentage, you can see the simplest way to select the entire column by clicking on the column letters and apply the number format you want. In this way, any data you add to that column in the future will have the correct digital format. Note that the title row will usually not be affected by the number format.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 15

1. Check the values ​​after applying the digital format : If you apply a digital format to existing data, you may have unexpected results. For example, applying the percentage (%) format to a cell with a value of 5 will give you 500%, not 5%. In this case, you need to re-enter the value correctly in each cell.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 16

1. If you reference a cell with a numeric format in a formula, the worksheet can automatically apply the same number format to the new cell. For example, if you use a value with the currency format in a formula, the calculated value will also use the currency number format.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 17

If you want the data to appear exactly as entered, you will have to use the text number format. This format is especially good for numbers you don't want to perform calculations, such as phone numbers, zip codes or numbers starting with 0, like 02415. For best results, you can apply format the text number before entering data into these cells.

Increase and decrease the number of digits after the comma

The Increase Decimal command and Decrease Decimal allow you to control the decimal position displayed in a cell. These commands do not change the value of the cell; instead, they display values ​​for a set of decimals.

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 18

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

Guide to full Excel 2016 (Part 8): Learn about Number Formats Picture 19

The Increase / Decrease Decimal command does not work with a number of digital formats, such as Date and Fraction.

Having fun!

David Pac

Update 25 May 2019