LEARNING OBJECTIVES
Show
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 TypeBefore we begin, let’s review a few key points you need to consider before creating any chart in Excel.
Integrity CheckCarefully 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
Time Series Trend: Line Chart 1The 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. Read more: http://www.investopedia.com/terms/n/nasdaq.asp 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. Figure 4.1 Stock TrendsBefore 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 you increase the number of bars on a column chart, it becomes increasingly difficult to read. As you scroll through the data on the worksheet shown in Figure 4.1 you will see that 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. Our line chart will show the volume of sales for the NASDAQ on the Y axis and the Month number on the X axis. The following steps explain how to construct this chart: Download Data file: CH4 Data
This adds, or embeds, the line chart to the worksheet, as shown in Figure 4.3 Why?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. Note: Excel 2010 uses three contextual tabs for charts. Later versions use only two. Each has all the same tools. They are just organized a little differently. Figure 4.3 Embedded Line Chart in the Stock Trend WorksheetAs 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:
Figure 4.4 shows the line chart after it is moved and resized. You can also see that the title of the chart has been edited to read May 2014-2016 Trend for NASDAQ Sales Volume. 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. Figure 4.4 Line Chart Moved and ResizedIntegrity CheckWhen 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. Skill RefresherInserting a Line Chart
Adjusting the Y Axis ScaleAfter 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:
Note: If you do not see “Format Axis . . . on your menu, you have not right clicked in the correct spot. Press “Escape” to turn the menu off and try again Figure 4.5 Format Axis Pane
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. Figure 4.6 Adjusted Y Axis for the S&P 500 ChartSkill RefresherAdjusting the Y Axis Scale
Trend Comparisons: Line Chart 2We 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.
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. Also, the chart is covering the data again, and the title needs to be changed. Figure 4.6 Trend Comparison Line Chart
Good. But, we still cannot really see the Closing Price data. It is the flat red line at the very bottom of the chart.
Better! But, it would be nice to be able to see that the values on the right represent prices.
“Instant” Chart – F11On the Stock Trend worksheet:
Frequency Distribution: Column Chart 1A 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. The number of students for this chart is in Column C. The labels for grades are in Column A. The following steps explain how to create this chart:
Figure 4.12 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. Figure 4.12 Grade Frequency Distribution ChartWhy?Column Chart vs. Bar ChartWhen 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 SheetThe charts we have created up to this point have been added to, or embedded in, an existing worksheet (with the exception of the Instant Chart we created using F11). Charts can also be placed in a dedicated worksheet called a chart sheet. It is called a chart sheet because it can only contain an Excel chart. Chart sheets are useful if you need to create several charts using the data in a single worksheet. If you embed several charts in one worksheet, it can be cumbersome to navigate and browse through the charts. It is easier to browse through charts when they are moved to a chart sheet because a separate sheet tab is added to the workbook for each chart. The following steps explain how to move the grade frequency distribution chart to a dedicated chart sheet:
Figure 4.14 shows the Final Grades for the 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. Figure 4.14 Chart Sheet Added to the WorkbookFrequency Comparison: Column Chart 2We 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 Spring Quarter. We will use a column chart to compare the grade distribution for Spring (Column B) with the overall grade distribution 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:
Figure 4.15 shows the completed percentages added to the Grade Distribution worksheet. The column chart we are going to create uses the grade categories in the range A4:A8 on the X axis and the percentages in the range E4:F8 on the Y axis. This chart uses data that is not in a contiguous range, so we need to use the Ctrl key to select the ranges of cells.
Figure 4.17 shows the final appearance of the column chart. The column chart is an appropriate type for this data because 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. Figure 4.17 Completed Grade Distribution Column ChartIntegrity CheckToo Many Bars on a Column Chart? Percent of Total: Pie ChartThe next chart we will demonstrate is a pie chart. A pie chart is used to show a percent of total for a data set at a specific point in time. The data we will use to demonstrate a pie chart is related to enrollment data for Portland Area Community Colleges for Fall of 2014. You will find that data on the Enrollment Statistics sheet.
Note: if you let go of the mouse button before dragging, you may only get one slice to move when you drag it out from the center. This can be another option for displaying your data. Use the Undo button to undo this if you want to try again.
Although there are no specific limits for the number of categories you can use on a pie chart, a good rule of thumb is ten or less. As the number of categories exceeds ten, it becomes more difficult to identify key categories that make up the majority of the total. Figure 4.21 Final Enrollment Statistics Pie ChartSkill RefresherInserting a Pie Chart
Percent of Total: Stacked Column ChartThe last chart type we will demonstrate is the stacked column chart. We use a stacked column chart to show a percent of a total . For example, the data on the Enrollment Statistics worksheet shows student enrollment by race for several colleges. We would like to see all of the data on all of the colleges.
Figure 4.23 shows the column chart that is created after selecting the 100% Stacked Column format option. As mentioned, the goal of this chart is to show the enrollment of students by race. However, notice that Excel places the racial categories on the X axis. It would be more useful if the different colleges were there instead. Figure 4.23 Initial Construction of the 100% Stacked Column ChartThe reason that Excel organized the data this way is that there are more Race/ethnicity categories (data in column A) than there are colleges (data in row 2). Not a bad guess. But, not what we wanted in this case. The remaining steps explain how to correct this problem and complete the chart:
Figure 4.25 shows the final stacked column chart. Notice the similarities and differences in the enrollment at the local community colleges. Figure 4.25 Final 100% Stacked Column ChartSkill Refresher:Inserting a Stacked Column Chart
Key Takeaways
AttributionAdapted 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. Which option will you click after selecting a chart and clicking on at its top to add a trendline?Select the + to the top right of the chart. Select Trendline. Note: Excel displays the Trendline option only if you select a chart that has more than one data series without selecting a data series. In the Add Trendline dialog box, select any data series options you want, and click OK.
How do you add a trendline in Powerpoint?A trendline is a regression line that tries to measure the relationship between the variables on the scatter plot. To add a trendlines, we right click a point on the plot, and select Add Trendline. This opens the Format Trendline pane. We can then select the type of trendline under Trendline Options.
How do you add a trendline equation in Excel?Trendline. Select the chart.. Click the + button on the right side of the chart, click the arrow next to Trendline and then click More Options. ... . Choose a Trend/Regression type. ... . Specify the number of periods to include in the forecast. ... . Check "Display Equation on chart" and "Display R-squared value on chart".. How do you add a trendline in Google Sheets?Add a trendline. On your computer, open a spreadsheet in Google Sheets.. Double-click a chart.. At the right, click Customize. Series.. Optional: Next to "Apply to," choose the data series you want to add the trendline to.. Click Trendline. If you don't see this option, trendlines don't work with your data.. |