Changing the format of the cells in your worksheet can make your data much easier to read, both by setting data labels apart from the actual data and by adding borders to define the boundaries between labels and data even more clearly. Of course, using formatting options to change the font and appearance of a cell's contents doesn't help with idiosyncratic data types such as dates, phone numbers, or currency.
For example, consider U.S. phone numbers. These numbers are 10 digits long and have a 3-digit area code, a 3-digit exchange, and a 4-digit line number written in the form (###) ###-####. Although you can certainly type a phone number with the expected formatting in a cell, it's much simpler to simply type the 10 digits and have Excel 2007 change the data's appearance. You can tell Excel 2007 to expect a phone number in a cell by opening the Format Cells dialog box to the Number tab and displaying the formats under the Special category.
Clicking Phone Number from the Type list tells Excel 2007 to format 10-digit numbers in the standard phone number format. As you can see by comparing the contents of the active cell and the contents of the formula bar in the next graphic, the underlying data isn't changed, just its appearance in the cell.
If you type a nine-digit number in a field that expects a phone number, you won't see an error message; instead, you'll see a two-digit area code. For example, the number 425555012 would be displayed as (42) 555-5012. An 11-digit number would be displayed with a 4-digit area code.
Just as you can instruct Excel 2007 to expect a phone number in a cell, you can also have it expect a date or a currency amount. You can make those changes from the Format Cells dialog box by choosing either the Date category or the Currency category. The Date category enables you to pick the format for the date (and determine whether the date's appearance changes due to the Locale setting of the operating system on the computer viewing the workbook). In a similar vein, selecting the Currency category displays controls to set the number of places after the decimal point, the currency symbol to use, and the way in which Excel 2007 should display negative numbers.
The new Excel 2007 user interface enables you to set the most common format changes by using the controls in the Home tab's Number group.
You can also create a custom numeric format to add a word or phrase to a number in a cell. For example, you can add the phrase per month to a cell with a formula that calculates average monthly sales for a year to ensure that you and your colleagues will recognize the figure as a monthly average. To create a custom number format, click the Home tab and then click the Number group's dialog expander to display the Format Cells dialog box. Then, if necessary, click the Number tab.
In the Category list, click Custom to display the available custom number formats in the Type list. You can then click the base format you want and modify it in the Type box. For example, clicking the 0.00 format causes Excel 2007 to format any number in a cell with two digits to the right of the decimal point.
The zeros in the format indicate that the position in the format can accept any number as a valid value.
To customize the format, click in the Type box and add any symbols or text you want to the format. For example, typing a dollar sign to the left of the existing format and then typing "per month" to the right of the existing format causes the number 1500 to be displayed as $1500.00 per month.
You need to enclose any text in quotes so that Excel 2007 recognizes the text as a string to be displayed in the cell.
In this exercise, you assign date, phone number, and currency formats to ranges of cells in your worksheet. After you assign the formats, you test them by entering customer data.
USE the ExecutiveSearch workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Appearance folder.
OPEN the ExecutiveSearch workbook.
CLOSE the ExecutiveSearch workbook.