As well as giving your spreadsheet a professional look, the use of Excel formatting can provide essential information that determines 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 numbers in Excel, it is only the formatting of these numbers that tells the user of the spreadsheet 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|
To Excel, a value in a cell, is stored as one of the following data types:
Text values will always be represented as simple text and logical values are displayed as either TRUE or FALSE.
However, numbers can be displayed in many different ways in an Excel Cell. As illustrated above, a numeric value may represent a date, a time, a percentage, a currency, etc.
This is illustrated by the spreadsheet below, which shows different ways of formatting Excel cells containing numeric values.
|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|
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 formatting toolbar. In particular, Excel 2007 and Excel 2010 have many of the data formatting options on the Home tab of the ribbon.
However, for the full menu of formatting options, you will need to use the Format Cells control box, which can be accessed by any of the following methods :
Do you have problems identifying whether the contents of a cell are stored as text or as a number by Excel?
Use the options in the Alignment tab of the Format Cells control box to set the horizontal alignment of your cells to "General".
This causes Excel to align numerical values to the right of the cell and text to the left of the cell. You can then tell, at a glance, the underlying data type of a cell's contents.
Once you have brought up the Format Cells control box, make sure the Number tab is selected. Within this tab, you will see a window with the heading Category, which has several data types listed.
Select the Excel formatting Category that you want to apply to your cell (e.g. Number, Percentage, Date, etc). For most of the categories, this will cause further options to appear on the right hand side of the control box, which you can use to tailor your formatting style.
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.