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.
We can view the various formatting options available by selecting a cell that contains a number and heading to the
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
.76will read as 76%.
It's important to note that number formatting will not actually change the value of cells in Excel. The number
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
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.
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...