Chapter 6. Working with Charts
Charts are graphical representations of data that allow you to visualize and communicate your data in a more meaningful way than with simple numbers in tables. In Excel, you not only have the capability to easily chart your data, but you can also quickly change the appearance of charts with a bevy of options. For instance, you can change titles, customize the legend, set axis points, add category names, and more.
As mentioned in Chapter 1, “Working with the Excel User Interface,” certain Excel objects activate contextual tabs in Excel 2010. These contextual tabs are special types of tabs that appear only when a particular object is selected. When you work with Excel charts, you see the Chart Tools contextual tab. This tab contains sub-tabs that expose the various commands you would use to design, layout, and format a chart.
In this chapter, you will discover just how easy it is to create charts in Excel.
The Chart Tools Contextual Tab
Creating a Chart
Interpreting numeric data by looking at numbers in a table can be difficult. Using data to create charts can help people visualize the data’s significance. For example, you might not have noticed in a spreadsheet that the same month of every year has low sales figures, but it becomes obvious when you make a chart from the data in that spreadsheet. The chart’s visual nature also helps others review your data without the need to review every single number.
Select the cells you want to include in your chart.
Click the Create Chart dialog launcher at the bottom right-hand corner of the Charts group on the Ribbon’s Insert tab.
Click on the Chart Type icon for the chart you want to create.
Click the OK button to create your chart.
Note: Creating Quick Charts
You can quickly create a chart by selecting your data range and pressing the F11 key on your keyboard. When you do this, Excel automatically creates a chart and puts it on its own tab.
On the Design tab, you see the Chart Layouts group. There, you can click the Quick Layout command to choose a chart layout that includes a chart title.
Click the Chart Title box and then type the name for the chart.
Click the Move Chart option button on the Design tab.
Enter the name you want for the new worksheet and then click OK. In this case, Excel moves the chart to a new worksheet named SalesData.
Note: Moving a Chart
Regardless of your selection in step 6, you can always move your chart to another (perhaps new) worksheet. Simply right-click a blank area in the chart and click Move Chart. To place the chart in a new worksheet, click the New sheet option and type a name for the new sheet. To move it to a different worksheet, click the Object in option button and select the worksheet from the drop-down list. Click OK, and Excel moves the chart.
Changing the Chart Type
Charting is one of those skills you learn by doing. At first, you might not even know what type of chart you want to create until you see it. You can always select a different chart type for a chart so that it better represents the data.
Click the Change Chart Type command on the Design tab.
Select a new chart type and chart subtype in the Chart Type dialog box.
Click OK.
The updated chart type appears in your chart.
Note: Using the Default Chart Type
The default chart type is a column chart. To make another chart type the default, select it in the Change Chart Type dialog box and then click the Set As Default Chart button.
Altering the Source Data Range
Suppose you need to point your chart to a completely different data table. That is, the data that feeds your chart will be coming from a different location. In this scenario, you can reconfigure your chart to change its source data location.
Go to the Design tab and select the Select Data command.
Click directly in your worksheet and select the new data range. The Data range area in the Source Data dialog box automatically updates.
Click OK.
The updated data range appears in your chart.
Note: Locating Incorrect Data
If you notice that one of the data points in your chart is way off the scale, this is a good sign that you might have entered data into your worksheet incorrectly. If this is the case, edit the worksheet data and the chart updates automatically. (See the task “Changing the Chart Source Data” later in this chapter for help altering the original data.
Altering Chart Options
Changing your chart options is even easier than creating the chart in the first place. You can add or edit chart titles, alter your axes, add or remove gridlines, move or delete your legend, add or remove data labels, and even show the data table containing your original data. All of this can be managed from the Layout tab.
Click the Chart Title button and choose More Title Options.
In the Fill section, add a background color by checking Solid Fill and then click Close.
Click the Axes button and then select Primary Vertical Axis, Show Axis in Thousands to change the format of the vertical axis.
Click the Gridlines button and then select Primary Horizontal Gridlines, Major & Minor Gridlines to make both visible on the chart.
Note: Formatting the Axes Gridlines
To change the pattern and scale of the gridlines, double-click the gridline itself and then use the Format Gridlines dialog box to make your selections. Click OK when finished.
Click the Legend tab, select whether you want to show a legend, and review how altering the Placement options affects your chart.
Click the Data Labels tab and see how the addition of label descriptions affects your chart.
Click the Data Table tab and select whether you want to show the data table (with or without the legend keys).
Review how your chart has changed.
Tip: Adding Data Tables
If you want to show a data table along with the chart, go to the Layout tab and click Data Table, Show Data Table.
Note: Printing Charts
If you want to print just your chart (as opposed to the entire worksheet) select the chart and click the Print Selected Chart option under the Print pane of the Backstage view.
Formatting the Plot Area
The plot area consists of a border and the location of the data points in your chart. You can alter the style, color, and weight of the border. You can also alter the color of the plot area.
Go to the Format tab and click on the Chart Elements drop-down, then choose Plot Area from the drop-down.
Click on the Shape Fill button and choose a color to change the background color of the plot area.
Observe how your chart has changed.
Tip: Determining which Area You’re In
If you are unsure whether you are in the chart area or the plot area, click directly on the chart. Look at the Chart Elements drop-down found in the upper-left corner of the Format tab. This drop-down shows the name of the chart element that is currently active. You can also use this nifty drop-down to quickly switch from one element to another.
Formatting the Chart Area
The chart area consists of a border, the background, and all the chart fonts. You can alter the style, color, and weight of the border. You can also alter the color of the background. You can also change all the fonts and font styles in the chart.
Go to the Format tab and click on the Chart Elements drop-down, then choose Chart Area from the drop-down.
Click on the Shape Fill button and then choose a color to change the background color of the plot area.
Observe how your chart has changed.
Tip: Formatting from the Home Tab
You can change the font and color of most chart elements by using the formatting commands on the Home tab. You can simply select the chart element and use the Home tab to easily change the font, alignment, color, and other format properties. This makes quick work of any formatting tasks you may need to do on your charts.
Formatting the Axis Scale
Excel automatically establishes the axis increments according to the maximum amount on the chart. Usually this will suffice, but if you want to show more detail about actual numbers, it can be convenient to alter your value axis.
Go to the Format tab and click on the Chart Elements drop-down, then choose Vertical (Value) Axis from the drop-down.
Click the Scale tab of the Format Axis dialog box and type the changes to the axis scale increments—for example, decrease the value in the Major unit field.
Click Close.
Review how your chart has changed.
Note: Number and Alignment
To change the number format, click the Number tab and select the numeric format you want to use. To change the alignment, click the Alignment tab and select a rotation for the axes.
Altering the Original Data
A chart is linked to the worksheet data, so when you make a change in the worksheet, the chart is updated. If you want to change a value in the worksheet, edit it as you do normally. The chart is instantly updated to reflect the change. If you delete data in the worksheet, the matching data series is deleted in the chart.
Select the worksheet tab or range that contains the charted data.
Click a cell that you want to alter or need to update.
Type in the new data and press the Enter key.
Go back to the chart and see how the edited data point has changed your chart.
Tip: Saving Changes
When working with charts, you want to make sure you save your changes often. You wouldn’t want to lose any changes you made in case your network goes down or your computer freezes.
Adding Data to Charts
Suppose you want to expand your chart to include additional data. If so, you need to place the data on your original worksheet and indicate to Excel that you want it included in your chart.
After adding any new data you want to include in your chart to the original data range, click directly on your chart to see what data is currently referenced in the chart.
Click and drag the blue chart data line to include the newly added data.
Use the blue handle to drag the chart data line in the new location.
The chart automatically updates to include the new data.
Note: Excluding Chart Data
You can also click and drag the blue chart data line to exclude data in your chart. Simply drag the blue line so that the data you want to exclude is no longer contained within the chart.
Adding a Legend
A legend helps a reader make sense of all the data points and colors shown in a chart. You typically wouldn’t need a legend if you’re plotting only one data series. However, if you are plotting two or more data series in one chart, it’s definitely a best practice to have a legend. Considering the fact that you will probably add data to your chart, going from one data series to many, it’s helpful to know how to add a legend after your chart has been created.
Click anywhere on your chart.
Go to the Layout tab and Click the Legend command.
Select the Show Legend at Bottom option.
Note that your chart now has a Legend.
Tip: Formatting Legends
Right-click the legend and choose Format Legend from the shortcut menu. From the dialog box that appears, you can alter the patterns, fonts, and even the placement of the legend.
