Most valuable professional
  • Excel Book Excerpt

Excel Select Anything on a Chart to Format

This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.

Select Anything on a Chart to Format

Problem: I need to further customize a chart. Why aren’t there any Layout tab buttons for series? How can I change one data point?

Strategy: I certainly understand the frustration. The Layout tab in Excel offers fifteen dropdowns, but they missed the most important dropdown; the one for series.

You can choose each series from the Current Selection dropdown on the left side of the Layout (and Format) tabs. But the dropdown does not offer each individual point or data label.

You can’t format data labels until you add them with the Data Label dropdown on the Layout tab.

In Excel 2003 and Excel 2010, you could format something by double-clicking, but in Excel 2007, only right-click would work.

To select a series, click on the series. If the series is too small to click, then use the Current Selection dropdown on the Layout tab.

To select an individual data point, you have to first select the series then do a second single-click on the data point. You can never choose data points using the Current Selection dropdown.

To add data labels to a chart, make sure that no series is selected. Open the Data Labels dropdown on the Layout tab and choose any item. You can even choose More Options and immediately click OK. The alternative method is to right-click one series and choose Add Data Labels, but this will not add data labels to the other series.

Once you have data labels, click on one label to select all of the labels for that series. Once data labels are selected, click again on one data label to select the label for an individual point.

Additional Details: If you click in a chart and start pressing the right arrow, you will cycle through every element that can be formatted. Watch the Current Selection dropdown to see what you are selecting.

In the chart below, Excel cycles through 46 separate items. In contrast, the Current Selection dropdown only offers 14 items. What is the difference between 46 and 14? The arrow keys will select each individual entry in the legend, each data point, each data label, even the “Millions” tag next to the axis.


Figure 1017 Using arrow keys to cycle will include each legend entry.

Once you have selected something, use Ctrl+One, or the Format Selection button to access the Format dialog box.

Everything on a chart can be customized. You can use any of these methods:

  • Right-click the chart element and choose Format
  • Double-click the chart element in all but Excel 2007
  • Click the chart element to select it and then press Ctrl+One
  • Use the arrow keys to select an element and press Ctrl+One.
  • Choose the chart element from the Current Selection dropdown on either the Layout tab or Format tab and then click the Format Selection button in the same group

There is no one “right” method of these five. There are times when the only way to format something is by clicking it and other times when something is nearly invisible and the only way you can format it is from the Current Selection dropdown. So you need to be ready to use whichever method will get you to the correct element.

Gotcha: None of the methods described above will let you access an element that is not yet on the chart. Use the dropdowns on the Layout tab to add elements such as Chart Title, Axis Title, Data Table, Error Bars, Drop Lines, Up/Down Bars, Trendlines.

Say that you want to change the color of just the May central region column. The first click on the central region column will select all of the central region columns. A second single-click will select just the one column. You right-click to access Format Data Point

<-Previous Topic                    Next Topic->

For more resources for Microsoft Excel: