4 Change workbook appearance

Efficiently entering data into a workbook saves you time, but you must also ensure that your data is easy to read. Microsoft Excel 2019 gives you a wide variety of ways to make your data easier to understand. For example, you can change the font, character size, or color used to present a cell’s contents. Changing how data appears on a worksheet helps set the contents of a cell apart from the contents of surrounding cells. To save time, you can define a number of custom formats and then apply them quickly to the cells you want to emphasize.

You might also want to specially format a cell’s contents to reflect the value in that cell. For example, you could create a worksheet that displays the percentage of improperly delivered packages from each regional distribution center. If that percentage exceeds a threshold, Excel could display a red traffic light icon, indicating that the center’s performance requires attention.

This chapter guides you through procedures related to changing the appearance of data, applying existing formats to data, making numbers easier to read, changing data’s appearance based on its value, and adding images to worksheets.

Format cells

Excel worksheets can hold and process lots of data, but when you manage numerous worksheets, it can be hard to remember from a worksheet’s title exactly what data is kept in that worksheet. Data labels give you and your colleagues information about data in a worksheet, but it’s important to format the labels so that they stand out visually. To make your data labels or any other data stand out, you can change the format of the cells that hold your data.

Many of the formatting-related buttons on the ribbon have arrows at their right edges. Clicking the arrow displays a list of options for that button, such as the fonts available on your system or the colors you can assign to a cell.

You can also make a cell stand apart from its neighbors by adding a border around the cell or changing the color or shading of the cell’s interior.

If you want to change the attributes of every cell in a row or column, you can click the header of the row or column you want to modify and then select the format you want.

One task you can’t perform by using the tools on the ribbon is to change the default font for a workbook, used in the formula bar. The default font when you install Excel is Calibri, a simple font that is easy to read on a computer screen and on the printed page. If you’d prefer to change the default font, you can do so, but only from the Excel Options dialog box, not from the ribbon.

To change the font used to display cell contents

1. Select the cell or cells you want to format.

2. On the Home tab of the ribbon, in the Font group, click the Font arrow.

3. In the font list, click the font you want to apply.

To change the size of characters in a cell or cells

1. Select the cell or cells you want to format.

2. Click the Font Size arrow.

3. In the list of sizes, click the size you want to apply.

To change the size of characters in a cell or cells by one increment

1. Select the cell or cells you want to format.

2. Click the Increase Font Size button.

   Or

   Click the Decrease Font Size button.

To change the color of a font

1. Select the cell or cells you want to format.

2. Click the Font Color arrow (not the button).

3. Click the color you want to apply.

   Or

   Click More Colors, select the color you want from the Colors dialog box, and then click OK.

To change the background color of a cell or cells

1. Select the cell or cells you want to format.

2. Click the Fill Color arrow (not the button).

   

3. Click the color you want to apply.

   Or

   Click More Colors, select the color you want from the Colors dialog box, and click OK.

To add a border to a cell or cells

1. Select the cell or cells you want to format.

2. Click the Border arrow (not the button).

3. Click the border pattern you want to apply.

   Or

   Click More Borders, select the borders you want from the Border tab of the Format Cells dialog box, and click OK.

To change font appearance by using the controls on the Font tab of the Format Cells dialog box

1. Click the Font dialog box launcher.

2. Make the formatting changes you want, and then click OK.

To copy formatting between cells

1. Select the cell that contains the formatting you want to copy.

2. Click the Format Painter button.

3. Select the cells to which you want to apply the formatting.

Or

1. Select the cell that contains the formatting you want to copy.

2. Double-click the Format Painter button.

3. Select cells or groups of cells to which you want to apply the formatting.

4. Press the Esc key to turn off the Format Painter.

To delete cell formatting

1. Select the cell or cells from which you want to remove formatting.

2. In the Editing group, click the Clear button.

   

3. In the menu that appears, click Clear Formats.

To change the default font of a workbook

1. Display the Backstage view, and then click Options.

2. On the General page of the Excel Options dialog box, in the Use this as the default font list, click the font you want to use.

3. In the Font size list, click the font size you want.

4. Click OK.

5. Exit and restart Excel to complete the default font change.

Define styles

