Number formats in Excel

One of the most common uses of Excel is entering quantitative data to be analyzed using functions. Whenever a numerical value is entered in Excel, it can be formatted in one of many ways. Let's take a look at these formatting options to get a sense for how we can use them to make our spreadsheets clean and easy to read.

Formatting options

We can view the various formatting options available by selecting a cell that contains a number and heading to the Number formatting drop-down on the Home ribbon tab:

Number formatting menu

The following are the most commonly-used formatting options available in Excel:

  • Number. Excel will format your input as a generic number with commas between the thousands digits.
  • Currency. Excel will format your input with a currency sign in front of it and two trailing digits after the decimal point.
  • Accounting. Same as Currency format, but currency sign will align to the left-hand side of the cell.
  • Date. Excel will format the given input as a date. Note that dates in Excel are represented as numerical quantities — see our date and time function overview for more information!
  • Time. Excel will format the given input as a date / time. Times are also represented as numerical quantities in Excel.
  • Percentage. Excel will format the given input as a Percentage. For example, an input of .76 will read as 76%.

It's important to note that number formatting will not actually change the value of cells in Excel. The number 2 formatted as a number, currency, accounting, or even percentage will not change it's actual value — it's still stored as the numerical quantity 2 in Excel.

Changing decimal points

Another handy feature of number formats in Excel is that they can be used to modify the number of trailing decimal places behind a number. Just click the Add / Remove Decimal Place buttons on the Home section of the Ribbon:

Add / remove decimal places

Let's try formatting our currency quantity with no decimal places to save some space:

Formatted number

Whenever you're working in Excel, make an effort to align the formatting of your numbers — including both formatting style and number of decimal places — with the information you're trying to convey to your viewers. If a high level of specificity is required, use many trailing decimal places. If not, consider eliminating decimal places to round to the nearest whole number.

Save three hours of work a day with these 5 simple Excel hacks

Work smarter, not harder. Sign up for our 5-day mini-course to receive must-learn lessons on how to automate your work using Excel.

  • Why the TRUE and FALSE functions are so important...
  • Why to rethink the way you do VLOOKUPs...
  • Plus, we'll reveal why you shouldn't use PivotTables and what to use instead...

By submitting this information, you agree to Deskbright's privacy policy.

Comments