Home > Sample chapters

How to Change the Appearance of a Workbook in Microsoft Excel 2016

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

04fig12.jpg

Select built-in number formats from the Special category

You can watch this format 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.

04fig13.jpg

Change the appearance of data without affecting the underlying data

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 pick from a wide variety of date, currency, and other formats to best reflect your worksheet’s contents, your company standards, and how you and your colleagues expect the data to appear.

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. If one of the built-in formats is close to the custom format you’d like to create, you can base your custom format on the one already included in Excel.

To apply a special number format

  1. Select the cells to which you want to apply the format.
  2. On the Home tab, in the Number group, click the Number Format arrow, and then click More Number Formats.
  3. In the Format Cells dialog box, in the Category list, click Special.
  4. In the Type list, click the format you want to apply.
  5. Click OK.

To create a custom number format

  1. On the Number Format menu, click More Number Formats.
  2. In the Format Cells dialog box, in the Category list, click Custom.
  3. Click the format you want to use as the base for your new format.
  4. Edit the format in the Type box.
  5. Click OK.

To add text to a number format

  1. On the Number Format menu, click More Number Formats.
  2. In the Format Cells dialog box, in the Category list, click Custom.
  3. Click the format you want to use as the base for your new format.
  4. In the Type box, after the format, enter the text you want to add, in quotation marks—for example, “boxes”.

    04fig14.jpg

    Define custom number formats that display text after values

  5. Click OK.