Excel Intro:
Number Formats

Title: Jan's Illustrated Computer Literacy 101
Did you want Working with Numbers: 2007,2010,2013,2016  or españolIcon: Change web

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.

Button: Currency 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 Button: Currency - general icon 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.

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

Button: Comma 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, Button: Decimal in Excel 2003 (español). (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. 

WarningApparent 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

Before you start...

Project 1: Excel Intro
    InterfaceTo subtopics
    Select & Navigate To subtopics
    Common Tasks To subtopics
     footprintAutoSum
     footprintSort
     footprintChart Wizard
     footprint Number Formats
    Summary
    Quiz
    ExercisesTo subtopics

Project 2: Excel BasicsTo subtopics

Project 3: Format & ArrangeTo subtopics

Project 4: Groups & FormulasTo subtopics

Project 5: DesignTo subtopics

Search
Glossary
Appendix
Icon Step-by-Step 

Step-by-Step: Number Formats

  Icon Step-by-Step

What you will learn:

to change number formats
what each number format button does

Start with: (Excel open to budget.xls from resource files)

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. Cells B7:B13 in Percentage formatWhile 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. Cells B7:B13 in Comma formatWhile 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. Cells B7:B13 after Increase DecimalsWhile 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.
     
  2. Cells B7:b13 after Decrease decimalsClick the Decrease Decimals button twice. 

    3. What changed:

    • Only one zero is left to the right of the decimal. 
    • (The column width did not reduce its width.)
       
  3. Close the workbook with  File  |  Close  or by clicking the Close button on the workbook window. Do not save changes!