As you work with Excel, you will probably develop preferred formats for data labels, titles, and other worksheet elements. Instead of adding a format’s characteristics one element at a time to the target cells, you can format the cell in one action by using a cell style. Excel comes with many built-in styles, which you can apply by using the Cell Style gallery. You can also create your own styles by using the Style dialog box, which will appear in a separate group at the top of the gallery when you open it. If you want to preview how the contents of your cell (or cells) will look when you apply the style, point to the style to get a live preview.

It’s likely that any cell styles you create will be useful for more than one workbook. If you want to include cell styles from another workbook in your current workbook, you can merge the two workbooks’ style collections.

To apply a cell style to worksheet cells

1. Select the cells to which you want to apply the style.

2. On the Home tab, in the Styles group, click the Cell Styles button.

3. In the gallery that appears, click the style you want to apply.

To create a new cell style

1. Click the Cell Styles button, and then click New Cell Style.

   

2. In the Style dialog box, enter a name for the new style.

3. Select the check boxes next to any elements you want to include in the style definition.

4. Click the Format button.

5. Use the controls in the Format Cells dialog box to define your style.

6. Click OK.

To create a cell style based on existing cell formatting

1. Select a cell that contains the formatting on which you want to base your new cell style.

2. Click the Cell Styles button, and then click New Cell Style.

   Excel displays the Style dialog box with the active cell’s characteristics filled in.

3. Enter a name for the new style.

4. Click OK.

To modify an existing cell style

1. Click the Cell Styles button, right-click the style you want to modify, and then click Modify.

2. In the Style dialog box, make the necessary changes to your style name and style elements.

3. Click the Format button.

4. Use the controls in the Format Cells dialog box to define your style.

5. Click OK.

To create a new cell style based on an existing cell style

1. Click the Cell Styles button, right-click the style you want to duplicate, and then click Duplicate.

2. In the Style dialog box, type a distinct name for the duplicate version of the style and select the elements of the existing style to include in the duplicated one.

3. Click the Format button.

4. Use the controls in the Format Cells dialog box to make any other changes to the duplicate style.

5. Click OK.

To merge cell styles from another open workbook

1. Click the Cell Styles button, and then click Merge Styles.

2. In the Merge Styles dialog box, click the workbook from which you want to import cell styles.

3. Click OK.

To delete a custom cell style

1. Click the Cell Styles button, right-click the style you want to delete, and then click Delete.

Apply workbook themes and Excel table styles

Excel 2019 includes powerful design tools that you can use to quickly create workbooks and worksheets that look attractive and professional. These tools include workbook themes and table styles.

A theme is a way to specify the fonts, colors, and graphic effects that appear in a workbook. Excel comes with many themes.

When you start to format a workbook element, Excel displays a palette of colors with two sections: standard colors, which remain constant regardless of the workbook’s theme, and colors that are available within the active theme. If you format workbook elements by using colors specific to a theme, applying a different theme changes the colors of those elements.

You can change a theme’s colors, fonts, and graphic effects. If you like the combination you create, you can save your changes as a new theme that will appear at the top of the Themes gallery.

Just as you can define and apply themes to entire workbooks, you can apply and define Excel table styles. After you give your style a descriptive name, you can set the appearance for each Excel table element, decide whether to make your new style the default for the current document, and save your work.

To apply an Office theme to a workbook

1. On the Page Layout tab of the ribbon, in the Themes group, click the Themes button.

2. Click the theme you want to apply.

To change the fonts, colors, and effects of an Office theme

1. In the Themes group, click the Colors, Fonts, or Effects button.

2. Click the set of colors, fonts, or effects you want to apply.

To create a new Office theme based on an existing theme

1. Use the controls in the Themes group to change the fonts, colors, or effects applied to the current theme.

2. Click the Themes button, and then click Save Current Theme.

3. Enter a name for your new theme.

4. Click Save.

To delete a custom Office theme

1. Click the Themes button, and then click Save Current Theme.

2. In the Save Current Theme dialog box, right-click the theme you want to delete, and then click Delete.

3. Click Yes.

To apply a table style

1. Click any cell in the list of data you want to format as a table.

2. On the Home tab, in the Styles group, click the Format as Table button, and then click the table style you want to apply.

3. In the Format as Table dialog box, verify that Excel has identified the data range correctly.

   

4. Select or clear the My table has headers check box to reflect whether or not your list of data has headers.

