Office 2016 For Seniors For Dummies (2016)

Part III

Excel

For an explanation of Excel's IF function, visit www.dummies.com/extras/office2016forseniors.

Chapter 9

Creating Visual Interest with Formatting and Charts

Get ready to . . .

Adjust Row Height and Column Width

Wrap Text in a Cell

Apply Gridlines or Borders

Apply Fill Color

Format Text in Cells

Format the Spreadsheet as a Whole

Create a Basic Chart

Identify the Parts of a Chart

Format a Chart

Large spreadsheets with many rows and columns of numbers can be confusing or boring to look at. What do the numbers really mean? Which number goes with what descriptive text?

You can make a spreadsheet less boring and more informative by applying formatting. You can change font and text size, apply background shading, add borders, and lots more. You can also adjust the row and column sizes to create space between columns or rows of text.

To help users interpret the data, you can also add charts that summarize the data and/or show trends in the data. Excel offers a wide variety of chart types, enabling you to customize virtually every aspect of a chart’s formatting.

Adjust Row Height and Column Width

By default, each column is the same width. If you type text that exceeds the column width, one of two things happens:

· If the cell to the right is blank: The text overflows into it.

· If the cell to the right is not blank: The text appears cut off (truncated). The full text is still stored there, though.

Figure 9-1 illustrates each case.

Figure 9-1

To fix such a problem, you must widen the column. Here are some ways how:

· To widen the column exactly enough to hold the longest entry in it: Double-click the divider between the column headers, or choose Home ⇒ Cells ⇒ Format ⇒ AutoFit Column Width.

· To widen the column manually: Drag the divider between the column headers. See Figure 9-2.

Figure 9-2

You can also make a column narrower; just drag to the left, rather than to the right.

This process also works with row heights. You can drag the divider between two row numbers to change the row height, or double-click the divider to autofit to the content.

Row heights adjust automatically to fit the tallest text in them, so you don’t have to worry about text being vertically truncated in rows — usually. If you manually adjust the height of a row and then put some larger text into it, the larger text might become truncated because the row height has been fixed. To make it autofit again, choose Home ⇒ Cells ⇒ Format ⇒ AutoFit Row Height.

Wrap Text in a Cell

Sometimes, rather than making a row wider to make its content fit, you might want the content to wrap to multiple lines within the cell (like a paragraph in a Word document). To do this, select the cell and then choose Home ⇒ Alignment ⇒ Wrap Text. (See Figure 9-3.)

Figure 9-3

Wrapping is handy for column headings and long explanations.

Apply Gridlines or Borders

When you look at your worksheet onscreen, you see faint lines — gridlines — that separate the rows and columns.

By default, gridlines appear onscreen but not in print. To control how gridlines appear, display the Page Layout tab and then mark or clear the View and/or Print check boxes in the Sheet Options/Gridlines group. See Figure 9-4. (There is also a Gridlines check box on the View tab, but it controls only the onscreen display, not the print setting.)

Figure 9-4

You can also apply other lines around one or more sides of each cell: borders. These lines can be any color or thickness you want. Borders always display onscreen and always print, regardless of settings. Borders are useful for helping the reader’s eyes follow the text across the printed page, and for identifying which parts of a spreadsheet go together logically.

The easiest way to apply and format borders is to use the Borders button’s drop-down list on the Home tab. (It’s in the Font group, as shown in Figure 9-5.) Here’s how:

Figure 9-5

1. Select the range of cells to which you want to apply the border.

2. Choose Home ⇒ Font ⇒ Borders.

3. From the list of borders that appears, choose one that best represents the side(s) to which you want to apply the border.

The border will apply to the outside edges of the range you select. So, for example, choosing Top Border applies a top border only to the cells in the top row of the range, not to the top of every cell in that range.

· To add a border on all sides of each cell in the range: Choose All Borders.

· To remove the border from all sides of all cells in the selected range: Choose No Border.

· To add borders on more than one side but not all sides: Repeat the process several times, each time choosing one individual side.

If you want to choose a specific color, style, or weight for the border, follow these steps:

1. Choose Home ⇒ Font ⇒ Borders ⇒ More Borders (the bottom command on the menu from Figure 9-5). The Format Cells dialog box appears with the Border tab displayed. See Figure 9-6.

2. In the Style area, click the desired line style.

3. In the Color area, open the drop-down list and click the desired color.

4. In the Presets area, click the preset for the sides you want to apply the border to: None, Outline, or Inside.

If you want the border around each side of each cell, click both Outline and Inside.

or

In the Border area, click the button(s) for individual sides, or click directly on the sample area in the desired spots, which applies the border to only certain sides.

Step 4 is necessary. If you choose a border style and color but don’t apply it to any sides of the range, that’s like selecting nothing at all.

5. Click OK.

Figure 9-6

Apply Fill Color

Fill color — also called shading — is the color or pattern that fills the background of one or more cells.

Shading can help the reader’s eyes follow information across a page, and can add color and visual interest to a worksheet. In some types of spreadsheets, such as a checkbook register, it’s common to shade every other line, for easier reading.

The fill color you choose can be any of the following. (See Figure 9-7.)

· A theme color, or a tint/shade of a theme color: These colors do change if you apply a different theme.

· A standard color: These colors don’t change if you apply a different theme to the workbook.

· No Fill: This removes all existing fill from the selected cells.

Figure 9-7

To apply fill color, follow these steps:

1. Select the cell(s) to be filled.

2. On the Home tab, click the down arrow to the right of the Fill Color button, opening its menu.

Clicking the Fill Color button applies whatever color is selected (that is, the color that currently appears on the button’s face) without opening the menu.

3. Choose your color using one of these methods:

· Click one of the color swatches in the Theme Colors palette.

· Click one of the colors in the Standard Colors palette.

· Click More Colors to open a dialog box of additional standard colors. Choose one and then click OK.

To change the workbook’s theme, choose Page Layout ⇒ Themes. Changing themes changes the fonts and colors used. To change only the colors, choose Page Layout ⇒ Themes ⇒ Colors.

Format Text in Cells

Text formatting within a cell works very much like it does in Word and PowerPoint. To change the font, font size, color, or attributes (such as bold or italic) for an Excel spreadsheet cell or range, follow these steps:

1. Select the cell(s).

2. On the Home tab, use the buttons and drop-down lists in the Font group to apply text formatting. Figure 9-8 points them out.

Figure 9-8

Text alignment controls how the text lines up within cells. Figure 9-9 points out the controls on the Home tab for working with cell alignment, and also shows some examples. Cell alignment refers to how the text interacts with the available space in the cell.

· Horizontal orientation describes whether the text is left-aligned, right-aligned, or centered when the cell is wider than needed to accommodate the entry.

· Vertical orientation describes whether the text aligns with the top or bottom of the cell, or is centered vertically between the top and bottom, when the cell is taller than needed to accommodate the entry.

Figure 9-9

Orientation refers to the direction of the text. By default, text runs horizontally from left to right. You can change that with the Orientation button on the Home tab. For example, you could use vertical or slanted text so that labels in a heading row take up less space horizontally.

1. Select the cell(s).

2. Click the Orientation button.

3. Make a selection from its menu.

Figure 9-10 shows the menu and also some rotated text.

Figure 9-10

Excel differentiates between angled and rotated text on the Orientation menu (shown in Figure 9-10). Angled text is diagonal; rotated text is straight up-and-down.

Format the Spreadsheet as a Whole

The Page Layout tab contains various formatting controls that affect the entire worksheet.

These controls affect the worksheet when it’s printed. (See Figure 9-11.)

Figure 9-11

· Margins: Here, you can set the page margins for printing your worksheet. Choose from among the presets or choose Custom Margins to set your own.

· Orientation: Choose from Portrait (tall) or Landscape (wide) for printing your worksheet.

· Size: Choose the paper size. The default is regular letter size (8.5” x 11”).

· Print Area: Use this to set only a portion of the worksheet to print. This is a handy option if you don’t want everything on the sheet to print. For example, you have two tables of data on the same sheet, but you want to print only one.

· Background: Use this to place a picture behind the cells of the sheet, such as a logo. (This is uncommon because pictures tend to interfere with the readability of the data.)

· Print Titles: Click this button to open the Page Setup dialog box with the Sheet tab displayed. From there, you can select certain rows and columns to repeat on every page of a multipage printout (such as a row containing column headings, for example).

· Scale to Fit (group): Options here enable you to force your printout to fit on a certain number of pages by automatically decreasing the size as needed. You can scale to a certain number of pages (with the Height and Width lists), or you can set an overall scale percentage. Refer to Figure 9-11.

Create a Basic Chart

When you have a lot of numeric data on a sheet, it can be difficult to discern its meaning. Look at Figure 9-12 to see what I mean. Even though this worksheet is attractively formatted, there are still a lot of numbers. Using a chart might help make more sense out of them.

Figure 9-12

Excel offers various chart types, each suited for a different type of data analysis. For example, Figure 9-13 shows two different charts created from portions of the data from Figure 9-12.

Figure 9-13

To create a chart, follow these steps:

1. Select the data to include on the chart. Include any cells that contain text labels that should be in the chart, too.

You might need to select a range of cells that don’t touch each other (noncontiguous) for Step 1. If that’s the case, hold down the Ctrl key while you select the cells you want.

2. On the Insert tab, click a chart type. (Use the buttons in the Charts group.) A menu opens showing a few common subtypes for that type. For example, in Figure 9-14, you can see some column chart subtypes. Choose one of these. A new chart is created and placed on the current sheet as a floating object.

