Telling a Story with Charts: Excel 2016: Charts and Graphics

Telling a Story with Charts

A chart  is a visual representation of numeric values. The spreadsheet program, Excel, is an excellent number cruncher. It also has a powerful and very customizable charting function. The basic function of any chart is to convey a message. In most cases, the underlying message of most charts involves some sort of comparison. A chart can:

  • Compare an item to another item: For example, a chart can compare the price of peanut butter in three different grocery stores.
  • Compare data over time: For example, a chart can display a fluctuating stock price over the course of a year.
  • Make relative comparisons: For example, a chart can display the relative percentage of votes each candidate received in an election.
  • Compare data relationships: For example, a chart can show the relationship between advertising expenditures for a product and its increase or decrease in sales.

Charts come in many forms, varieties, and styles. This workshop will focus on some common chart types: the pie chart, the line chart, the column chart, and the bar chart. Brief definitions of these chart types follow:

  • Column charts depict the numeric relationship between two elements.
  • Pie charts show the relative proportion of an element to a whole.
  • Line charts track a continuous data series, usually across time.
  • Bar charts are used when there are lengthy category labels. A bar chart is essentially a column chart that has been rotated 90 degrees clockwise.

We will be creating charts for the Continuous Education Department in the fictional University of the Midwest. This department offers a series of computer workshops for members of the University and the local community. We will create basic charts as well as explore some chart formatting options.

About These Materials for MacOS Users

These materials are written with the presumption that you have modified a particular MacOS system setting for the Keyboard, which will enable many of the same keyboard shortcut combinations used by Windows to behave as expected on your MacOS computer. Information regarding this simple change is available at:

https://ittrainingtips.iu.edu/p=?10227

If you have not made this change, not all instructions in these materials may behave as expected on your computer. If this happens, you may have to perform some simple substitutions. The following table lists common substitutions between the Windows and MacOS operating systems:

On Windows

On MacOS

Control key

Command key

Alt key

Option key

Enter

Return

Right-Click

Control key+Click

Opening an Existing Worksheet

First we will open Excel and look at an example column chart.

1. Launch Excel.

The application opens.

To better understand the common elements of charts, we will open an existing worksheet that already contains a chart. We'll open the file ChartData.xlsx, which will illustrate many of the basic principles of how charts are presented in Excel.

2. To begin to open a workbook, in the left pane,

Click Open Other Workbooks

NOTE for MacOS Users: To begin to open the file, on the menu bar, in the File menu, Click Open.

3. To browse for the file,

ClickBrowse button

NOTE for MacOS Users: To browse for the file, Clickthe On my Mac button.

4. To open the desired subfolder,

navigate into the epclass folder on your Desktop,

Double-Click the ExcelCharts folder

5. To select the appropriate file,

Double-Click ChartData.xlsx

The file opens with a chart example.

Several elements of a chart are identified below:

chart with callouts identifying the various elements

Elements of a Chart

Almost all charts contain common elements; namely a title, axes, plot area, labels, and a legend.

Chart Title

A main chart title will summarize the data in the chart, and often highlight the key idea being presented in the chart. A useful analogy is the newspaper headline; a good chart title will tell the full story but also will cause enough interest to make someone want to look at the chart more closely. In the example chart, the title, "Domestic vs. International Box Office Receipts" makes the core comparison of the chart clear.

Axes

Many charts have two or more axes. Of the charts we will be looking at today, both line and column/bar charts contain axes; pie charts do not.

The horizontal axis of a chart is often called the category axis,or x axis. This axis represents the category for each data point. In the example, the category axis contains movie names.

The vertical axis of a chart is often called the value axis or y axis. This axis displays a series of values in a scale appropriate to the data of the chart. In our example, the value axis displays values from $0 to $2,200,000,000. Note that there is a value at every $200,000,000. This enables a reader more accurately to interpret each data element.

Plot Area

All charts have a chart area, which is the entire background of the chart. The plot area shows the actual chart and may or may not have a different background color.

Axis and Data Labels

A chart can have several different labels, depending upon the chart type. In our example, the horizontal axis title identifies the movies, whereas the vertical axis title identifies the movie sales.

Data labels can help a chart viewer understand what he or she is seeing. Actual numerical values are displayed for each data point.

Legend

Finally, many charts have a legend that identifies the various series in a chart. In the example, the legend (located to the right of the chart) identifies the blue data series as representing the domestic box office receipts and the red data series as representing the international box office receipts. In many cases, a legend is a vital and necessary part of a chart.

Understanding How Excel Handles Charts

A key point to keep in mind is that charts are dynamic. The charts are dynamically linked to their source data. This means that when the data in the worksheet is altered, the chart is immediately updated to reflect that change.

Let's examine this feature by altering the international box office receipts for the film Minions in our example chart.

1. If necessary, scroll down so that the data table is visible on the screen.

2. To select the appropriate cell,

Click cell C31

Let's say that the Minions film made over 8 hundred million dollars internationally, and change the data to reflect that.

3. To indicate this change, in the formula bar,

Press & Drag 6, type: 8 Enter

The chart updates to reflect the revised data.

Embedded Charts

An embedded chart sits or "floats" on top of the worksheet. The chart shown here is an embedded chart. We can move or resize an embedded chart, change its proportions, and perform many other operations. The Ribbon provides many tools for working with charts. When printing the chart, we can also print the related data next to the chart.

Moving and Resizing a Chart

An embedded chart can be moved and resized easily with the mouse. When a chart is selected, additional chart tools will be available to use from the Ribbon.

1. To select the chart,

Click the blank chart area

The Chart Tools contextual tabs are displayed on the Ribbon. In Excel 2016 for Windows, you will also see three buttons immediately to the right of the chart that will allow you to add, remove, or change Chart Elements, set a style and color scheme, or apply chart filters. In Excel 2016 for Mac, the controls to add, remove, or change Chart Elements, and set a style and color scheme are located on the Ribbon. Applying chart filters is not available in Excel 2016 for Mac.

First we will practice moving the chart. When the chart is selected, the cursor will change to a four-sided arrow when we move it over the chart.

2. To prepare to move the chart,

Point to a blank area in the chart area

The Tool Tip "Chart Area" will appear when your cursor is located properly.

You will see the cursor change to a four-sided arrow: cursor with shape of four sided arrow coming from the tip

3. To move the chart,

Press & Drag the chart

When the chart is selected, there are eight handles that can be used to resize the chart. These handles appear at the chart's corners and midway between the corners.

NOTE: To move a chart from one worksheet to another, you may use standard cut and paste techniques, or use the Move Chart tool on the Ribbon.

Next we will practice resizing the chart.

4. To resize the chart,

Point to a corner handle until a double-sided arrow appears,

Press & Drag the handle

The chart is resized.

Chart Sheets

A chart can also be created to occupy an entire sheet. Using a chart sheet is advantageous when printing a chart on a page by itself. Locating particular charts may be easier because you can change the name of each the sheet tab to provide meaningful descriptions of the chart it contains.