Unit 2: More Work with Charts
This unit examines some more advanced styles of charts you can create in Excel and other spreadsheet programs. We discuss the scatterplot, which shows a correlation between two variables plotted on the x- and y-axis. We learn about linear fit, which creates a best-fit line and equation for your data. Next, we explore combination charts, which combine two different chart types into one to compare two different data sets.
Finally, we examine sparklines, which are tiny charts that fit into one cell, usually adjacent to the cell from which the data are derived. A sparkline graphically represents the number. When used in consecutive cells, sparklines create a visual way to differentiate numerical data so your audience can easily understand your message – they do not need a large chart embedded on the page, separate from the data.
Completing this unit should take you approximately 1 hour.
Upon successful completion of this unit, you will be able to:
- construct a scatter chart;
- create a trendline and line equation for a data series;
- discuss the appropriate context for using Sparklines; and
- discuss the appropriate context for using a combination chart.
2.1: The Scatter Chart
We use a scatter chart to show how two variables, plotted on the x and y axes, are related. Researchers often use scatterplots in scientific applications. For example, you can use a scatterplot to show a timeline for bacterial growth. For an annual business analysis, you can use scatterplots in other applications to display supply and demand.
One benefit of the scatterplot is that it allows you to calculate linear trendlines. Spreadsheet programs use a mathematical operation called the method of least squares to calculate a straight line that best fits your data. This can be useful for forecasting trends.
Read this text on constructing a scatter chart for supply and demand data. The reading outlines the steps for creating a scatter chart after Figure 4.45. Most of the text details how to format these charts. Pay careful attention to the section "Adding a Trendline and Equation" since it provides step-by-step guidance on how to add a trendline in Excel. Note that a linear fit did not work in the example, and they needed to use a different fit.
Watch these two brief videos showing worked examples of creating a scatter plot and determining the best-fit trend line.
This process is similar to Google Sheets. Watch this tutorial on creating scatter plots with trendlines in Google Sheets.
2.2: Combination Charts
As their name suggests, combination charts combine two different chart types into a single chart. We often use combination charts to compare trends or correlations among variables. For example, you could plot a company's monthly revenue as a column chart and combine it with a line chart to display monthly expenses. When making combination charts, it is essential to label your spreadsheet correctly. A properly labeled, organized spreadsheet will make graphing the two data sets easier.
2.3: Sparklines
Sparklines are tiny graphs that are visible in the worksheet itself. We use them to display trends or other quick information about your data. For example, you can include a line graph sparkline at the end of a column of stock prices to show how the prices change with time. This is useful for quickly displaying trends without creating a large graph.
Sparklines can be added from the insert tab to provide insight into your data without taking up too much space, as shown below.
Figure 1: Sparklines
Watch this video on how to create sparklines in an annual sales spreadsheet.
We can also use sparklines in Google Sheets. Watch this video on creating sparklines in Google Sheets to display donations to an organization over time.