# Lesson 20

Preview

Chart Basics

Charts

Worksheets and Data
A worksheet also has an invisible drawing layer, which holds tables, charts, images, and diagrams. Charts can be inserted based on data in worksheet cells. In addition, a workbook can store chart sheets; a chart sheet displays a single chart and is also accessible by clicking tab.

Table Data and Charts
In order to create a chart you must have some data in a range of cells or in a table. In most cases, the data that a chart uses comes from data on that worksheet but a chart can also be created from data stored on a different worksheet or even in a different workbook.

Data Series Concept
Data series is a term used to describe data that is related together on a table/chart. For example in the figures below, we see two graphics. The one on the right (FIGURE 2) is a table that shows two sectors, Northern and Southern. The Northern Sector is a data series that contains all of the numbers for the Northern Sector and these numbers are related together. We can see how these numbers are related in the chart in FIGURE 1. We see that the chart legend at the bottom of the chart indicates the Northern Sector is a blue color. All blue columns are related together, in that all blue columns in the chart represent the Northern Sector. The second data series, Southern Sector shows a column of numbers in FIGURE 2. They are also related together. We see this relationship again in the FIGURE 1 chart, with all of the orange columns representing the Southern Sector.

Chart Anatomy – Elements of a Chart
A chart contains various elements that comprise the framework for the data being shown. In FIGURE 3 below, we have a typical column chart, with two data series. Data series 1 is the blue columns that represent the Northern Sector and data series 2 is the orange columns that represent the Southern Sector. Data series were previously discussed above.

The chart area is basically the entirety of the chart, including the chart title, plot area, and legend. Beginning from the top of the chart, we see the chart title, “Northern Sector vs. Southern Sector Sales”. You may choose to not even have a chart title if you believe it does not increase chart readability.  Below the chart title is the plot area, which includes all of the data being charted, from the top of the vertical axis down to the horizontal axis.

The vertical Y axis is broken up into much smaller sections numerically since this axis is the axis that scales and measures the sales volume in numerical amounts. We see that we have major units (10,000) increments and minor units (2,000) which are the small tick marks.

The horizontal X axis is broken up into various sections including the months January through June. Below the horizontal axis is the legend, which is a color coded so we know what data is assigned to a specific color. All of the elements mentioned are customizable.

Constants and Variables
In the field of Information Technology, there are 2 terms we need to be aware of: constants and variables. Constants are values that don’t change, and there are variables, which are values that do change.

In FIGURE 3 above, notice that on the Horizontal Axis, we have the months of the year listed, Jan through Jun. The Horizontal Axis is considered the independent variable. This means the data stands on its own and is not dependent on anything.
The Vertical Axis does not have name labels; the labels are numerical, with each major number being shown as an increment of 10,000. This Vertical Axis is considered the dependent variable, meaning that the values are dependent on the other axis, the Horizontal Axis.

Although there are three data series in the data table in FIGURE 4, only data series 1 and 2 are shown in the chart above. The third data series (Add On Sales) will be added later in the exercises as part of your learning.

Chart Editing and Customization Tools

​Chart and Table Contextual Tabs
A chart can be either embedded into the worksheet next to its corresponding table or displayed on a separate chart sheet. If desired, the chart can be easily moved from an embedded chart to a chart sheet (and vice-verse). You can move a chart, resize it, change its proportions, adjust its borders, edit the data including its formatting.

To make any changes to a chart you must click on the chart to activate the chart and its editing controls. When you click on a chart to activate it, you will immediately see a 2 contextual tab appear on the Ribbon, one tab is labeled “Design”, and the other tab is labeled “Format”.  When you click on either of these contextual tabs with a mouse, a separate toolbar will appear with various features. Conceptual tabs also appear when other objects are clicked with a mouse, such as a table. A table will have only 1 contextual tab appear that is labeled, “Design”. When you click on this contextual tab with a mouse, a separate toolbar will appear with various features.

Chart and Table Changes (Editing/Customization)
An important thing to remember is that charts can be updated dynamically. If you update the data in the table that the chart is based on, the chart will automatically update immediately to reflect that data change to its table.

An Excel chart has different areas that can be edited so the chart is customized to your taste. Each specific area can be edited by selecting the area on the Ribbon’s Chart Tools contextual tab or by selecting the area with your mouse.

​There are four methods that can be used to customize or edit an Excel chart. These four methods are:
​    1.  Ribbon Chart Contextual Tabs, “Design” and “Format” (FIGURE 5). Some part of the chart must be selected with a mouse to make these tabs visible.

2.  Three icons that appear to the right of the chart are top-to-bottom: Chart Elements, Chart Styles, Chart Filters  in FIGURE 6. In FIGURE 7, they are shown how they would normally appear with a chart.

3.   Mouse shortcut menu (Right-button click with mouse on a specific chart area) (FIGURE 8)

4.   Chart Mini-Toolbar (FIGURE 9). This Mini-Toolbar normally appears directly above a mouse shortcut menu, like the one shown in FIGURE 10.

END OF LESSON

Back to: Intermediate Excel 2016 > Module 3 – Intermediate Excel 2016