Copyright

The Number Format in Excel: Options & Properties

The Number Format in Excel: Options & Properties
Coming up next: How to Highlight Cells in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 Formatting Numbers
  • 0:49 Format Cells Dialogue Box
  • 2:12 Number
  • 4:34 Currency
  • 5:06 Date
  • 7:16 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed
Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Since Excel is normally used to display numbers, the way they are displayed becomes a necessity in order to differentiate between just numbers, percentages and dollar amounts. This lesson will review the different number-formatting options in Excel.

Formatting Numbers

The ability to format numbers in Excel is a good way to improve the readability of worksheets and workbooks. Format means to apply a style to a cell that determines how the entry will be displayed.

Because Excel is normally used to display numbers and calculations, you need to format the numbers so they are logical, consistent and straightforward. For instance, formatting currency with leading dollar signs, adding trailing percentage signs to percentages and inserting commas to large numbers are all circumstances where number formatting is important.

This lesson will guide you through the steps to formatting numbers using Excel's built-in, number-formatting options.

Format Cells Dialogue Box

Let's start by reviewing the many formatting options for numbers. Go to the Home menu in the ribbon, look in the Cells grouping of commands and click on Format. On the bottom of the drop-down menu is the Format Cells command. Clicking on the option will open the Format Cells dialogue box. See the tabs across the top? This lesson will focus on the Number tab.

Look in the Category section. You have several options for formatting numbers:

  • General
  • Number
  • Currency
  • Accounting
  • Date
  • Time
  • Percentage
  • Fraction
  • Scientific
  • Text
  • Special
  • Custom

There are three categories (besides General, which is no formatting at all), that seem to be used more frequently than others. They are Number, Currency and Date. This lesson will focus on these three options.

Just a quick note: When formatting cells, you can apply the format to one cell or select an entire row or column and apply the formatting all at once.

Number

So there are three different elements to number formatting.

  1. Decimal Places: Do you want two zeros on the end of your number or none? For example, 23.00 or just a 23.
  2. Thousands Separator: Do you want a comma (,) for numbers 1,000 or more? (1,000 or 1000)
  3. Negative Numbers: Do you want the number with a leading minus sign (-)? Do you want it displayed in red? Or surrounded by parentheses?

Let's use an example to demonstrate the three elements. In the lesson video, we have a family budget spreadsheet (please see the video at 02:54). All numbers have been formatted, except for the first row of numbers, which is the monthly cash available. This number is our income minus our expenses. We want to add 2 decimal places, add the comma for numbers 1,000 or more and identify the negative numbers in red with parentheses.

Since we want to apply the formatting to all monthly cash balances, we can select all the applicable cells and apply the formatting at one time. Let's review each step to making our changes.

  1. Select the cell or multiple cells you want to format.
  2. Go to the Home menu in the ribbon.
  3. Look in the Cells grouping of commands.
  4. Click on Format and choose Format Cells from the drop-down menu.

Now that we have our Format Cells dialogue box open, we can make our selection for the number formatting.

  1. Select the Number category.
  2. Enter 2 for the decimal places.
  3. Add a check to the box for 'use 1,000 separator (,),' and you'll see a comma for the option.
  4. Under the Negative Numbers section, choose the last option, which is red text with parentheses.
  5. Click OK.

Excel will take care of the rest and format the selected cells to the number formatting options we selected. In addition, if you update or change the numbers in the formatted cells, Excel will retain the formatting.

Currency

Let's look at Currency. Formatting currency is similar to numbers. You can choose the decimal places, select which currency symbol you want to use and decide how you would like your negative numbers to appear. However, there is one difference. The thousands separator (,) is always used when using the currency format option. If you look at our family budget example, after formatting the monthly cash balances to currency, the numbers now have a leading dollar sign:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support