Creating a Dynamic Chart

A chart is linked directly to the Data Range specified in the Source Data step of the

Chart Wizard. If any points in this range of data are modified, the chart is automatically

updated to reflect a new corresponding data point. In reality, we could specify an empty range as the Data Range if we created the chart before the data was entered into the spreadsheet. Then, the chart would be updated as new data was entered. In VBA, we will see how to perform these actions automatically so that the user can observe a chart being created one data point at a time. However, before we learn any VBA coding, we should use some Excel functions we have already learned to create a dynamic chart. We will use three main Excel concepts to create a dynamic chart: defining names, the OFFSET function, and the COUNT function. We will first use the OFFSET and COUNT functions to create some range names and then set the Series of the chart to these dynamic ranges. We will use the following example to demonstrate this process. Figure 5.17 features production data that records the “Units Sold” for each month of the past year. We have created a Scatter Chart for this data using the Chart Wizard. In this figure, the Source Data is currently linked to the range B4:C15.

Figure 5.17 The original data and chart; the chart is currently not dynamic.

Our first step is to create two dynamic ranges: one for “Months” and one for “Units Sold.” By creating these dynamic ranges, we ensure that our range names include new data points as they are entered into either column of the table. We therefore use the OFFSET and COUNT functions in the following manner:

=OFFSET(initial_data_location, 0, 0, COUNT(entire_column), 1)

The rows_to_move and columns_to_move parameters are set to 0 because we are only interested in the column in which our reference_cell ( = initial_data_location) is located. The width is again set to 1, since we are interested only in one column. The height parameter is found using the COUNT function. The COUNT function reviews the entire

column of the relative data and counts how many cells have numeric values. So, the

height of our range becomes dynamic as the amount of numeric values in the column

increases.

We now create two dynamic ranges, named “Months” and “UnitsSold,” with the Define

Name option. In the Refers To window of this dialog box, we use the above functions as

follows:

=OFFSET(B4, 0, 0, COUNT(B:B), 1) for Month =OFFSET(C4, 0, 0, COUNT(C:C), 1) for UnitsSold

In the Define Name window, we also use sheet names and absolute referencing (see Figure 5.18).

Figure 5.18 Defining the dynamic ranges using the OFFSET and COUNT functions.

Now, we can simply use these dynamic range names to define our data range. We right- click on the already-created chart and choose Source Data from the list of options. We then go to the Series tab and modify the X Values and Y Values windows. Using the workbook name, we place the dynamic ranges in their corresponding windows as follows (see Figure 5.19):

=WorkbookName.xls!Month

Figure 5.19 Entering the dynamic ranges as the corresponding series values.

Now, if we enter data for sequential months in the data table, the chart is updated automatically. In Figure 5.20, we have added data for months 13 to 16; without modifying the data range again, the chart is updated to reflect the values of these new points. We have therefore created a dynamic chart.

Figure 5.20 The dynamic chart is now created. As new data values are entered, the

chart is automatically updated.

5.5 Summary

¾ Excel Charts allow you to illustrate your data in order to perform better analysis. When using charts, you should understand Data Ranges, Ranges, and Series. ¾ A Data Range is the group of cells selected to create a chart. A Range is any

group of cells that forms a row, column, or area.

¾ Series are various sets of data. You can modify the number of series and the data sources for each series using the Chart Wizard.

¾ There are four basic steps in the Chart Wizard: Step 1: Select the Chart Type and options.

Step 2: Verify or change the Data Range and define all Series. Step 3: Determine the Chart Options.

Step 4: Choose the location of the chart.

¾ A chart can be modified after it is created by right-clicking on the chart or different parts of the chart. You can change basic settings as well as formatting.

¾ The Data Range and Series can be modified to further manipulate the presentation of data.

¾ A dynamic chart can be created using the OFFSET and COUNT functions to create dynamic ranges used as Series in the Source Data.

5.6 Exercises

5.6.1 Review Questions

1. List ten different types of charts that can be created in Excel. 2. Why is it useful to create charts in Excel?

3. What is the Data Range of a chart?

4. How does one begin using the Chart Wizard?

5. What are the four main steps used to create a chart with the Chart Wizard? 6. How many data series are needed in order to create a stock chart? In what order

should the series been arranged? Give an example of when one can use this chart type. What are the six tabs found on the Chart Options form?

7. How do you name a series?

8. What are the two main options for locating a chart?

9. How can the source data of a chart be modified after the chart has been created? 10. What parts of the chart can be formatted?

11. Can the scale of Gridlines be changed? 12. What is a dynamic chart?

13. What is a dynamic range?

14. How do you change the color of a series of data on a chart?

5.6.2 Hands-On Exercises

1. Refer to the spreadsheet created in Chapter 4, Problem 8.. Using the Chart Wizard, create a line chart that plots velocity and average acceleration versus time. Give each series an appropriate label and place the chart legend at the bottom of the table. Title the chart “Velocity and Acceleration of a Moving Object” and label the x- axis “Time (s).”

