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:

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:

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

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.

Comments