15 Scatter Charts

The Scatter Chart

This section focuses on the scatter chart type. What makes this chart different from the other charts demonstrated in this chapter is that 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 that 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 the scatter chart to look just like a line chart. Since both 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 that is produced on a scatter chart and produce a mathematical equation. We will demonstrate these features in this section.

The Scatter Chart: Supply and Demand

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.14 if starting here.)

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 good or item. Fundamental economic laws state that as prices rise, sellers are willing to increase supply and sell more goods. 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. There are ten data points to show the change in supply and demand as the price changes in Column A. The values you see in Columns A through C are formula outputs that are 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”.

The formula =A3*(1+$C$14) is multiplying the Price in A3 by 1 + the percent change in cell C14. Using 1 + % is the same as multiplying A3 by 110% (given the example in Figure 4.45.) If you only multiply by the price change percent, you will get just the percent increase amount. Not the price but the percent increase. In the above example, if you multiply by the price change percent only, you will get $.15. Not the new price after the increase has been applied.

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.

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 the bottom of 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 on this dialog box there are inputs for defining values for both the X and Y axes. Charts that 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. You will see this range appear in the Series X values input box after it is highlighted.

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.

14. 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.

15. Click the OK button at the bottom of the Edit Series dialog box.

16. Click the Add button on the left side of the Select Data Source dialog box.

17. Type the series name Supply. This should appear in the Series name input box.

18. Press the TAB key on your keyboard to advance to the Series X values input box on the Edit Series dialog box.

19. Highlight the range C3:C12 on the Supply & Demand worksheet. This range appears in the Series X values input box after it is highlighted.

20. Press the TAB key on your keyboard to advance to the Series Y values input box on the Edit Series dialog box.

21. Highlight the range A3:A12 on the Supply & Demand worksheet.

22. Click the OK button at the bottom of the Edit Series dialog box.

23. Click the OK button at the bottom of the Select Data Source dialog box.

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 are not reflecting 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.

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 titles to the chart by clicking the Quick Layout drop down button on the Chart Tools Design Tab. Choose Layout 1

2. Click on the Chart Title to activate it. Select Subtle Effect – Orange, Accent 6 from the Theme Styles 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, bold, and the font size to 14 points.

4. Change the wording of the chart title as follows: Supply and Demand for Breakfast Cereal.

5. Click on the vertical Axis Title

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. Click on the X Axis Title.

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 as follows: Quantity in Units.

11. Change the color of the plot area back to white. Note: if you don’t change the color to white, when you do step 12 the entire chart will be Aqua Accent 5. You can always come back and change the plot area back to white if that happens.

12. Change the color of the chart area to Aqua, Accent 5, Lighter 40% (see 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 dropdown list of options.

14. Change the font style of the legend to Arial and bold the font.

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, you will see the lines change on the chart.

Figure 4.50 “Scatter Chart with Price Change at 2%” shows the completed scatter chart when the Price Change is set to 2%, and Figure 4.51 “Scatter Chart with Price Change at 14%” shows the same chart when the Price Change is set to 14%. The point at which the demand and supply lines intersect on Figure 4.51 “Scatter Chart with Price Change at 14%” is known as 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 referred to as the equilibrium price.

Changing the Scale of the X and Y Axes

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.15 if starting here.)

For all the charts demonstrated in this chapter, Excel has automatically established the scale for the Y axis. 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%”, 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 plot area of the chart. This is because Excel keeps rearranging the scale of both 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. Right click anywhere on the Y axis of the chart and select Format Axis.

3. Click in the Maximum setting under the Axis Options in the Format Axis dialog box.

4. Click in the input box next to the Maximum setting. Remove the 1.6 and enter the number 5.0.

5. We will not be studying the behavior of supply and demand beyond a $5.00 price point, so there is no need to extend the Y axis beyond this point.

6. Click in the input box next to the Major Unit setting and change the value to 0.5 if not already set 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.

7. Click the X button at the top of the Format Axis dialog box.

8. Click anywhere along the X axis of the chart.

9. 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.

10. 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.

11. Click the Fixed option next to the Maximum setting under the Axis Options in the Format Axis dialog box.

12. Click in the input box next to the Maximum setting. Remove the 500.0 and enter the number 450.0. The number of units supplied or demanded will not exceed 450 based on the price points in our study. There is no need to extend the X axis to 500.

13. Click the Fixed option next to the Major Unit setting under the Axis Options in the Format Axis dialog box.

14. 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.

15. Click the Close button at the bottom of the Format Axis dialog box.

16. 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

Follow-along file: Continue with Excel Objective 4.00. (Use file Excel Objective 4.16 if starting here.)

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 you can use to plot additional points. The following steps explain how to accomplish this:

1. Set the value in cell C14 on the Supply & Demand worksheet to14.

2. Click anywhere in the chart area of the scatter chart to activate it.

3. Click the Add Chart Element button in the Design tab of the Chart Tools and select Trendline from the drop-down menu. Select the Linear Trendline option from the dropdown 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.

5. Figure 4.54 “Adding a Linear Trendline” shows the scatter chart after adding a linear trendline.

Notice that the line goes through only two points on the demand line. This indicates that this trendline may not be a good fit for the line that has been created on the chart.

6. Finding the right shape for a trendline may require trying a few different options. As shown in Figure 4.54 “Adding a Linear Trendline”, the linear trendline is not a good fit for the shape of the demand line. The remaining steps will demonstrate how to remove a trendline and access more trendline options:

7. Click the Trendline button in the Layout tab of the Ribbon. Select the None option from the drop- down list. This removes the trendline from the chart.

8. Click the Trendline button in the Layout tab of the Ribbon again. Note: You must remove a trendline because clicking on other trendlines will add them to your chart with the one you already have created. This time, select More Trendline Options from the drop-downlist.

9. Select the Demand option from the Add Trendline dialog box and click OK. This opens the Format Trendline dialog box.

10. Select the Power option from the Format Trendline dialog box.

11. 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”).

12. Click the Close button at the bottom of 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 plot area of the chart. We can use the equation to calculate the price for each quantity value substituted for X. For example, if the number 150 is substituted for X in the equation, the result is a price of $2.59. Based on the values used to create the chart, this result appears to be accurate.