Excel Formatting
Related Pages:
Custom Number FormattingHow to Merge Excel Cells
Wrap Text in Excel Cells
Excel Conditional Formatting
As well as giving your spreadsheet a professional look, the use of Excel formatting can provide essential information that influences the way a user interprets the data in the spreadsheet.
This is particularly the case with Excel numbers.
As dates, time, percentages and currency values are all stored as numeric values in Excel, the user often has to rely of the formatting of these numbers to understand what they represent.
For example, a cell containing the data value 0.5 could represent any of the following:
- The time 12:00 hrs
- The date and time 01-Jan-1900 12:00
- The currency value $0.50
- The percentage value 50%
- The simple number 0.5
The spreadsheet below shows different ways of formatting Excel cells containing numeric values.
| A | B | C | |
|---|---|---|---|
| 1 | Formatted Value | Underlying Value | |
| 2 | A number formatted as a percentage: | 59% | 0.59 |
| 3 | A number formatted as a currency: | $54.27 | 54.27 |
| 4 | A number formatted as a date: | 01 August 2008 | 39661 |
| 5 | A number formatted as a time: | 12:27 PM | 0.51875 |
| 6 | A number formatted as a fraction: | 1 3/4 | 1.75 |
How To Change Formatting in Excel
To format data in Excel cells, you first need to select the cells to be formatted. Some of the more popular formatting options can then be accessed directly from the Home tab of the ribbon (see below).
However, for the full menu of formatting options, you will need to use the 'Format Cells' dialog box, which can be accessed by any of the following methods:
- Right-click on the selected cell or range and select the Format Cells ... option from the right-click menu
or
- Press Ctrl + 1 (i.e. Select the Ctrl key and while holding this down, select the "1" (one) key)
or
- Click on the dialog box launcher in the Number grouping within the Home tab of the Excel ribbon (see rightabove).
The 'Format Cells' dialog box is shown on the leftabove.
Make sure that the Number tab at the top of the dialog box is selected then, from the Category options (on the left of the dialog box), select the Excel formatting type that you want to apply to your cell (e.g. Number, Percentage, Date, etc).
When you select a category, you may be presented with further options on the right hand side of the dialog box, which you can use to specify your formatting style.
Once you have specified the formatting details that you require, click OK to close the dialog box.
Note that the listed formats will only work with numbers, so if you apply a date format to a cell containing text, the appearance of the cell will remain unchanged.