5. Click OK.

To apply a table style and overwrite existing formatting

1. Click any cell in the list of data you want to format as a table.

2. Click the Format as Table button, and right-click the table style you want to apply.

3. On the shortcut menu that appears, click Apply and Clear Formatting.

4. Click OK.

To create a new table style

1. Click the Format as Table button, and then click New Table Style.

2. In the New Table Style dialog box, enter a name for the new style.

3. Click the table element you want to format.

4. Click the Format button, change the element by using the controls in the Format Cells dialog box, and then click OK.

5. Repeat the previous step to change the format of other elements.

6. Click OK to close the New Table Style dialog box.

To modify an existing table style

1. Click the Format as Table button, right-click the table style you want to modify, and then click Modify.

   Important

   You can’t modify the built-in Excel table styles, just the ones you create.

2. In the Modify Table Style dialog box, edit style elements you want to modify.

3. Click OK.

To delete a table style

1. Click the Format as Table button, right-click the table style you want to delete, and then click Delete.

   Important

   You can’t delete the built-in Excel table styles, just the ones you create.

2. In the message box that appears, click OK.

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.

You can also make idiosyncratic data types such as dates, phone numbers, or currency values easier to read by presenting them in a standardized way, regardless of how it was entered into Excel. 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—that is, with the area code surrounded by parentheses and the exchange and digit values separated by a hyphen—it’s much more straightforward to enter a sequence of 10 digits and have Excel add those formatting elements. To do so, you indicate to Excel that the contents of the cell will be a phone number.

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.

Just as you can instruct Excel to expect a phone number in a cell, you can also inform Excel that a cell will contain 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”.

   

5. Click OK.

Change the appearance of data based on its value

Recording information such as package volumes, vehicle miles, and other business data in a worksheet enables you to make important decisions about your operations. And as you saw earlier in this chapter, you can change the appearance of data labels and the worksheet itself to make interpreting your data easier.

Another way you can make your data easier to interpret is to have Excel change the appearance of your data based on its value. The formats that make this possible are called conditional formats, because the data must meet certain conditions, defined in conditional formatting rules, to have a format applied to it. In Excel, you can define conditional formats that change how the app displays data in cells that contain values above or below the average values of the related cells, that contain values near the top or bottom of the value range, or that contain values duplicated elsewhere in the selected range.

When you select which kind of condition to create, Excel displays the Conditional Formatting Rules Manager, which contains fields and controls you can use to define your rule. If your cells already have conditional formats applied to them, you can display those formats, determine the order in which they are applied, and indicate how Excel should react if more than one rule is true.

You can control your conditional formats in the following ways:

• Create a new rule.

• Change a rule.

• Remove a rule.

• Move a rule up or down in the order.

• Control whether Excel continues evaluating conditional formats after it finds a rule to apply.

• Save any rule changes and stop editing rules.

• Save any rule changes and continue editing.

• Discard any unsaved changes.

Clicking the New Rule button in the Conditional Formatting Rules Manager opens the New Formatting Rule dialog box. The commands in the New Formatting Rule dialog box duplicate the options displayed when you click the Conditional Formatting button in the Styles group on the Home tab. You can use those controls to define your new rule and the format to be displayed if the rule is true.

You can also create three other types of conditional formats in Excel: data bars, color scales, and icon sets.

Data bars summarize the relative magnitude of values in a cell range by extending a band of color across the cell.

When data bars were introduced in Excel 2007, they filled cells with a color band that decreased in intensity as it moved across the cell. This pattern, called a gradient fill, made it a bit difficult to determine the relative length of two data bars because the end points weren’t as distinct as they would have been if the bars were a solid color. In Excel 2019 you can choose between a solid fill pattern, which makes the right edge of the bars easier to discern, and a gradient fill pattern, which you can use if you share your workbook with colleagues who use Excel 2007.

Excel 2019 also draws data bars differently than in Excel 2007. Excel 2007 drew a very short data bar for the lowest value in a range and a very long data bar for the highest value. The problem was that similar values could be represented by data bars of very different lengths if there wasn’t much variance among the values in the conditionally formatted range. In Excel 2019, data bars compare values based on their distance from zero, so similar values are summarized by using data bars of similar lengths.

