PRDV007: Spreadsheets III: Presenting Data

This section focuses on the scatter chart type. This chart differs from the other charts demonstrated in this chapter because values are used on both the X- and Y-axes. So far, the charts we have demonstrated in this chapter use categories or qualitative labels for the X-axis. This means the distance between each category on the X-axis will always be the same, even if numbers are used. In a scatter chart, the X-axis operates just like the Y-axis. In other words, the distance between the values on the X-axis will vary depending on the value of the number. Depending on the format, we can create a scatter chart to look just like a line chart. Since the X- and Y-axes contain quantitative values, the scatter chart is a valuable tool for studying various shapes or functional forms for a line chart. In fact, a common feature used with the scatter chart is the trendline and equation. Excel can evaluate the line produced on a scatter chart and produce a mathematical equation. We will demonstrate these features in this section.

Supply and Demand: The Scatter Chart

A common use for a scatter chart is the study of supply and demand curves. This is because the data points for both the supply and demand lines require quantitative values on both the X- and Y-axes. The Y-axis contains the price of a certain good or item; the X-axis contains the quantity sold for that item. Fundamental economic laws state that sellers are willing to increase supply and sell more goods as prices rise. However, the reverse is true for consumers. As prices rise, consumers purchase fewer goods. The Supply & Demand worksheet contains hypothetical data for the supply and demand of breakfast cereal. Ten data points in Column A show the change in supply and demand as the price changes. The values in Columns A through C are formula outputs driven by the percentage in cell C14. For example, if the percentage in cell C14 is changed to 10, each price listed in Column A will increase, as shown in Figure 4.45, "Hypothetical Supply and Demand Data."

Figure 4.45 Hypothetical Supply and Demand Data

We will use the scatter chart to study the change in quantity supplied and demanded as the price increases over ten data points, as shown in Figure 4.45, "Hypothetical Supply and Demand Data." For many of the charts demonstrated in this chapter, we could highlight a range of cells and insert the chart type we needed. This was especially the case when the data was in a contiguous range of cells. However, this method rarely works when creating a scatter chart, even if the data are in a contiguous range. As a result, the method we present here starts with a blank chart and demonstrates how each data series is added to the chart individually. The following steps explain how we create this chart:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Activate cell E1 on the Supply & Demand worksheet. It is important to note that this cell location is not adjacent to any data on the worksheet.
3. Click the Scatter button from the Charts group of commands on the Insert tab of the Ribbon.

4. Select the Scatter with Smooth Lines and Markers format from the drop-down list of options (see Figure 4.46, "Selecting a Scatter Chart Format"). This adds a blank chart to the worksheet.
   
   

   
   
   

   Figure 4.46 Selecting a Scatter Chart Format
   
   

5. Click and drag the chart so the upper left corner is in the center of cell E2.
   
6. Resize the chart so the left side is locked to the left side of Column E, the right side is locked to the right side of Column M, the top is locked to the top of Row 2, and the bottom is locked to Row 17.
   
7. Click the Design tab in the Chart Tools section of the Ribbon. Then click the Select Data button in the Data group of commands. This opens the Select Data Source dialog box.
   
8. Click the Add button on the left side of the Select Data Source dialog box. This opens the Edit Series dialog box. Notice that this dialog box has inputs for defining values for the X- and Y-axes. Charts we previously created using this method only had an input for putting values on the Y-axis.
   
9. Type the series name Demand. This should appear in the Series name input box.
   
10. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
    
11. Highlight the range B3:B12 on the Supply & Demand worksheet. After it is highlighted, this range will appear in the Series X values input box.
    
12. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.
13. Highlight the range A3:A12 on the Supply & Demand worksheet.
    
    Figure 4.47, "Defining the Demand Data Series," shows the final settings in the Edit Series dialog box for the Demand data series. You will see that as the X- and Y-axis values are defined in the dialog box, they appear on the chart. The chart in this figure shows the price along the Y-axis and quantity along the X-axis.
    
    

    

    Figure 4.47 Defining the Demand Data Series
    
    

14. Click the OK button at the bottom of the Edit Series dialog box.
15. Click the Add button on the left side of the Select Data Source dialog box.
    
16. Type the series name Supply. This should appear in the Series name input box.
    
17. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.
    
18. Highlight the range C3:C12 on the Supply & Demand worksheet. After it is highlighted, this range appears in the Series X values input box.
    
19. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.
    
20. Highlight the range A3:A12 on the Supply & Demand worksheet.
    
21. Click the OK button at the bottom of the Edit Series dialog box.
    
22. Click the OK button at the bottom of the Select Data Source dialog box.
    

For Scatter Charts, Start with a Blank Chart

When creating a scatter chart, it is best to start with a blank chart and add each data series individually. This is because Excel will not always guess correctly which values belong on the X and Y axes since both contain numbers. The X-axis contains non-numeric data for other chart types, such as column or line charts, so it's easy for Excel to configure the chart you need.

Figure 4.48, "Scatter Chart Showing One Price," shows the appearance of the scatter chart before any formatting enhancements are applied. Notice only two plot points are located on the chart. This is because the price change value in cell C14 is still zero. Therefore, the data do not reflect any change in price, quantity demanded, or quantity supplied. The chart shows that at the current price of $1.50, suppliers are willing to provide fewer units compared with the number of units consumers are willing to buy.

Figure 4.48 Scatter Chart Showing One Price

The following steps explain the formatting enhancements we will apply to the scatter chart shown in Figure 4.48, "Scatter Chart Showing One Price":

1. Add a title to the chart by clicking the Chart Title button in the Layout tab of the Chart Tools section of the Ribbon. Use the Above Chart option from the drop-down list.
2. Select Subtle Effect - Orange, Accent 6 from the preset style list in the Shape Styles group of commands on the Format tab of the Ribbon.
3. Change the font style of the chart title to Arial and the font size to 14 points.
4. Change the wording of the chart title to the following: Supply and Demand for Breakfast Cereal.
5. To add a title to the Y-axis, click the Axis Titles button in the Layout tab of the Ribbon and select the Rotated Title option from the Primary Vertical Axis Title drop-down list.
6. Repeat steps 2 and 3 to format the Y-axis title. However, change the font size to 12 points.
7. Change the wording of the Y-axis title as follows: Price per Unit.
8. Add a title to the X-axis.
9. Repeat steps 2 and 3 to format the X-axis title. However, change the font size to 12 points.
10. Change the wording of the X-axis title to Quantity in Units.
11. Make the following format changes to the X- and Y-axis values: font style Arial, font size 11 points, and bold.
12. Change the color of the chart area to Aqua, Accent 5, Lighter 40 percent (see Figure 4.49, "Formatting Enhancements Added to the Scatter Chart").
    
    
    
    
    

    Figure 4.49 Formatting Enhancements Added to the Scatter Chart
    
    

13. Apply a bevel effect to the plot area. Use the Circle format option from the Bevel drop-down list of options.
14. Change the font style of the legend to Arial and put the font in bold.
15. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, the lines change on the chart.

Figure 4.50, "Scatter Chart with Price Change at Two Percent," shows the completed scatter chart when the Price Change is set to 2 percent, and Figure 4.51, "Scatter Chart with Price Change at 14 Percent," shows the same chart when the Price Change is set to 14 Percent. The point at which the demand and supply lines intersect in Figure 4.51, "Scatter Chart with Price Change at 14 Percent," is the market equilibrium point. The market equilibrium is where the quantity demanded equals the quantity supplied at a specific price. The price where quantity demanded equals quantity supplied is called the equilibrium price.

Figure 4.50 Scatter Chart with Price Change at Two Percent

Figure 4.51 Scatter Chart with Price Change at 14 Percent

Changing the Scale of the X and Y Axes

Excel has automatically established the scale for the Y-axis for all the charts demonstrated in this chapter. For scatter charts, Excel has also established the scale for the X-axis. The axis scale is the minimum and maximum value that appears on an axis. For example, in Figure 4.51, "Scatter Chart with Price Change at 14 Percent," the Y-axis scale is set to a minimum value of zero and a maximum value of 6.00. Although this is a very convenient feature of Excel, you may want to change the scale in some instances. If you change the value in cell C14 on the Supply & Demand worksheet, the lines jump or shift on the chart's plot area. This is because Excel keeps rearranging the scale of the X- and Y-axes. When studying the shape of lines, it is best to set the scale so it does not change. The following steps explain how to accomplish this:

