How to Change Workbook Appearance in Microsoft Excel 2010

Making Numbers Easier to Read

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 values.

As an 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 it’s certainly possible to type a phone number with the expected formatting in a cell, it’s much simpler to type a sequence of 10 digits and have Excel change the data’s appearance.

You can tell Excel to expect a phone number in a cell by opening the Format Cells dialog box to the Number page and displaying the formats available for the Special category.

The Type list displays special formats that are specific to the location selected in the Locale list.

Clicking Phone Number in the Type list tells Excel to format 10-digit numbers in the standard phone number format. You can see this in operation if you compare the contents of the active cell and the contents of the formula box for a cell with the Phone Number formatting.

Click to view larger image

The Phone Number format applied to the number shown in the formula box.

Just as you can instruct Excel 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 should display negative numbers.

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 dialog box launcher (found at the bottom right corner of the Number group on the ribbon) 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 to format any number in a cell with two digits to the right of the decimal point.

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” (including quote marks) to the right of the existing format causes the number 1500 to be displayed as $1500.00 per month.

In this exercise, you’ll assign date, phone number, and currency formats to ranges of cells.

1. Click cell A3.

2. On the Home tab, click the Font dialog box launcher.

   The Format Cells dialog box opens.

3. If necessary, click the Number tab.

4. In the Category list, click Date.

   The Type list appears with a list of date formats.

   

   The Type list displays date formats that are specific to the location selected in the Locale list.

5. In the Type list, click 3/14/01.

6. Click OK to assign the chosen format to the cell.

   Excel displays the contents of cell A3 to reflect the new format.

7. Click cell G3.

8. On the Home tab, in the Number group, click the Number Format button’s down arrow and then click More Number Formats.

9. If necessary, click the Number tab in the Format Cells dialog box.

10. In the Category list, click Special.

    The Type list appears with a list of special formats.

11. In the Type list, click Phone Number, and then click OK.

    Excel displays the contents of the cell as (425) 555-0102, matching the format you selected, and the Format Cells dialog box closes.

12. Click cell H3.

13. Click the Font dialog box launcher.

14. In the Format Cells dialog box that opens, click the Number tab.

15. In the Category list, click Custom.

    The contents of the Type list are updated to reflect your choice.

    

    The Sample area displays a preview of the currently selected number format.

16. In the Type list, click the #,##0 item.

17. In the Type box, click to the left of the existing format, and type $. Then click to the right of the format, and type “before bonuses” (note the space after the opening quote).

18. Click OK.

    The Format Cells dialog box closes.

Click to view larger image

The custom number formatting is applied to the value in the active cell.