Color scales compare the relative magnitude of values in a cell range by applying colors from a two-color or three-color set to your cells. The intensity of a cell’s color reflects the value’s tendency toward the top or bottom of the values in the range.

Finally, icon sets are collections of three, four, or five images that Excel displays when certain rules are met.

When icon sets were introduced in Excel 2007, you could apply an icon set as a whole, but you couldn’t create custom icon sets or choose to have Excel 2007 display no icon if the value in a cell met a criterion. In Excel 2019, you can display any icon from any set for any criterion or display no icon. Plus, you can edit the icon set in other ways so it summarizes your data exactly as you want it to.

When you click a color scale or icon set in the Conditional Formatting Rules Manager and then click the Edit Rule button, you can control when Excel applies a color or icon to your data.

To create a conditional formatting rule

1. Select the cells to which you want to apply a conditional formatting rule.

2. On the Home tab, in the Styles group, click the Conditional Formatting button, point to Highlight Cells Rules, and then click the type of rule you want to create.

3. In the Conditional Formatting Rules Manager that appears, set the rules for the condition.

4. Click the arrow next to the with box, and then click Custom Format.

5. Use the controls in the Format Cells dialog box to define the custom format.

6. Click OK to close the Format Cells dialog box.

7. Click OK to close the Conditional Formatting Rules Manager.

To edit a conditional formatting rule

1. Select the cells to which the conditional formatting rule is applied.

2. Click the Conditional Formatting button, and then click Manage Rules.

3. In the Conditional Formatting Rules Manager, click the rule you want to edit.

4. Click Edit Rule.

5. Use the controls in the Edit Formatting Rule dialog box to change the rule settings.

6. Click OK twice to close the Edit Formatting Rule dialog box and the Conditional Formatting Rules Manager.

To change the order of conditional formatting rules

1. Select the cells to which the conditional formatting rules are applied.

2. Click the Conditional Formatting button, and then click Manage Rules.

3. In the Conditional Formatting Rules Manager, click the rule you want to move.

4. Click the Move Up button to move the rule up in the order.

   Or

   Click the Move Down button to move the rule down in the order.

5. Click OK.

To stop applying conditional formatting rules when a condition is met

1. Select the cells to which the conditional formatting rules are applied.

2. Click the Conditional Formatting button, and then click Manage Rules.

3. In the Conditional Formatting Rules Manager, select the Stop If True check box next to the rule where you want Excel to stop.

   

4. Click OK.

To create a data bar conditional format

1. Select the range to which you want to apply the conditional format.

2. Click the Conditional Formatting button, point to Data Bars, and then click the format you want to apply.

To create a color scale conditional format

1. Select the range to which you want to apply the conditional format.

2. Click the Conditional Formatting button, point to Color Scales, and then click the color scale you want to apply.

To create an icon set conditional format

1. Select the range to which you want to apply the conditional format.

2. Click the Conditional Formatting button, point to Icon Sets, and then click the icon set you want to apply.

To delete a conditional format

1. Select the cells to which the conditional formatting rule is applied.

2. Click the Conditional Formatting button, and then click Manage Rules.

3. In the Conditional Formatting Rules Manager, click the rule you want to delete.

   

4. Click Delete Rule.

5. Click OK.

To delete all conditional formats from a worksheet

1. Click the Conditional Formatting button, point to Clear Rules, and then click Clear Rules from Entire Sheet.

Add images to worksheets

Establishing a strong corporate identity helps you ensure that your customers remember your organization and the products and services you offer. Setting aside the obvious need for sound management, one important attribute of a strong business is an eye-catching, easy-to-remember logo. After you or your graphic artist creates a logo, you should add an image file containing the logo to all your documents, especially any that might be seen by your customers. Not only does the logo mark the documents as coming from your company, it also serves as an advertisement, encouraging anyone who sees your worksheets to call or visit your company.

One way to add a logo or any other type of image to a worksheet is to locate the picture you want to add from your hard disk, insert it, and then make any formatting changes you want. For example, you can rotate, reposition, and resize the picture.

With Excel 2019, you can remove the background of an image you insert into a workbook. When you indicate that you want to remove an image’s background, Excel guesses which aspects of the image are in the foreground and eliminates the rest.

You can drag the handles on the inner square of the background removal tool to change how the tool analyzes the image.

