Add text and data

Format numbers in cells

Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

Excel offers a variety of number formats to display numbers as percentages, dates, currency, and more.

Number format
  1. Select a cell or a cell range.

  2. On the Home tab, select Number from the drop-down.
    General
    Or, you can choose one of these options:

    • Press Ctrl + 1 and select Number.

    • Right-click the cell or cell range, select Format Cells… , and select Number.

    • Select the dialog box launcher Alignment Settings next to Number Dialog box launcher and then select Number.

Currency
  1. Select a cell or a cell range that has numeric value.

  2. Select Home > Currency .

Decimal
  1. Select a cell or a cell range that has numeric value.

  2. Select Home > Increase Decimal Increase Decimal or Home > Decrease Decimal Decrease Decimal .

Date
  1. Select a cell or a cell range.

  2. On the Home tab, select Number Format Alignment Settings .

  3. Select Date, and then select the Type.

  4. Select OK.

Phone number or social security number
  1. Right-click a cell or a cell range, and then select Format Cells…

  2. Select Special.

  3. For Type, select either Phone Number or Social Security Number.

  4. Select OK.

Want more?

Discover more courses like this at LinkedIn Learning

Format numbers as currency in Excel 2016

Certainly one of the most common tools used for formatting is this $ button, it appears in the Number group on the Home tab.

Notice as you slide over this, it says Accounting Number Format. Click it, and we get this look.

Some people are a little surprised because it looks like there's a space on the right hand side.

And something else you might notice if the column were wider, no reason to really make it wider now, but if you do, the $ hangs on to the left hand side.

And nothing wrong with this. By the way, this is also affected by whether you might have right alignment here, or center alignment.

It's not a good idea to center numbers and if you try it here, all that's centering really is the title.

But as you click this from time to time, sometimes you'll see a slight little wiggle there in those $s.

Nothing to worry about, and you can see it that way too.

Most of the time, cells like this, we probably have not aligned them anyway, except for possibly the title. Keep it that way.

Nevertheless, that's $. Now, what if you don't want the $s, but in other respects, you want to keep the format like it is, regardless of how wide the column is.

So maybe the column is only about that wide possibly, but you say I don't want the $s.

Clicking the $ again simply reaffirms the choice, so it doesn't take it off. It's not a toggle button the way bold, italic, and underline are.

Certainly not the opposite of this, but something you might want to use if you don't want the $s, and yet retain other aspects of the format is the comma button right here.

And that's really just a variation on accounting.

It's not really a separate format. It's accounting format without the $.

Also, in either case, whether you use $ or comma, you might, as in the case here, not want to show the decimals.

And notice that I'm saying it that way, we're not changing the value in any sense.

And even if there were pennies out there, we might not want to show them.

So this does no actual changing of the values, it simply displays the numbers differently.

And many times, if you're working with financial information, you don't want to see the pennies.

If this were scientific data, you might be going in the opposite direction, showing more decimals.

Now, there's another format in the background too, and I say in the background because we're not seeing it here on the screen.

But if you were to go to FormatCells, you can get there by way of Ctrl+1, or you can even get there by this little button, just to the right of Number.

This is called the Dialog Box Launcher.

If you click this button, it will take you to the Number tab. And here's another choice called Currency.

Now Currency gives us some options for negatives.

If we simply go with the default here, it too will put in a $ and two decimal places, but it does it a little differently.

And if I make the column wider, you'll see the difference.

Already you probably recognized a difference. There appears to be no space to the right of the zero. So making this wider, we see what's happening.

Now, this is not to confuse you and to say that every time you're using this kind of a format, you're going to debate about do I show use Accounting format with or without the $s, or should I use Currency format.

It's best to more or less stick with one of these. In this worksheet, over in column A, we've got some data entries.

Nothing wrong with that. If you'd like to see a four-digit year, one quick way is on the Home tab, go to the Number group, click the drop arrow here, above.

How about Short Date? We get that display, or you saw the other one perhaps, Long Date.

Column is going to have to be a lot wider for that one.

There are all kinds of variations on what you might use here for dates as well.

If you jump into FormatCells, and you can do that with Ctrl+1, or possibly right click and simply choose Format Cells.

Go to the Number tab, if it's not automatically selected.

And we can use M's and D's and Y's in a variety of ways, but don't overlook the fact that there are many, many formats in here already.

If all the data here in this worksheet is about the current year, maybe you want a format here that doesn't even show the year.

But shows, for example, we've got March 12 here.

So we do see some examples in here where we're not seeing the year.

In other cases, maybe we're not even seeing the day, so we can do month and day, lots of combinations as we look through here.

If you wanted to explore other time layouts, once again, we can go to Format Cells, Ctrl+1, or right click Format Cells.

Go to the Time category on the Number tab and explore some of those options as well. Lots of choices there.

Now, if you were putting in Social Security numbers or phone numbers, you probably wouldn't type them like this, although there's a good thought behind what's being done here, why type those dashes in Social Security numbers?

Why type a phone number with left and right parenthesis and a space and a dash and all that if there's a way to format these.

So ideally in situations like this, you're going to be formatting a column like this, either ahead of time, or shortly after putting in maybe one or two of these, because there's shortcuts for both situations.

I'm going to right click column D this time, and go right into Format Cells.

And on the Number tab, category called Special, Social Security number, click Okay.

And we see the dashes. Now the dashes are not truly there, as I click on a cell, look in the formula bar, you don't see them at all.

If I were double clicking in here to do some editing, we don't see the dashes.

If I made a change there that should have been a five, I'll press Enter, we see the dashes, but we don't see them all actually editing.

Same basic idea with the phone numbers.

We can do these two columns together.

Same location, more or less. Ctrl+1 takes us to FormatCells, Special category, Phone Number, Okay.

There it is.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at linkedin.com/learning.

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

Benefits
  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×