Translator Tools / Articles / Navigating in uncharted waters of Microsoft Excel charts

Working with Excel charts

Depending on the location in an Excel spreadsheet, charts are subdivided into chart objects and chart sheets. A chart object is a chart which is placed on an existing sheet along with other data. A chart sheet is a standalone sheet occupied by the chart only, with nothing else in it.

When you select the Chart Area (by clicking empty space near chart's edge), or an individual series, Excel automatically outlines the respective cells with different colors (see Figure 3 above).

A word on formulas

All charts rely on formulas to refer to the data that exists somewhere else on the same or different sheet. These formulas usually refer to several types of data: 1) values used to plot individual series; 2) values used to show along the category axis; etc. The following types of formula may be used:

=Sheet1!$A$1. This formula indicates a cell address. If this type of formula is used, the cell's value will be used on the chart. The first part before the exclamation sign (shown in blue) refers to the name of the sheet where the cell is located, and the second part (shown in dark red) refers to the address of a cell, e.g. $A$1 means A1, etc. In a chart, this formula is usually used for series names used to create the legend.
="New York temp.". This is a text formula. It specifies the exact text which must be used. In a chart, it is usually used for series names used to create the legend. If you want to insert double quotes, you need to enter them twice, e.g.
="Temperature in ""Big Apple""".
=Sheet1!$A$1:$A$10. This formula refers to a cell range, e.g. $A$1:$A$10 means a range with the top left corner in cell A1 and the bottom right corner in cell A10. It is used for chart's data ranges, category names, and some other things.
={"Jan", "Feb", "Mar" ,"Apr"} (may also be shown as ={"Jan"\"Feb"\"Mar"\"Apr"}). This is an array formula. It specifies a number of text values to show on the chart. This type of formula is used quite rarely, usually for category names.

Different versions of Microsoft Excel provide different user interfaces for working with charts.

Excel 2003 and earlier:

Click anywhere on the chart. A new menu item, Chart, will appear on the menu bar, and a toolbar called 'Chart' will become visible. You can use these controls to get easy access to various chart components.
To modify chart components (e.g. resize, move, change text, format), you can click them with the mouse or select them from the drop-down list on the Chart toolbar.
You can change the size of the Plot Area, Legend, or Chart Area to make the translation fit.
The quickest way to edit Chart Title, Category Axis Title, Value Axis Title, and secondary axis titles (if used) is to select Chart -> Chart Options... and click the Titles tab where you can modify each title.
To edit all the data that goes into the chart, select Chart -> Source Data... and check the Data Range parameter on the Data Range tab.
To edit series names used to make the chart legend, select Chart -> Source Data... and click the Series tab. Each series will be shown in the Series list. Select the appropriate series and check the Name field.
Another way to edit series names is to select the series with the mouse or from the drop-down list on the Chart toolbar, and edit the formula in the formula bar. See the next section for more detail.
To edit labels shown along the category axis (if shown), select Chart -> Source Data... and click the Series tab. Use the field labelled 'Category (X) axis labels' at the bottom of the dialogue to see where the data is taken from or modify the formula (if it is an array formula).

Excel 2007 and later:

Click anywhere on the chart. You will see a new group of tabs called 'Chart Tools' appear at the top of the ribbon, with three tabs underneath: Design, Layout and Format.
To modify chart components (e.g. resize, move, change text, format), you can click them with the mouse or select them from the drop-down list in the Current Selection group on Layout and Format tabs.
You can change the size of the Plot Area, Legend, or Chart Area to make the translation fit.
To edit Chart Title, Category Axis Title, Value Axis Title, and secondary axis titles (if used), select them with the mouse, right-click and select Edit Text menu item (you can also left-click inside the title).
To edit all the data that goes into the chart, right-click the chart and choose Select Data from the dropdown menu. You will see 'Select Data Source' dialogue. An alternative way is to click Select Data button in the Data group on the Design tab.
To edit series names in the chart legend, right-click the chart and choose Select Data from the dropdown menu, or click Select Data button in the Data group on the Design tab. In the Select Data Source dialogue, select each individual series in the list on the left side, click Edit, and then edit the Series Name field in the Edit Series dialogue.
Another way to edit series names is to select the series with the mouse or from the dropdown list in the Current Selection group on Layout and Format tabs, and then edit the formula in the formula bar. See the next section for more detail.
To edit labels shown along the category axis (if shown), right-click the chart and choose Select Data from the dropdown menu, or click Select Data button in the Data group on the Design tab. In the Select Data Source dialogue, click Edit button on the right side of the dialogue to show Axis Labels dialogue. Use the field on this dialogue to see where the data is taken from or modify the formula (if it is an array formula).

Links & resources: