# Excel Intro: Number Formats

Did you want Working with Numbers: 2007,2010,2013,2016  or español

Numbers can be written in different ways and still mean the same amount. For example, 1 can also be written as 1.00, 1.000, or 100%, depending on exactly what you are doing and how you want to look at your numbers.

[For the perfectionists out there: Yes, I know that there are differences in the exact meaning of the numbers above, but for most purposes in spreadsheets we can treat them as the same.]

Excel includes buttons for some of the most common ways to format numbers. Later you will use a dialog that has even more choices.

Currency: formats the number as money, like \$1.00 if your system is set to use dollars. Excel comes in different languages, and will use the appropriate local currency. The button may change to the general currency symbol! You can also change the currency used with the Format dialog. You are not stuck with the default! The dialog is good to use when you want a different currency only some of the time.

Percent: formats the number as a percentage, like 100%.

Comma: formats the number to show 2 decimal places to the right of the decimal and uses commas to separate every 3 digits to the left of the decimal. Non-English versions of Excel may show a different button, . (Why doesn't the whole world do things the way I do?!)

Increase/Decrease Decimals: changes the number of places showing to the right of the decimal. The number is rounded when decreasing decimals. When increasing decimals, the original digits are shown, rounded to the number of decimal places you have chosen.

Apparent errors in calculated values: Calculations use the underlying numbers in the cells, not the numbers that you see. This can make a difference in the results of the calculation.

Example: Cell B2 contains the number 512.46 and cell C2 contains 66.6666. Cell D2 shows the sum of B2 and C2, which is 579.1266.

Format the cells to show only one decimal point. Then the values are rounded so that B2 displays 512.5 and C2 shows 66.7. What will you see in D2? The numbers showing in the cells add up to 579.2, but 579.1 is what shows. Confusing? Excel does the calculation for D2 with the original numbers, not the formatted numbers, and then does the formatting for D2.

[Note to teachers: You may need to review percentages and rounding procedures with your students to be sure they understand those ideas.]

Where you are:
JegsWorks > Lessons > Numbers

Project 3: Format & Arrange

Project 4: Groups & Formulas

Project 5: Design

Search
Glossary
Appendix

## Step-by-Step: Number Formats

 What you will learn: to change number formats what each number format button does

### Number Formats: Currency

1. Select cells B7:B13.

2. Click the Currency button to apply the currency number format. Now your numbers look like dollars, if that is the default currency on your computer. In another project you will learn how to change the currency being used.
3. What changed:

• Added \$ to the far left of the cell
• Added a decimal at the right of the whole numbers
• Shows exactly 2 digits to the right of the decimal
(Since you are formatting whole numbers, this format adds 2 zeros.)
• Commas divide groups of three digits to the left of the decimal.
• The column widens automatically to show all the digits.

### Number Formats: Percent

1. While B7:B13 is still selected, click the Percent button.
2. All the numbers are changed to percentages. Since these were large numbers, they look even larger as percentages.

What changed:

• Removed \$ and commas
• Added % at the right
• Moves the two digits that were at the right of the decimal.

### Number Formats: Comma

1. While B7:B13 is still selected, click the Comma button.
2. What changed:

• Removed % and moves those two digits back to the right of the decimal
• Added commas to separate groups of 3 digits to the left of decimal
• Shows 2 digits to the right of the decimal

### Number Formats: Change Decimals

1. While B7:B13 is still selected, click the Increase Decimals button.
2. What changed:

• Gained another digit to the right of the decimal.
• The column automatically widened to show the new digits.

3. Click the Decrease Decimals button twice.
4. What changed:

• Only one zero is left to the right of the decimal.
• (The column width did not reduce its width.)

5. Close the workbook with | or by clicking the Close button on the workbook window. Do not save changes!