Create charts and objects

  • 7/6/2017

In this sample chapter from MOS 2016 Study Guide for Microsoft Excel, learn about exam objectives related to presenting data in charts and enhancing worksheets through images, business diagrams, and text boxes.

Objective 5.1: Create charts

Charts (also referred to as graphs) are created by plotting data points onto a two-dimensional or three-dimensional axis to assist in data analysis and are therefore a common component of certain types of workbooks. Presenting data in the form of a chart can make it easy to identify trends and relationships that might not be obvious from the data itself.

Different types of charts are best suited for different types of data. The following table shows the available chart types and the data they are particularly useful for plotting.

Chart type

Typically used to show

Variations

Area

Multiple data series as cumulative layers showing change over time

Two-dimensional or three-dimensional

Independent or stacked data series

Bar

Variations in value over time or the comparative values of several items at a single point in time

Two-dimensional or three-dimensional

Stacked or clustered bars

Absolute or proportional values

Box & Whisker

Distribution of data within a range, including mean values, quartiles, and outliers

 

Column

Variations in value over time or comparisons

Two-dimensional or three-dimensional

Stacked or clustered columns

Absolute or proportional values

Funnel

Categorized numeric data such as sales or expenses

 

Histogram

Frequency of occurrence of values within a data set

Optional Pareto chart includes additive contributions

Line

Multiple data trends over evenly spaced intervals

Two-dimensional or three-dimensional

Independent or stacked lines

Absolute or proportional values

Can include markers

Pie

Percentages assigned to different components of a single item (nonnegative, nonzero, no morethan seven values)

Two-dimensional or three-dimensional

Pie or doughnut shape

Secondary pie or bar subset

Radar

Percentages assigned to different components of an item, radiating from a center point

Can include markers and fills

Stock

High, low, and closing prices of stock market activity

Can include opening price and volume traded

Sunburst

Comparisons of multilevel hierarchical data

 

Surface

Trends in values across two different dimensions in a continuous curve, such as a topographic map

Two-dimensional or three-dimensional

Contour or surface area

Treemap

Comparisons of multilevel hierarchical data

 

X Y (Scatter)

Relationships between sets of values

Data points as markers or bubbles

Optional trendlines

Waterfall

The effect of positive and negative contributions on financial data

Optional connector lines

You can also create combination charts that overlay different data charts in one space.

To plot data as a chart, all you have to do is select the data and specify the chart type. You can select any type of chart from the Charts group on the Insert tab. You can also find recommendations based on the selected content either on the Charts tab of the Quick Analysis tool or on the Recommended Charts tab of the Insert Chart dialog box.

g05se01.jpg

Click to view larger image

Excel displays the active data in each of the recommended chart thumbnails

Before you select the data that you want to present as a chart, ensure that the data is correctly set up for the type of chart you want to create (for example, you must set up hierarchical categories differently for a box & whisker, sunburst, or treemap chart than for a bar or column chart). Select only the data you want to appear in the chart. If the data is not in a contiguous range of rows or columns, either rearrange the data or hold down the Ctrl key while you select noncontiguous ranges.

A chart is linked to its worksheet data, so any changes you make to the plotted data are immediately reflected in the chart. If you want to add or delete values in a data series or add or remove an entire series, you need to increase or decrease the range of the plotted data in the worksheet.

g05se02.jpg

Click to view larger image

Change a chart by including more or less data

Sometimes a chart does not produce the results you expect because the data series are plotted against the wrong axes; that is, Excel is plotting the data by row when it should be plotting by column, or vice versa. You can quickly switch the rows and columns to see whether that produces the desired effect. You can preview the effect of switching axes in the Change Chart Type dialog box.

You can present a different view of the data in a chart by switching the data series and categories across the axis.

g05se03.jpg

Click to view larger image

Change a chart by switching the data series and categories

You can swap data across the axis from the Change Chart Type dialog box or you can more precisely control the chart content from the Select Data Source dialog box.

g05se04.jpg

Choosing the data to include in a chart

To plot data as a chart on the worksheet

  1. On the worksheet, select the data that you want to plot in the chart.

  2. Do any of the following:

    • On the Insert tab, in the Charts group, click the general chart type you want, and then on the menu, click the specific chart you want to create.

    • On the Insert tab, in the Charts group, click Recommended Charts. Preview the recommended charts by clicking the thumbnails in the left pane. Then click the chart type you want, and click OK.

    • Click the Quick Analysis button that appears in the lower-right corner of the selection, click Charts, and then click the chart type you want to create.

      g05se05.jpg

      Click to view larger image

      The Quick Analysis menu provides quick access to data transformation options

To modify the data points in a chart

  • In the linked Excel worksheet, change the values within the chart data.

To select the chart data on the linked worksheet

  • Click the chart area or plot area.

    g05se06.jpg

    Click to view larger image

    Selecting data on the chart selects the corresponding data on the worksheet

To change the range of plotted data in a selected chart

  • In the linked Excel worksheet, drag the corner handles of the series selectors until they enclose the series you want to plot.

Or

  1. Do either of the following:

    • On the Design tool tab, in the Data group, click Select Data.

    • Right-click the chart area or plot area, and then click Select Data.

  2. In the Select Data Source dialog box, do any of the following, and then click OK:

    • Click the worksheet icon at the right end of the Chart data range box, and then drag to select the full range of data you want to have available.

    • In the Legend Entries (Series) list and Horizontal (Category) Axis Labels boxes, select the check boxes of the rows and columns of data you want to plot.

To plot additional data series in a selected chart

  1. Do either of the following:

    • On the Design tool tab, in the Data group, click Select Data.

    • Right-click the chart area or plot area, and then click Select Data.

  2. In the Select Data Source dialog box, at the top of the Legend Entries (Series) list, click Add.

  3. In the Edit Series dialog box, do either of the following:

    • Enter the additional series in the Series name box.

    • Click in the Series name box and then drag in the worksheet to select the additional series.

  4. If necessary, enter or select the series values. Then click OK.

  5. In the Select Data Source dialog box, click OK.

To switch the display of a data series in a selected chart between the series axis and the category axis

  • On the Design tool tab, in the Data group, click the Switch Row/Column button.

Or

  1. Do either of the following:

    • On the Design tool tab, in the Data group, click Select Data.

    • Right-click the chart area or plot area, and then click Select Data.

  2. In the Select Data Source dialog box, click Switch Row/Column, and then click OK.