2. Using the set S defined in Chapter 4, Problem 2, create the following series plots of the values in S:

• A scatter plot.

• A line plot with nodes. • A line plot without nodes.

3. Create a table of x- and y-values for the equation y = ex _{using the following values for}

x: 0, 1, 2, 4, 6, 8, 10, 12, 14 and 16. Create a scatter chart of the data with the points

connected by a smooth line. Locate the chart on the spreadsheet.

a. Describe the curvature of the line. Is it convex up or convex down? b. Change the y-axis to a logarithmic scale. Is the line concave up or

concave down?

4. The table below is used to monitor the performance of workers at a manufacturing plant. Enter the table into a spreadsheet. Create a bar graph, scatter chart, and pie chart of the data in the table using the values in the “Worker Name” column as the values along the x-axis. Then try changing values in the data table and notice how the graph and charts change accordingly.

Worker

Name Worked Per Week Average Hours Average Units Output Per Week Average Units Not Meeting Specifications Per Week

Steve 40 100 5

Bob 36 95 2 Tom 41 105 6 Michael 41 100 4 Luther 45 115 9 Robby 41 105 5 Greg 46 125 7 Adam 35 90 1 Kenneth 39 100 4

5. In the chart created in problem 4, perform the following: a. Change the color of the data series.

b. Change the scale of the y-Axis. c. Display all data points.

6. Using your solution to Chapter 4, Problem 3, plot the heights of a cylinder versus the radii for the given volume. To best depict the data, display the results in the form of cylinders of various heights. Label the x-axis :Radius (ft)” and the series “Height.” Title the chart “Height of a Cylinder with Volume = 10 ft^3.”

7. A materials engineer is comparing various properties of ceramics to determine which is the most appropriate for use in a car engine. The ideal material will exhibit a high fracture toughness (in MPa•m1/2_{), a high tensile strength (in MPa), and a low thermal} conductivity (in W/m•K). The engineer tests each of the materials and obtains the following results:

• Materials A, B, C, D, E, F, and G have fracture toughnesses of 3, 3.5, 3.2, 2.7, 3.9, 3.2 and 2.6 MPa•m1/2_{, respectively.}

• Materials A, B, C, D, E, F, and G have tensile strengths of 270, 253, 285, 246, 279, 285 and 260 MPa, respectively.

• Materials A, B, C, D, E, F, and G have thermal conductivities of 29, 33, 37, 41, 25, 40 and 27 W/m•K, respectively.

For this application, tensile strength is the most important property, followed by fracture toughness and then thermal conductivity. Use Excel to create a chart that illustrates this data. Select the most appropriate chart type and justify your selection. Your chart should assist the engineer in making a final material selection.

8. The table below displays the high, low, and closing prices for stock XYZ on the 15th of each month in the year 2002. Create a stock chart to depict this information. Display the date on the x-axis and the price on the y-axis. Title the chart “Stock Prices for XYZ in 2002.” Do not show a legend.

High

Price Price Low Closing Price

1/15/02 1.75 1.45 1.52 2/15/02 1.75 1.43 1.5 3/15/02 1.8 1.49 1.59 4/15/02 1.77 1.5 1.63 5/15/02 1.84 1.55 1.71 6/15/02 1.9 1.55 1.73 7/15/02 1.88 1.6 1.61 8/15/02 1.75 1.57 1.59 9/15/02 1.7 1.52 1.52

10/15/02 1.65 1.45 1.5

11/15/02 1.65 1.45 1.47

12/15/02 1.6 1.35 1.42

9. Given that Series 1 equals the set {56, 49, 52, 45, 40, 55, 58} and Series 2 equals the set {23, 24, 32, 26, 26, 19, 29}, create the following 3-dimensional charts to plot the two series:

• A surface chart

• A 3-dimensional line chart

• A stacked 3-dimensional area chart

10. In the above problem, which chart was best suited for the data? What scaling limitations, if any, did you find for different chart types? What happens if you plot by rows instead of columns?

11. Now suppose that the two series given in Problem 9 represent the high and low temperatures for each day of a week in February. Create a 3-dimensional chart to display these temperatures. Select the chart type you think is most appropriate to depict this type of information. Include a data table of the high and low temperatures with the chart.

12. A team of industrial engineers is conducting a productivity study of workers at a manufacturing plant. By observing workers at random times throughout the day, the team is able to use statistical tools to estimate the percent of time the workers spend performing different activities. The categories “Direct Work,” “Indirect Work,” “Travel,” “Breaks,” and “Other” comprise the workers' activities. The table below displays the team’s results in terms of the percent of time workers were observed doing each activity for one week:

Direct Work Indirect Work Travel Breaks Other Monday 0.35 0.25 0.1 0.2 0.1

Tuesday 0.42 0.23 0.15 0.15 0.05

Wednesday 0.4 0.17 0.2 0.21 0.02

Thursday 0.32 0.15 0.14 0.27 0.12

Friday 0.27 0.13 0.21 0.31 0.08