Number formatting in Excel is the process of controlling the appearance of numbers in your spreadsheets. Formatting numbers appropriately will help your readers to read and understand them better. Questions you need to ask yourself when dealing with numbers in Excel include:
- Do I need to include a currency symbol for amounts?
- How many decimal places shall I display? That is, how accurate do the numbers need to be?
- What format should dates be in, for example dd/mm/yy, or dd/mm/yyyy or mm/dd/yy etc
All the number formatting commands are to be found on the Home tab in the Number group. Let’s format some numbers right now! Suppose we have a column of sales figures like this:
We can make these sales figures much more readable in just a few clicks. Select the column by clicking on its letter heading. Now click Home > Number > Number Format drop down selector. You should see the following number format options:
Select the Currency option and you should see the currency symbol that your region uses appear to the left of each number in the column. You should also see 2 decimal places appear.
You can tweak the number formatting, too, by clicking Home > Number > Number Format drop down selector, and then select More Number Formats at the bottom. The formatting of the currently selected cell(s) is initially shown in the Format Cells window, but you can change any aspect of it. Let’s change the number of decimal places to 0 for our sales figures.
There are lots and lots of different formatting options available in this window.
Note that any number formatting you apply affects only the value in the cell. The value displayed in the formula bar is unformatted.
Formatting Dates In Excel
Let’s have a quick look at how we can format dates in Excel. If you type in a value of “14/03/2011?, Excel will be able to recognise it as a date, and Date will be displayed in the Number Format selector. Note that dd/mm/yyyy is a typical date format used in the UK. If you want to change the date format to mm/dd/yy, for example, click Home > Number > Number Format (drop down selector) > More Number Formats, and make the following changes:
As you can see, you can also have the name of the month displayed too: “14 March 2012?
