Unit 2: More Work with Charts
This unit examines some more advanced charts you can create in Excel and other spreadsheet programs. We discuss the scatter plot, which shows correlations between two variables plotted on the x- and y-axes. We will learn about linear fit, which creates a best-fit line and equation for data. We will then explore combination charts, which combine two different chart types to compare two different sets of data.
Finally, we will examine sparklines, a relatively new feature of Excel. Sparklines are very small charts that can fit into a cell, usually adjacent to the cell where the data is derived from. The sparkline graphically represents the number. When used in consecutive cells, they create a way to differentiate numerical data that lets us comprehend it more easily without the need for a large separate chart.
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;
- create Sparklines in an appropriate context; and
- create a combination chart in an appropriate context.
2.1: Scatter Charts
We use scatter charts to show how two different variables, plotted on the x- and y-axes, are correlated. Researchers frequently use scatter plots in scientific applications. For example, you can use a scatter plot to show bacterial growth over time. You can also use scatter plots in business, such as by displaying supply and demand over a year.
One benefit of the scatter plot is that it allows us to calculate the linear trendline. Spreadsheet programs use a mathematical operation called the least-squares method to calculate a straight line that best fits data. This can be useful for forecasting trends.
This section describes how to construct and format a scatter chart for supply and demand data. Pay careful attention to the section on adding trendlines, which gives step-by-step guidance using Microsoft Excel. In the example, a linear fit did not work, and they needed to use a different fit.
Watch these two examples of how to create a scatter plot and determine the best-fit trend line in Microsoft Excel.
This process is similar in Google Sheets. Watch this tutorial on how to create scatter plots with trendlines in Google Sheets.
2.2: Combination Charts
As their name suggests, combination charts combine two different chart types into one. We often use combination charts to compare trends or correlations among variables. For example, you could plot monthly revenue for a company as a column chart and combine it with a line chart to display monthly expenses. When making combination charts, it is important to label your spreadsheet correctly. A well-labeled, organized spreadsheet will make it easier for you to graph the two data sets properly.
2.3: Sparklines
Sparklines are small 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 to display trends quickly without having to create a large graph.
We can also use sparklines in Google Sheets. Watch this video on how to create sparklines in Google Sheets to display donations to an organization over time.