1. Change the value in cell C14 on the Supply & Demand worksheet to zero.
2. Click anywhere on the Y-axis of the chart.
3. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box.
4. In the Format Axis dialog box, click the Fixed option next to the Minimum setting under the Axis Options. This ensures that the minimum value for the Y-axis will always be zero.
5. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
6. Click in the input box next to the Maximum setting. Remove the 1.6 and enter the number 5.0. We will not be studying supply and demand behavior beyond a $5.00 price point, so there is no need to extend the Y-axis beyond this point.
7. In the Format Axis dialog box, click the Fixed option next to the Major Unit setting under the Axis Options.
8. Click in the input box next to the Major Unit setting and change the value from 0.2 to 0.5 (see Figure 4.52, "Setting the Y-Axis Scale"). This allows us to measure the plot points in $0.50 intervals along the Y-axis. When the axis extends to $5.00, $0.20 intervals may place too many values along the Y-axis, making it difficult to read.
9. Click the Close button at the bottom of the Format Axis dialog box.
   
   

   

   Figure 4.52 Setting the Y-Axis Scale

10. Click anywhere along the X-axis of the chart.
11. Click the Format Selection button in the Layout tab of the Chart Tools section of the Ribbon. This opens the Format Axis dialog box for the X-axis.
12. Click the Fixed option next to the Minimum setting under the Axis Options in the Format Axis dialog box. This ensures that the minimum value for the X-axis will always be zero.
13. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.
14. Click in the input box next to the Maximum setting. Remove the 500.0 and enter 450.0. Based on the price points in our study, the number of units supplied or demanded will not exceed 450. There is no need to extend the X-axis to 500.
15. The Fixed option is next to the Major Unit setting under the Axis Options in the Format Axis dialog box.
16. Click in the input box next to the Major Unit setting and change the value from 100.0 to 50.0. This allows us to measure the plot points in 50-unit intervals along the X-axis.
17. Click the Close button at the bottom of the Format Axis dialog box.
18. Change the value in cell C14 to 2. Then change it to 4 and then to 8. Change the value one more time to 14. As you change the values in cell C14, the lines change, but they no longer jump or shift since the scale of both axes is fixed.

Figure 4.53, "Final Appearance of the Scatter Chart," shows the final appearance of the scatter chart after the scale is set for both the X and Y axes. Notice that market equilibrium is achieved at a price of approximately $2.50.

Figure 4.53 Final Appearance of the Scatter Chart

Adding a Trendline and Equation

A trendline can be applied to a chart to estimate or predict where plot points may occur at various points along the X- and Y-axes. Excel enables you to add a trendline to a chart and provides the equation to plot additional points. The following steps explain how to accomplish this:

1. Set the value in cell C14 on the Supply & Demand worksheet to 14.
2. Click anywhere in the chart area of the scatter chart to activate it.
3. Click the Trendline button in the Layout tab of the Ribbon. Select the Linear Trendline option from the drop-down list.
4. Select the Demand option from the Add Trendline dialog box and click the OK button. This adds a new line to the plot area of the chart as well as the legend.

Figure 4.54 Adding a Linear Trendline

5. Click the Trendline button in the Layout tab of the Ribbon. From the drop-down list, select the None option to remove the trendline from the chart.
6. Click the Trendline button in the Layout tab of the Ribbon again. This time, select More Trendline Options from the drop-down list.
7. Select the Demand option from the Add Trendline dialog box and click OK. This opens the Format Trendline dialog box.
8. Select the Power option from the Format Trendline dialog box.
9. Click the "Display Equation on chart" option at the bottom of the Format Trendline dialog box (see Figure 4.55, "The Format Trendline Dialog Box").
10. Click the Close button at the bottom of the Format Trendline dialog box.
    
    

Figure 4.55 The Format Trendline Dialog Box

Figure 4.56, "Scatter Chart with a Power Trendline," shows the scatter chart with the Power Trendline added for the demand series. Notice that the line fits perfectly over the demand series in the plot area. In fact, it may be difficult to see the line in the figure. This indicates that the trendline is an excellent fit for the demand line. As a result, we can be confident in using this line to predict other demand values along the X and Y axes. You can also see that the equation for this trendline has been added to the chart's plot area. We can use the equation to calculate the price for each quantity value substituted for X. If the number 150 is substituted for X in the equation, the result is a price of $2.59. This result appears to be accurate based on the values used to create the chart.

Figure 4.56 Scatter Chart with a Power Trendline

 This text was adapted by Saylor Academy under a Creative Commons Attribution-NonCommercial-ShareAlike 3.0 License without attribution as requested by the work's original creator or licensor.