Excel Charting Training Course

Excel Charting

Unit: Working with Charts

After creating a chart, you may need to rearrange the data or move the chart to a new location.

Moving a Chart Within the Current Worksheet

Excel had an annoying habit of locating new charts near the bottom of your data. With a large spreadsheet, you may need to move the chart to the proper location thousands of rows away. Some methods are faster than others.

There are several ways to move a chart within the current worksheet.

Drag and Drop Method

When a chart is selected, a border appears around the chart. Eight resizing handles appear in the border. To move a chart, you click the border but avoid the resizing handles. You can then drag the chart to a new location.

Because it is somewhat difficult to click on a thin chart border, you might try clicking inside the chart in order to drag the chart to a new location. This approach works as long as you can click on some whitespace between the plot area and the chart border.

You can easily drag a chart anywhere in the visible window. If you accidentally drop the chart just a pixel outside of the visible window, the chart boomerangs back to its original position.

Cut and Paste Method

Instead of dragging and dropping, the fastest way to relocate a chart might be to cut and paste it. You follow these steps to quickly move a chart within the current worksheet:

1. Select a chart.
2. Press Ctrl+X to cut the chart from the worksheet.
3. Press F5 to display the Go To dialog.
4. Type the address of the cell that you want to contain the top-left corner of the chart and click OK.
5. Press Ctrl+V to paste the chart in the new location.

Reversing the Series and Categories of a Chart

Excel follows strict rules in deciding whether rows should be series or categories. Luckily, you can reverse this decision with a single button click.

If Excel chooses the wrong orientation for the data in a chart, you can click the Switch Row/Column icon in the Design ribbon.

You will see that the years have changed from being category labels to being series labels. The products have changed from being series labels to being category labels.

To reverse the orientation of data in a chart, you click Switch Row/Column.

If your data has more columns of data than rows, the headings in the first row become category labels.

The eight columns of monthly data become category labels, and the three rows become series.

If your data has more rows than columns, the headings in the first column become category labels.

Changing the Data Sequence by Using Select Data

The Select Data icon on the Design ribbon allows you to change the rows and columns of your dataset, and it also allows you to resequence the order of the series. When you click this icon, the Select Data Source dialog appears.

Buttons in the Legend Entries side of the Select Data Source dialog allow you to add new series, edit a series, remove a series, or change the sequence of a series. A single Edit button on the right side of the dialog allows you to edit the range used for category labels.

For example, use the Select Data Source dialog, as follows:

1. Select the chart and choose Design, Select Data. The Select Data Source dialog displays.
2. Click Switch Rows/Columns to move the city names to the left side of the dialog.
3. Click Alice Springs on the left side and then the up arrow button until Alice Springs is the first series.
4. Click Broome and then the up arrow button until Broome is the second series.
5. Continue re-sequencing the cities until they are in the desired order.
6. On the Horizontal (Category) Axis Labels side of the dialog, click the Edit button. The data initially points to B1:E1. Change the address in the Axis Labels dialog box to an array by entering the four new labels inside curly braces: ={“Summer”,“Fall”,“Winter”,“Spring”}. Click OK to close the Axis Labels dialog box and return to the Select Data Source dialog box.

Leaving the Top-Left Cell Blank

In the past, Excel would tell you to always leave the top-left cell of your dataset blank before creating a chart.

This old guideline was that if your series or category labels contained either dates or numeric labels, you should leave the top-left cell blank. Now, if your labels contain values formatted as dates, there is no need to leave the top-left cell blank. In addition, if your data has been converted to a table, using the Format as Table icon on the Home ribbon, it is impossible to leave the top-left cell blank.

In a few instances, your results improve if the top-left cell is blank.

Assume there are years in cells B1:D1 of a worksheet, and they are numeric. Consider A1:D1, you have text in column A, followed by three numbers in B:D. This is remarkably similar to the data in A2:D4. You have text in column A, followed by numbers in B:D. If you create a chart from this dataset, Excel assumes that you do not have series labels and assumes that A1:A4 represent four category labels. This erroneously produces the an incorrect chart.

Cells A11:D14 in the figure contain exactly the same data as A1:D4, except the Region label was cleared from A11. In this case, Excel correctly sees three series and three categories.

Moving a Chart to a Different Sheet

In Excel, charts always start out as objects embedded in a worksheet. However, you might want to display a chart on its own full-page chart sheet.

There are two options for moving a chart:

• Choose the Move Chart icon at the right edge of the Design ribbon.
• Right-click any whitespace near the border of the chart and choose Move Chart.

Either way, the Move Chart dialog appears, offering the options New Sheet and Object In. The Object In drop-down lists all the worksheets in the current workbook. The New Sheet option allows you to specify a name for a new sheet.

Note: When you choose to move a chart to a new sheet, the chart is located on a special sheet called a chart sheet. This sheet holds one chart that can be printed to fill a sheet of paper. You cannot have additional cells or formulas on a chart sheet.

Customising Charts Using the Design Ribbon

The Design ribbon allows you to quickly customize a chart with just a couple clicks. For example, the Chart Styles gallery allows you to change the colour scheme and effects for the entire chart.

The Chart Layouts gallery offers professionally designed combinations of chart elements.

Choosing a Chart Layout

Depending on the chart type you have chosen, the Chart Layouts gallery offers 4 to 12 built-in combinations of chart elements. When you choose a new chart layout from the gallery, you get a predefined combination of title, layout, gridlines, and so on.

The Chart Layouts gallery offers up to a dozen predefined layouts for the current chart type.

Choosing a Colour Scheme

The Design ribbon is also home to the Chart Styles gallery, which offers 48 variations of colour and effects. The gallery has columns for each of the six accent colours, monochrome, and mixed colours.

As you proceed down the first four rows of the gallery, each style has a bit more extreme effect applied to the bars. The styles in row 2 have a white border around the bars.

The styles in row 3 have a dark gradient. The styles in row 4 have a glass-like surface on the bars. The styles in row 5 have a dark border around the bars, combined with a light tint in the plot area. The final row has a dark background that is suitable, for example, if you are using the chart in a dark-themed PowerPoint presentation.

As shown in the figure above, you can choose from eight colour schemes when you use the Chart Styles gallery.

Modifying a Colour Theme

You can change to any of the 20 themes or create your own theme in order to easily access new colours and effects.

On the Page Layout ribbon, you can click the Themes drop-down to choose from the 20 built-in themes. If you choose a new theme from the Themes drop-down, Excel applies a new colour and set of effects to all of the charts in the current workbook. If you want to change only the colours or effects, you can use the Colours or Effects drop-down in the Themes group.