Chapter 6: Chart Details

Lesson 6 of Introduction to Excel taught you how to choose a chart type and build a chart from your data. This chapter covers the rest of MO-210's chart objectives: putting a chart on its own sheet, changing the data a chart shows, working with every chart element, applying layouts and styles, and making a chart accessible. Chapter 13 covers the advanced chart types on MO-211.

6.1 Creating charts quickly

As a reminder, select the data (including its labels) and choose a chart from the Insert tab, or choose Insert › Recommended Charts to see Excel's suggestions. Two shortcuts are handy on a timed exam:

• Alt+F1 inserts a chart of the default type (a clustered column chart) on the current sheet.
• F11 inserts a chart of the default type on a new chart sheet.

When a chart is selected, two tabs appear: Chart Design and Format. Three buttons also appear beside the chart:

• Chart Elements (+) turns elements on and off.
• Chart Styles (the paintbrush) applies styles and colors.
• Chart Filters (the funnel) hides or shows series and categories without changing the data.

To change the type of an existing chart, choose Chart Design › Change Chart Type.

6.2 Chart sheets

A chart can live in one of two places:

• An embedded chart floats on a worksheet beside the data.
• A chart sheet is a sheet that holds only the chart, filling the window. Chart sheets have their own tab, like a worksheet.

To move a chart, select it and choose Chart Design › Move Chart:

• New sheet moves the chart to a new chart sheet. Type the sheet's name in the box, such as Sales Chart.
• Object in moves the chart back onto a worksheet. Choose the sheet from the list.

6.3 Changing chart data

6.3.1 How a chart reads its data

A chart turns a range into series and categories:

• A data series is one set of related values, drawn as one color of bars, one line or one set of slices. Series names appear in the legend.
• Categories are the labels along the horizontal axis (or the slices of a pie).

When you select a chart, Excel outlines its source data on the worksheet: series names in one color, category labels in another and values in a third. Drag the corners of these outlines to add or remove rows and columns from the chart.

6.3.2 The Select Data dialog box

Chart Design › Select Data shows and changes everything the chart is built from:

• Chart data range at the top is the whole source range. Select a new range to replace the chart's data.
• Legend Entries (Series) on the left lists each series. Use Add, Edit and Remove, and the arrows to change the order series are drawn in.
• Horizontal (Category) Axis Labels on the right lists the categories. Click Edit to choose a different range of labels.
• Hidden and Empty Cells decides how the chart shows empty cells (as gaps, as zero, or by connecting the line) and whether data in hidden rows and columns is plotted.

6.3.3 Adding a data series

There are three ways to add a series:

1. Select Data › Add. In Edit Series, set Series name to the cell with the series label, and Series values to the range of numbers. Clear the ={1} placeholder before you select the values.
2. Drag the outline. Drag the corner of the values outline on the worksheet to include the new row or column.
3. Copy and paste. Copy the new data, including its label, select the chart and paste (Ctrl+V).

6.3.4 Switching rows and columns

Excel decides on its own whether the rows or the columns of your data become the series, usually making the longer dimension the categories. Chart Design › Switch Row/Column swaps them. For a table of regions (rows) by quarters (columns), one view groups the bars by region with a bar for each quarter, and the switched view groups them by quarter with a bar for each region.

The Switch Row/Column button is also in the Select Data dialog box.

6.4 Chart elements

Chart elements are the parts of a chart beyond the bars or lines themselves:

Element	What it shows
Chart Title	The title above the chart, or overlaid on it
Axis Titles	Labels for the horizontal and vertical axes
Axes	The axes themselves and their labels
Data Labels	Values (or names or percentages) on each bar, point or slice
Data Table	A table of the source data under the chart
Error Bars	Ranges of uncertainty on each point
Gridlines	Major and minor lines behind the plot area
Legend	The key to the series, at the right, top, left or bottom
Lines	Drop lines and high-low lines (line charts only)
Trendline	A fitted line showing the trend of a series
Up/Down Bars	Bars between two line series (line charts only)

6.4.1 Adding and removing elements

Use either:

• Chart Design › Add Chart Element, which lists every element with placement choices, such as Legend › Bottom or Data Labels › Outside End.
• Chart Elements (+) beside the chart. Check or uncheck an element, and point at it and click its arrow for placement choices.

To remove an element, uncheck it, or click it in the chart and press Delete.

6.4.2 Modifying elements

• Edit text in a title by clicking it and typing. To link a title to a cell, click the title, type = in the formula bar, click the cell and press Enter.
• Format any element by double-clicking it, or by selecting it and pressing Ctrl+1. The Format pane opens with that element's fill, border, effects, size and options. For an axis, that includes the minimum and maximum, units and number format. For data labels, it includes what the labels show and where they sit.
• Select hard-to-click elements with the Current Selection list at the left of the Format tab. Choose the element, then Format Selection.

6.5 Layouts and styles

6.5.1 Quick Layout

A layout is a preset arrangement of elements: which titles, labels, legend and gridlines appear, and where. Chart Design › Quick Layout offers about a dozen, named Layout 1, Layout 2 and so on. Point at one to preview it. The choices depend on the chart type.

A layout changes only which elements appear. It doesn't change colors or effects.

6.5.2 Chart styles and colors

A chart style sets the look: colors, effects, backgrounds and fonts. The Chart Styles gallery on the Chart Design tab names them Style 1, Style 2 and so on. Point at a thumbnail to see its name and preview it.

Change Colors, to the left of the gallery, applies a color set: Colorful palettes or Monochromatic shades of one color. The Chart Styles button (paintbrush) beside the chart has the same choices on its Style and Color tabs.

The Format tab works on one selected element at a time. Use its Shape Styles gallery, Shape Fill, Shape Outline and Shape Effects for a bar, the plot area or the chart area, and WordArt Styles for text. Its Size group sets an exact height and width.

6.6 Alt text

Alternative text (alt text) describes a chart for people who can't see it. Screen readers read it aloud. The Accessibility Checker (chapter 3) reports any chart without alt text as an error.

To add alt text:

1. Right-click the chart's border (the chart area) and choose View Alt Text (or Edit Alt Text), or choose Format › Alt Text.
2. In the Alt Text pane, type a description in the box.
3. Close the pane.

The pane also has Mark as decorative, for images that add nothing a reader needs. Don't use it for charts, because charts carry information.

6.6.1 Writing useful alt text

Good alt text says what kind of chart it is and what it shows, especially the main point:

• Weak: "Chart 1."
• Better: "Column chart of sales by region."
• Best: "Column chart of 2027 sales by region. The West region leads with $1.2 million; the South is lowest at $640,000."

Keep it to a sentence or two. For a complicated chart, summarize the trend and point readers to the source data.

6.7 Practice