Monday, February 1, 2016

Basic Excel Number Formats

Excel is all about numbers, right? Well, sort of. Certainly it makes calculating things much easier, but I also love how nice and organized the grid is.

But that isn't the point of this blog post. Today I want to talk about number formatting.

Have you ever seen a table that looks like this?




Ugh! What a mess! First, it's hard to read. Second, you have no idea what you're looking at. Money? Counts? Percentages?

One of the most essential skills in Excel is knowing how to format your numbers for easier reading. Like anything in Excel, there are several ways to do this. I'll start with the simplest and most straight-forward.

In the Ribbon

In the Home tab of the Ribbon are several number formatting options ready to go. See the Number section of the Home tab.



One Click Formatting

There are a few formats that you can choose with one click. These are the buttons directly above where it says Number in the Ribbon.

Currency - Noted by a dollar sign

  • A dollar sign will be added to the front of the number (Note that you can select other world currencies as well)
  • Thousands will be separated by a comma
  • Decimals will be rounded to two decimal places
  • Negatives will have a dash in front

Percentage - Noted by a percent sign

  • The number will automatically be multiplied by 100 to denote a percent
  • A percent sign will be added to the end of the number
  • No decimal places will be displayed

Accounting - Noted by a comma

  • Thousands will be separated by a comma
  • Decimals will be rounded to two decimal places
  • Negatives will have a dash in front

Increase/Decrease Decimal

  • See those two buttons with all the zeros? If you look carefully, you'll see that one shows one zero turning into two, and the other has two zeros turning into 0. That means that one button will increase the number of decimals shown and the other will decrease the number of decimals shown.

Drop Down Formatting

Above the one click format buttons, there is a drop down with a few additional options.




General

No specific formatting. This will display your number with no formatting and all the decimals.

Number

This will display the number with no symbols or commas, but will default to two decimal places.

Currency

Same as above, currency will add a dollar sign, a comma to denote thousands, and two decimal places.

Accounting

Very similar to currency except that negatives are denoted by parentheses () rather than a negative (-). Also, the alignment will shift so that decimals and thousands will line up with other numbers in the column.

(Note that for the following three formats, you'll want to make sure you've entered a date or time, otherwise you'll end up with gobbeldy gook.)

Short Date

Will display the date as M/D/Y.

Long Date

Will display the date as Day, Month Day, Year.

Time

Will display the time as Hour:Minute:Second AM/PM

Percentage

Similar to above, percentage will multiply the number by 100, add a % sign, but will now include two decimals.

Fraction

Excel will round any decimals to a fraction with a single digit denominator.

Scientific

Remember that old 4.24 x 10^4 format you learned in school? That's that this is, though is uses E to denote the 10x.

Text

You have to be careful with text. Excel will start treating your number like it isn't a number anymore. I would only use this if you really know it's what you want.


More Number Formats

If you haven't found a format at this point that fits your exact needs, you can find more options in the drop down under More Number Formats. A pop up window will open.


Notice that all of the drop down formats are listed, but if you click one, there are several more options listed. Excel will even show you exactly what the number will look like under Sample.

There are two new formats listed here: Special and Custom. The purpose of this post is to do the basics, so I'm going to talk about those in a separate post where we can go into the details.


For now, you should have enough to get you started in building your tables and spreadsheets and making them easier to use.

As always, don't be afraid to try some things out, and feel free to email me at excealathome.blogspot@gmail.com.

Good luck!




2 comments:

  1. This is very helpful and informative. I have always struggled with Excel. Thank you for sharing at the Faith Filled Parenting LinkUp.

    ReplyDelete
    Replies
    1. I've noticed that so many people struggle with Excel, so you are not alone! I hope to help more people see how easy it is! Thanks for visiting!

      Delete