Figure 9-14

For more chart subtypes, click More Charts at the bottom of the menu in Figure 9-14. (The exact command name depends on the button; in Figure 9-14 it is More Column Charts.) Then you can choose from the Insert Chart dialog box.

After you create the chart, you can

· Resize it. Drag one of the corners of its frame, or one of the selection handles on a side (represented by several dots).

· Move it. Drag any part of its frame except a corner or a side selection handle.

· Place it on its own tab. Choose Chart Tools Design ⇒ Location ⇒ Move Chart. Then in the Move Chart dialog box, click New Sheet and then click OK.

If the chart isn’t what you expected data-wise, try one of these techniques:

· Delete the chart and try again, selecting different ranges.

· Change how the data is plotted by choosing Chart Tools Design ⇒ Data ⇒ Switch Row/Column.

· Choose Chart Tools Design ⇒ Data ⇒ Select Data to redefine what cells are used to make the chart.

When a chart is selected, the Ribbon has an extra set of Chart Tools tabs: Design and Format. They disappear when you select something other than the chart, and reappear when you reselect it.

Identify the Parts of a Chart

Every part of the chart has a name, and learning the Excel lingo can help you format the specific parts. Figure 9-15 points out some of the key features of a chart.

· Chart area: The entire chart, including all the labels and extras: everything in the chart frame

· Plot area: The part of the chart that contains the data bars/area/pie/points

· Legend: The key that shows what each color represents

· Wall: The background of the plot area, if any

· Floor: On certain types of 3-D charts, the bottom of the plot area

· Data series: All the data points in the same data series (represented by a single color or legend key item)

· Data point: A single numeric value represented on the chart (for example, a single bar or point)

· Chart title: A text label that describes the entire chart

· Axis: A line on which data is plotted

Figure 9-15

A column chart has both a vertical and a horizontal axis.

Format a Chart

Nearly every aspect of a chart can be formatted differently. You can change the color of every data series, for example, and change the font and size of each text item. You can adjust the rotation of a 3-D chart, move the legend to different positions, add or remove the chart title and various types of labels, and much more.

The easiest way to change the look of a chart is to apply a chart style to it. Chart styles are located on the Design tab. With the chart selected, open the Chart Style gallery and select one of the style presets. See Figure 9-16.

Figure 9-16

The chart style colors come from the color theme that you applied to the workbook. To choose a different color theme, choose either Page Layout ⇒ Themes ⇒ Themes or Page Layout ⇒ Themes ⇒ Colors.

Each individual element of a chart can also be separately formatted. From the Chart Tools Design tab, click Add Chart Element to open a menu of chart elements, and then point to the desired element to see a submenu of formatting you can apply to it. See Figure 9-17. For each element you can also select the More command at the bottom of the submenu to open a task pane where you can fine-tune the formatting, as in Figure 9-17.

Figure 9-17

Here’s an easy way to turn a certain chart element on or off: Click the Chart Elements icon, which is the plus sign near the upper-right corner of the chart when the chart is selected. On the fly-out menu that appears, you can mark or clear the check box for the chart element. There is also a fly-out submenu with the same additional options as on the menu system on the Ribbon. See Figure 9-18. By the way, the other two icons nearby are the Chart Styles icon (which provides shortcut access to the various chart styles from the Chart Tools Design tab) and the Chart Filters icon (which enables you to quickly exclude certain data from the chart).

Figure 9-18

Each element of the chart is also selectable for formatting. For example, you can click the legend and then apply formatting to only that.

Sometimes it’s difficult to select a tiny element of a chart — and difficult to tell what you selected — so Excel offers some assistance. On the Chart Tools Format tab, the leftmost group is called Current Selection. It has a drop-down list from which you can select a chart element, as an alternative to clicking a chart element to select it.

After you select a chart element, use the tools on the Chart Tools Format tab (shown in Figure 9-19) to apply formatting:

Figure 9-19

· Use any of the controls here to apply a color, border, special effect, or other formatting option to it. For example, a Shape Styles gallery provides shapes and formatting presets, and a WordArt Styles gallery provides presets for various types of text formatting.

· Choose Format ⇒ Current Selection ⇒ Format Selection to open a task pane for formatting that element. See Figure 9-20.

Figure 9-20

Alternatively, you can right-click the selected element and choose the format command from the menu that appears. The exact name of the command varies; for example, when a data series is selected and right-clicked, the command is Format Data Series.

When you open the Format task pane for a particular element (select the element and then choose Chart Tools Format ⇒ Current Selection ⇒ Format Selection), the choices depend on the type of element. Click one of the icons at the top of the task pane to select a broad category of options, and then double-click headings to expand the options beneath them. Refer to Figure 9-20.

The changes you make in the Format task pane take effect immediately.