Display Numbers, Dates, & Times in the Correct Format in Excel

In this tutorial I am going to look at Number formatting.

Number formatting allows you to change how numbers are displayed in Excel, anything from currencies to times to dates, decimals, percentages, and more.

As an example I will go back to the table of data from the previous tutorial and make various Number formatting changes. First I will change the format of the Order Number column to Number:

I select the Order Number column then go to the Number section of the Home tab. The dropdown menu which has General selected by default has many Number format options, all of which can be accessed by clicking the More Number Formats option at the bottom of the dropdown menu.

To access this feature in previous versions of Excel, right-click the desired cells, go to Format Cells, and then go to the Number tab.

As my order number has more than 3 digits I need to comma separate the numbers to improve readability. To do this, click the Comma Style button in the Number section.

By default, Number formatting has 2 decimal places but this isnt needed for my Order Number column. To reduce the decimal places click the arrow pointing to the right. (Indicating that all numbers will be moved to the right and a decimal place will be removed)

Next I change the Order Date and Order Time columns to date/time formatting. This time around I am going to use the Format Cells pop up which is how Number formatting is applied prior to Excel 2007. (right-click then select Format Cells)

Select the Number tab to get the formatting options. To get date options select Date then choose a date format. To get Time formatting options select Time then select a time format. For this example I went with the full date for the Order Date column and have changed the Order Time column to 24 hour.

The Format Cells pop up window has a greater selection of number formats than the Home tab. There is even a Custom number format option but this will be covered in a later tutorial.

The next format changes to make are changing the Order Total column to Currency and the Order Completion column to %. These changes are simple, like before you just go to the Number formatting dropdown and select the required option.

 

Once you have added Number formatting Excel will treat the cell values differently. The currency columns may have £ symbols but will still be treated as numbers in calculations and %s will be treated like there decimal equivalents in calculations. (E.g. 36% will be treated as 0.36 in calculations)

Most formats dont change the underlying values too much apart from Date and Time formats. How these values appear compared to their underlying numerical value is completely different. Below I have converted a row back into just numbers.

The Order Date and Order Time values of 42142 and 0.30 mean nothing to you and me but, when converted, Excel can interpret them as a date and a time perfectly.

Note: Accompanying Excel file has a sheet with no number formats and a sheet with number formats so you can see the difference between the 2.