- Construct a line chart to show timeline and comparison trends.
- Learn how to use a column chart to show one and two frequency distributions.
- Create and format a map chart.
- Insert a funnel chart.
- Learn how to use a pie chart to show the percent of the total for a data set.
- Compare the difference between a column chart and a bar chart.
- Construct column charts to show how a percent of total changes over time.
This section reviews the most commonly used Excel chart types. To demonstrate the variety of chart types available in Excel, it is necessary to use a variety of data sets. This is necessary not only to demonstrate the construction of charts but also to explain how to choose the right type of chart given your data and the idea you intend to communicate.
Choosing a Chart Type
Before we begin, let’s review a few key points you need to consider before creating any chart in Excel.
- The first is identifying your idea or message. It is important to keep in mind that the primary purpose of a chart is to present quantitative information to an audience. Therefore, you must first decide what message or idea you wish to present. This is critical in helping you select specific data from a worksheet that will be used in a chart. Throughout this chapter, we will reinforce the intended message first before creating each chart.
- The second key point is selecting the right chart type. The chart type you select will depend on the data you have and the message you intend to communicate.
- The third key point is identifying the values that should appear on the X and Y axes. One of the ways to identify which values belong on the X and Y axes is to sketch the chart on paper first. If you can visualize what your chart is supposed to look like, you will have an easier time selecting information correctly and using Excel to construct an effective chart that accurately communicates your message. Table 4.1 “Key Steps Before Constructing an Excel Chart” provides a brief summary of these points.
Carefully Select Data When Creating a Chart
Just because you have data in a worksheet does not mean it must all be placed onto a chart. When creating a chart, it is common for only specific data points to be used. To determine what data should be used when creating a chart, you must first identify the message or idea that you want to communicate to an audience.
Table 4.1 Key Steps before Constructing an Excel Chart
|Define your message.||Identify the main idea you are trying to communicate to an audience. If there is no main point or important message that can be revealed by a chart, you might want to question the necessity of creating a chart.|
|Identify the data you need.||Once you have a clear message, identify the data on a worksheet that you will need to construct a chart. In some cases, you may need to create formulas or consolidate items into broader categories.|
|Select a chart type.||The type of chart you select will depend on the message you are communicating and the data you are using.|
|Identify the values for the X and Y axes.||After you have selected a chart type, you may find that drawing a sketch is helpful in identifying which values should be on the X and Y axes. In Excel, the axes are:
The “category” axis. Usually the horizontal axis – where the labels are found.
The “value” axis. Usually the vertical axis – where the numbers are found.
Time Series Trend: Line Chart 1
The first chart we will demonstrate is a line chart. Figure 4.1 shows part of the data that will be used to create two line charts. This chart will show the trend of the NASDAQ stock index.
This chart will be used to communicate a simple message: to show how the index has performed over a two-year period. We can use this chart in a presentation to show whether stock prices have been increasing, decreasing, or remaining constant over the designated period of time.
Before we create the line chart, it is important to identify why it is an appropriate chart type given the message we wish to communicate and the data we have. When presenting the trend for any data over a designated period of time, the most commonly used chart types are the line chart and the column chart. With the column chart, you are limited to a certain number of bars or data points. As shown below in Figure 4.1, as the number of bars increases on a column chart, it becomes increasingly difficult to read. In our first example, there are 24 points of data used to construct the chart. This is generally too many data points to put on a column chart, which is why we are using a line chart.
The following steps explain how to construct this chart:
Download Data file: CH4 Data
1. Open data file CH4 Data and save a file to your computer as CH4 Charting.
2. Navigate to the Stock Trend worksheet.
3. Highlight the range B4:C28 on the Stock Trend worksheet. (Note – you have selected a label in the first row and more labels in column B. Watch where they show up in your completed chart.)
4. Click the Insert tab of the ribbon.
5. Click the Line button in the Charts group of commands. Click the first option from the list, which is a basic 2D Line Chart (see Figure 4.2). Notice Excel adds, or embeds, the line chart into the worksheet.
Line Chart vs. Column Chart
We can use both a line chart and a column chart to illustrate a trend over time. However, a line chart is far more effective when there are many periods of time being measured. For example, if we are measuring fifty-two weeks, a column chart would require fifty-two bars. A general rule of thumb is to use a column chart when twenty bars or less are required. A column chart becomes difficult to read as the number of bars exceeds twenty.
Figure 4.3 shows the embedded line chart in the Stock Trend worksheet. Do you see where your labels showed up on the chart?
Notice that additional tabs, or contextual tabs, are added to the ribbon. We will demonstrate the commands in these tabs throughout this chapter. These tabs appear only when the chart is activated.
As shown in Figure 4.3, the embedded chart is not placed in an ideal location on the worksheet since it is covering several cell locations that contain data. The following steps demonstrate common adjustments that are made when working with embedded charts:
1. Moving a chart: Click and drag the upper left corner of the chart to the corner of cell B30.
2. Resizing a chart: Place the mouse pointer over the bottom lower corner sizing handle, drag and drop to approximately the end of Column I, and Row 45.
3. Adjusting the chart title: Click the chart title once. Then click in front of the first letter. You should see a blinking cursor in front of the letter. This allows you to modify the title of the chart.
4. Type the following in front of the first letter in the chart title: May 2014-2016 Trend for NASDAQ Sales.
5. Click anywhere outside of the chart to deactivate it.
6. Save your work.
Figure 4.4 shows the line chart after it is moved and resized. Notice that the sizing handles do not appear around the perimeter of the chart. This is because the chart has been deactivated. To activate the chart, click anywhere inside the chart perimeter.
When using line charts in Excel, keep in mind that anything placed on the X-axis is considered a descriptive label, not a numeric value. This is an example of a category axis. This is important because there will never be a change in the spacing of any items placed on the X-axis of a line chart. If you need to create a chart using numeric data on the category axis, you will have to modify the chart. We will do that later in the chapter.
Inserting a Line Chart
- Highlight a range of cells that contain data that will be used to create the chart. Be sure to include labels in your selection.
- Click the Insert tab of the ribbon.
- Click the Line button in the Charts group.
- Select a format option from the Line Chart drop-down menu.
Adjusting the Y-Axis Scale
After creating an Excel chart, you may find it necessary to adjust the scale of the Y-axis. Excel automatically sets the maximum value for the Y-axis based on the data used to create the chart. The minimum value is usually set to zero. That is usually a good thing. However, depending on the data you are using to create the chart, setting the minimum value to zero can substantially minimize the graphical presentation of a trend. For example, the trend shown in Figure 4.4 appears to be increasing slightly in recent months. The presentation of this trend can be improved if the minimum value started at 500,000. The following steps explain how to make this adjustment to the Y-axis:
1. Click anywhere on the Y (value or vertical) axis on the May 2014-2016 Trend for NASDAQ Sales Volume line chart (Stock Trend worksheet).
2. Right Click and select Format Axis. The Format Axis Pane should appear, as shown in Figure 4.5.
Mac Users: Hold down the Control key and click the Y axis. Then choose Format Axis.
3. In the Format Axis Pane, click the input box for the “Minimum” axis option and delete the zero. Then type the number 500000 and hit Enter. As soon as you make this change, the Y axis on the chart adjusts.
4. Click the X in the upper right corner of the Format Axis pane to close it.
5. Save your work.
Figure 4.6 shows the change in the presentation of the trend line. Notice that with the Y axis starting at 500,000, the trend for the NASDAQ is more pronounced. This adjustment makes it easier for the audience to see the magnitude of the trend.
Adjusting the Y-Axis Scale
- Click anywhere along the Y-axis to activate it.
- Right Click.
(Note, you can also select the Format tab in the Chart Tools section of the ribbon.)
- Select Format Axis . . .
- In the Format Axis pane, make your changes to the Axis Options.
- Click in the input box next to the desired axis option and then type the new scale value.
- Click the Close button at the top right of the Format Axis pane to close it.
Trend Comparisons: Line Chart 2
We will now create a second line chart using the data in the Stock Trend worksheet. The purpose of this chart is to compare two trends: the change in volume for the NASDAQ and the change in the Closing price.
Before creating the chart to compare the NASDAQ volume and sales price, it is important to review the data in the range B4:D28 on the Stock Trend worksheet. We cannot use the volume of sales and the closing price because the values are not comparable. That is, the closing price is in a range of $45.00 to $115.00, but the data for the volume of Sales is in a range of 684,000 to 3,711,000. If we used these values – without making changes to the chart — we would not be able to see the closing price at all.
The construction of this second line chart will be similar to the first line chart. The X axis will be the months in the range B4:D28.
- Highlight the range B4:D28 on the Stock Trend worksheet.
- Click the Insert tab of the ribbon.
- Click the Line button in the Charts group of commands.
- Click the first option from the list, which is a basic line chart.
Figure 4.6.5 shows the appearance of the line chart comparing both the volume and the closing price before it is moved and resized. Notice that the line for the closing price (Close) appears as a straight line at the bottom of the chart.
1. Move the chart so the upper left corner is in the middle of cell M1.
2. Resize the chart, using the resizing handle so the graph is approximately in the area of M1:U13.
3. Click in the text box that says “Chart Title.” Delete the text and replace it with the following: 24 Month Trend Comparison.
4. Adjust the Closing Price axis, by double-clicking the red line across the bottom of the chart that represents the Closing Price.
5. The Format Data Series dialogue box opens. In the Series Options, select Secondary Axis.
Excel adds the secondary axis. Format the values on the secondary axis to represent prices.
1. Double click the Secondary Vertical Axis. (The vertical axis on the right that goes from 0 to 140.)
2. In axis options, scroll down to the Number section.
3. Use the Symbol list box to add the $.
4. Press the Close button to close the Format Axis pane.
5. Save your work.
X and Y-Axis Number Formats
- Double click anywhere along the X or Y axis to activate it.
- Click Number from the list of options on the left side of the Format Axis dialog box.
- Select a number format and set decimal places on the right side of the Format Axis dialog box.
- Click the Close button in the Format Axis pane.
Frequency Distribution: Column Chart 1
A column chart is commonly used to show trends over time, as long as the data are limited to approximately twenty points or less. A common use for column charts is frequency distributions. A frequency distribution shows the number of occurrences by established categories.
For example, a common frequency distribution used in most academic institutions is a grade distribution. A grade distribution shows the number of students that achieve each level of a typical grading scale (A, A−, B+, B, etc.). The Grade Distribution worksheet contains final grades for some hypothetical Excel classes.
To show the grade frequency distribution for all the Excel classes in that year, the Numbers of Students appear on the Y-axis and the Grade Categories appear on the X-axis. In this situation, notice we do not select the Total row. The totals are a representation of all data and would skew the graph. Essentially you would be graphing the information twice. If you want to display the totals in a chart, the best approach is to create a separate chart that only displays the total values.
The following steps to create the column chart:
1. Select the Grade Distribution worksheet.
2. In Row3, replace the red text at states [Insert Current Year] and replace it with the actual current academic term and year.
3. Select two non-adjacent columns by selecting A3:A8.
4. Press, and hold down the Crtl key.
Mac Users: Hold down the Command key instead.
5. Without letting go of the Ctrl key, select C3:C8
6. From the ribbon click the Insert tab. Choose the Column button.
7. Select the Clustered Column format. (First option listed.)
8. Click and drag the chart so the upper left corner is in the middle of cell H2. Resize the graph to fit in the area of H2: O13.
9. Click any cell location on the Grade Distribution worksheet to deactivate the chart.
10. Save your work.
Figure 4.10 shows the completed grade frequency distribution chart. By looking at the chart, you can immediately see that the greatest number of students earned a final grade in the B+ to B− range.
Column Chart vs. Bar Chart
When using charts to show frequency distributions, the difference between a column chart and a bar chart is really a matter of preference. Both are very effective in showing frequency distributions. However, if you are showing a trend over a period of time, a column chart is preferred over a bar chart. This is because a period of time is typically shown horizontally, with the oldest date on the far left and the newest date on the far right. Therefore, the descriptive categories for the chart would have to fall on the horizontal – or category axis, which is the configuration of a column chart. On a bar chart, the descriptive categories are displayed on the vertical axis.
Creating a Chart Sheet
- Click anywhere on the Final Grades for All Excel Classes chart on the Grade Distribution worksheet.
- From the Chart Tools Design tab. Select Move Chart . This opens the Move Chart Dialog box.
- Click the New sheet option on the Move Chart dialog box.
- The entry in the input box for assigning a name to the chart sheet tab should automatically be highlighted once you click the New sheet option. Type All Excel Classes. This replaces the generic name in the input box (see Figure 4.11).
- Click the OK button at the bottom of the Move Chart dialog box. This adds a new chart sheet to the workbook with the name All Excel Classes.
- Save your work.
Figure 4.12 shows the Final Grades for all the Excel Classes column chart is in a separate chart sheet. Notice the new worksheet tab added to the workbook matches the New sheet name entered into the Move Chart dialog box. Since the chart is moved to a separate chart sheet, it no longer is displayed in the Grade Distribution worksheet.
Frequency Comparison: Column Chart 2
We will create a second column chart to show a comparison between two frequency distributions. Column B on the Grade Distribution worksheet contains data showing the number of students who received grades within each category for the Current Excel Class Class. We will use a column chart to compare the grade distribution for the current class (Column B) with the overall grade distribution for Excel courses for the whole year (Column C).
However, since the number of students in the term is significantly different from the total number of students in the year, we must calculate percentages in order to make an effective comparison. The following steps explain how to calculate the percentages:
1. Highlight the range B4:C9 on the Grade Distribution worksheet.
2. Click the AutoSum button in the Editing group of commands on the Home tab of the ribbon. This automatically sums the values in the selected range.
3. Select cell E4. Enter a formula that divides the value in cell B4 by the total in cell B9. Add an absolute reference to cell B9 in the formula =B4/$B$9. Autofill the formula down to cell E8.
4. Select cell F4 . Enter a formula that divides the value in cell C4 by the total in cell C9. Add an absolute reference to cell C9 in the formula =C4/$C$9.
5. Autofill the down to F8.
6. Select A3:A8, press and hold down the Ctrl key and select E3:F8.
7. Click the Insert tab of the ribbon.
8. Select the Column button. Select the first option from the drop-down list of chart formats, which is the Clustered Column.
9. Click and drag the chart so the upper left corner is in the middle of cell H2.
10. Resize the chart to the approximate area of H2:N12.
11. Change the chart title to Grade Distribution Comparison. If you do not have a chart title, you can add one. On the Design tab, select Add Chart Element. Find the Chart Title. Select the Above Chart option from the drop-down list.
12. Save your work.
Figure 4.13 shows the final appearance of the column chart. The column chart is an appropriate type for this data as there are fewer than twenty data points and we can easily see the comparison for each category. An audience can quickly see that the class issued fewer As compared to the college. However, the class had more Bs and Cs compared with the college population.
Too Many Bars on a Column Chart?
Although there is no specific limit for the number of bars you should use on a column chart, a general rule of thumb is twenty bars or less.
Data visualization brings more depth in how information, in this case geographically, connects. You can use a map chart to compare values and show categories across geographical regions like countries/regions, states, counties or postal codes. Excel will automatically convert data to geographical locations and will display values on a map. As shown below, in Figure 4.14, in the next steps we will compare West Coast Community College enrollments for Fall of 2019 using a map chart.
- From the Enrollment Statistics worksheet, select A3:A13. Next, press and hold the CTRL key and select C3:C13.
- Click the Insert tab on the Ribbon.
- Click Maps, and choose the Filled Map option.
- From the Charts Design Tab, choose Move Chart, and select the New Sheet option. In the name box type Map. Click Ok.
- To make sure the map and data are vibrant and will stand out in a presentation make the following changes:
a) Select the Title. Type Enrollment Totals. Change the font to bold, size 18.
b) From the top right corner of the Chart area, choose the Charts Elements plus sign.
c) Select the Data Labels checkbox. Notice the values appear on each State.
Mac Users: there is no “Charts Element plus sign”. Follow the alternate steps below.
Click the “Chart Design” tab on the Ribbon
Click the “Add Chart Element” button on the Ribbon
Point to “Data Labels” option and click “Show”
d) Save your work.
Another graph to visualize data is a Funnel chart. Funnel charts provide a visual snapshot of a process. From our data, we will create a Funnel Chart to show how many students we have in the admissions process. You can quickly review the funnel chart to see admissions predicts to have 932 new enrolled students for Winter Term 2020.
Insert a Funnel chart by following the below steps.
- From the Admissions sheet, select A3:B7.
- From the Insert tab, choose Recommended Charts.
- Scroll down the list and select the Funnel Chart. Click OK.
- Move and resize the graph to approximately fit in the range of D1:K15.
- From the Design Tab, click the 6th style option provided in the styles gallery. (The background is black.) It is okay if your text color is different than the figure below.
- Change the Chart Title to Admissions Pipeline Winter 2020.
- From the Charts Elements, turn off the Legend.
Mac Users: Click the “Add Chart Element” button and change the “Legend” option to “None“
- Save your work.
PERCENT OF TOTAL: DOUGHNUT PIE CHART
The next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of the total for a data set at a specific point in time. Using the Doughnut Pie Chart, show the percentage of students enrolled at a full-time status. As in the last example, the data is located on the Enrollment Statistics sheet.
- From the Enrollment Statistics worksheet, select A3:A13. Next, press and hold the CTRL key and select D3:D13.
- Click the Insert tab on the ribbon.
- Click the Pie button in the Charts group of commands.
- Select the “Doughnut” option from the drop-down list of options.
- From the Charts Design Tab, choose Move Chart, and select the New Sheet option. In the name box type Full-Time Students. Click Ok.
- From the Chart Tools Design Tab, click the Chart Styles gallery and apply Style 7.
- On the Chart, expand the Charts Elements tools. Select the Legend Choose to display the legend on the Right.
Mac Users: Click “Add Chart Element” button, point to Legend and choose “Right”
- From the Charts Elements, select the Data Labels checkbox. Then, drop down the Data Labels menu. Choose, More Options.
Mac Users: Click “Add Chart Element” button, point to Data Labels and choose “More Data Label Options”
9. From the Format Data Label Options menu, select Percentages, and Deselect Values to show the percent of total students that are enrolled at a full-time status.
10. Close the Format Data Labels menu.
Notice the font is small compared to the graph size. Adjust the font size of the Title, Legend, and Data Label by following the below steps:
- Select the Title. Change the font to bold, size 18.
- Select the Legend. Change the font to bold, size 14.
- Select one of the data labels in a doughnut wedge. Notice now all labels are selected. Change the font bold, size 12.
Inserting a Pie Chart
- Highlight a range of cells that contain the data you will use to create the chart.
- Click the Insert tab of the ribbon.
- Click the Pie button in the Charts group.
- Select a format option from the Pie Chart drop-down menu.
Bar chart vs Column chart
We will statistical data to compare a bar and column chart. Both the Bar and the Column chart display data using rectangular bars where the length of the bar is proportional to the data value. Both charts are used to compare two or more values. However, the difference lies in their orientation. A bar chart is oriented horizontally whereas the column chart is oriented vertically. Although alike, they cannot be always used interchangeably. The difference in their orientation, meaning typically the more data values the harder it is to read in a column format. This is where visually a bar chart would be a better choice. Complete the below steps to insert both a bar and column chart comparing not only the gender and age differences of enrolled students but the type of graphs you are viewing the data in.
- From the Enrollment Statistics sheet, select A3:A13. Press and hold the CTRL key and select G3:H13.
- From the Insert tab, choose Recommended Charts. Scroll down and select the Stacked Bar chart option. Click Ok.
- Move and resize the graph so it fits approximately in L1:U13.
- Add a chart title. Type Age Comparison.
- Notice the age difference. Currently, per State, the majority of students are under 20 years old. Save your work.
Next, insert a column chart comparing gender.
- From the Enrollment Statistics sheet, select A3:A13. Press and hold the CTRL key and select I3:J13.
- From the Insert tab, choose Recommended Charts. Choose the first option, Clustered Column chart.
- Move and resize the graph so it fits approximately in L15:U32.
- Add a chart title. Type Gender Comparison.
- Notice the ratio of women and men enrolled are pretty equal per State. Save your work.
Stacked Column Chart
The last chart types we will demonstrate is the stacked column chart and a bar chart. You will use a stacked column chart to show differences in budgeted expense accounts for the admissions department and a bar chart for age comparisons of enrolled students at the college.
Follow the below steps to insert a stacked column chart.
- Click the Expenses sheet. Select the range A4:G9.
- Click the Insert tab of the ribbon.
- Click the Column button in the Charts group of commands. Select the 3D– Stacked Column format.
- Change the Chart Title to Expenses.
- Move the Chart to a New Sheet. In the name box type Budget.
- Save your work.
Figure 4.21 shows the final stacked column chart.
Inserting a Stacked Column Chart
- Highlight a range of cells that contain data that will be used to create the chart.
- Click the Insert tab of the ribbon.
- Click the Column button in the Charts group.
- Select the Stacked Column format option from the Column Chart drop-down menu to show the values of each category on the Y-axis. Select the Stacked Column option to show each category on the Y-axis.
- Identifying the message you wish to convey to an audience is a critical first step in creating an Excel chart.
- Both a column chart and a line chart can be used to present a trend over a period of time. However, a line chart is preferred over a column chart when presenting data over long periods of time.
- The number of bars on a column chart should be limited to approximately twenty bars or less.
- When creating a chart to compare trends, the values for each data series must be within a reasonable range. If there is a wide variance between the values in the two data series (two times or more), the percent change should be calculated with respect to the first data point for each series.
- When working with frequency distributions, the use of a column chart or a bar chart is a matter of preference. However, a column chart is preferred when working with a trend over a period of time.
- A pie chart is used to present the percent of total for a data set.
- A stacked column chart is used to show how a percent total changes over time.
Adapted by Noreen Brown from How to Use Microsoft Excel: The Careers in Practice Series, adapted by The Saylor Foundation without attribution as requested by the work’s original creator or licensee, and licensed under CC BY-NC-SA 3.0.