If you want to generate a repeating image in the background of a worksheet to form a tiled pattern or texture behind your worksheet’s data, or perhaps add a single image that serves as a watermark, you can do so.

To add an image stored on your computer to a worksheet

1. On the Insert tab of the ribbon, in the Illustrations group, click Pictures.

2. In the Insert Picture dialog box, navigate to the folder that contains the image you want to add to your worksheet.

3. Click the image.

4. Click Insert.

To add an online image by using Bing Image Search

1. In the Illustrations group, click the Online Pictures button.

2. In the Insert Pictures dialog box, enter search terms identifying the type of image you want to find online.

3. Press Enter.

4. Click the image you want to add to your worksheet.

5. Click Insert.

To resize an image

1. Click the image.

   

2. Drag one of the handles that appears on the image’s border.

   Or

   On the Format tool tab of the ribbon, in the Size group, enter new values for the image’s vertical and horizontal size in the Height and Width boxes.

To edit an image

1. Click the image.

2. Use the controls in the Size group to change your image’s appearance.

To delete an image

1. Click the image.

2. Press the Delete key.

To remove the background from an image

1. Click the image.

2. Click the Remove Background button.

3. Drag the handles on the frame until the foreground of the image is defined correctly.

4. On the Background Removal tool tab of the ribbon, click the Keep Changes button.

To set an image as a repeating background

1. On the Page Layout tab, in the Page Setup group, click the Background button.

2. Click the Browse button and navigate to the folder that contains the file you want to use as your repeating background.

   Or

   Enter search terms in the Search Bing box, and then press Enter.

3. Click the image you want to set as your background.

4. Click Insert.

Skills review

In this chapter, you learned how to:

• Format cells

• Define styles

• Apply workbook themes and Excel table styles

• Make numbers easier to read

• Change the appearance of data based on its value

• Add images to worksheets

Practice tasks

The practice files for these tasks are located in the Excel2019SBSCh04 folder. You can save the results of the tasks in the same folder.

Format cells

Open the FormatCells workbook in Excel, and then perform the following tasks:

1. Change the formatting of cell B4 so the text it contains appears in 14-point, bold type.

2. Center the text within cell B4.

3. Change the background fill color of cell B4.

4. Draw a border around the cell range B4:C13.

Define styles

Open the DefineStyles workbook in Excel, and then perform the following tasks:

1. Apply an existing cell style to the values in cells B4 and C3.

2. Create a new cell style and apply it to the values in cell ranges B5:B13 and C4:N4.

3. Edit the font size of the new cell style you just created and save it under a new name.

Apply workbook themes and Excel table styles

Open the ApplyStyles workbook in Excel, and then perform the following tasks:

1. Display the MilesLastWeek worksheet and change the theme applied to the workbook.

2. Change the colors used for the new theme.

3. Save a new theme based on the settings currently applied to the workbook.

4. On the Summary worksheet, create an Excel table from the list of data in the cell range A1:B10.

5. Define a new Excel table style and apply it to the same data.

Make numbers easier to read

Open the FormatNumbers workbook in Excel, and then perform the following tasks:

1. Apply a phone number format to the value in cell G3.

2. Apply a currency or accounting format to the value in cell H3.

3. For cell H3, create a custom number format that displays the value in that cell as $255,000 plus benefits.

Change the appearance of data based on its value

Open the CreateConditionalFormats workbook in Excel, and then perform the following tasks:

1. Apply a conditional format to cell C15 that displays the cell’s contents with a red background if the value the cell contains is less than 90 percent.

2. Apply a data bar conditional format to cells C4:C12.

3. Apply a color scale conditional format to cells F4:F12.

4. Apply an icon set conditional format to cells I4:I12.

5. Delete the conditional format from the cell range C4:C12.

6. Edit the data bar conditional format so the bars are a different color.

Add images to worksheets

Open the AddImages workbook in Excel, and then perform the following tasks:

1. Insert the phone image file from the Excel2019SBSCh04 folder.

2. Remove the background from the phone image.

3. Resize the phone image so it will fit between the Call Volume label in cell B4 and the top of the worksheet.

4. Move the image to the upper-left corner of the worksheet, resizing it if necessary so it doesn’t block any of the worksheet text.

5. Add a repeating background image by using the texture image from the Excel2019SBSCh04 folder.