Interactive charts – those that allow users to select the data shown – are a staple of Excel- based dashboards. With such charts, you can easily update charts, change data ranges, and make changes to data series shown in charts, all without necessarily changing the underlying structure of the chart. Examples of interactive charts include:
• Charts that automatically expand and contract to reflect varying volumes of data,
• Charts that limit the amount of data displayed (for example, the last twelve months),
• Charts that allow you to select the amount of data plotted using scrollbars, and
• Charts that use check boxes to allow you to control which data series appear.
Using Check Boxes to Control Which Data Series Are Plotted
To the extent this type of interactivity can be anticipated and incorporated into a chart, those responsible for creating and disseminating dashboards throughout an organization can do so much more efficiently. Figure 60 shows an interactive chart that uses check boxes to display up to three data series.
Figure 60 - Interactive Chart Using Check Boxes to Display Data Series
The chart in Figure 60uses check boxes to allow you to select which data series to plot. Check boxes are form controls that return True when checked and False when unchecked. By defining names based on the True and False conditions of the cells linked to the checkboxes, it is possible to create interactive charts that plot only those data series that have been checked.
Using Dynamic Defined Names to Plot Varying Amounts of Data
Dynamic defined names can be used to adjust the data range and X-axis labels automatically as data is added or removed. When creating dynamic defined names for charts, keep in mind two important considerations.
1. The range should not include the data labels at the top of the data columns.
2. Create at least two dynamic ranges for each chart, one for the data and one for the X- axis labels.
Figure 61 shows a daily sales chart based on dynamic defined names. As additional rows for sales results append to the data range, the new data points will automatically display in the chart. Follow along as your instructor explores the setup of the dynamic defined names and adds some new data points.
Figure 61 - Daily Sales Chart with Dynamic Defined Names for Input
Controlling Date Ranges with a Spinner Form Control
Earlier in the course, we discussed dynamic defined ranges and form controls. We will now use both to create dynamic charts.
Form controls are useful for giving you control over the amount of data that is included in a chart. For example, it may be desirable in one instance to plot the latest twelve months of ending balances for accounts receivable and in another instance to plot only the last three months of data. Unlike creating charts that automatically update as the data expands or contracts, a scenario such as this is facilitated by controlling the size of the data range.
With dynamic defined names, the data ranges automatically expand or contract as the volume of data changes. In this case, the data range expands and contracts based on user input. Form controls give the creator of a chart much greater control over the input of a chart user. In our example, a form control is linked to a cell that is an input to an OFFSET function. The OFFSET function controls the vertical size of the data range, which is a defined input to the chart. As a user manipulates the control, the size of the data range expands or contracts, and the chart changes to reflect the data range. Figure 62 shows a chart that uses a spinner form control to specify the number of months of data to display.
Figure 62 - Chart Using a Spinner to Control the Chart Range
Let's examine some of the steps to build a chart using a spinner to control the chart data range. 1. Enter the data in columns A and B as shown. Label the columns accordingly.
2. Narrow column C for use as a spacer. Enter the label Number of Months to Display: in cell D1. Widen the column sufficiently so that the label fits within the cell borders. 3. Now, create three defined names.
a. The input range for the spinner control is easy. Place the cursor in cell E1 and then type Input in the Range Box on the toolbars to create the defined name "Input."
b. The dynamic defined names for the chart data range and X-axis range are a bit more difficult because they must be defined so that their size may be altered by the value in Input, the input cell for the spinner control. These defined names must be defined by formula, not cell references.
i. In the New Names dialog box, type in ARBalance in the Names. In the Refers to box, enter the following formula and click OK as shown in Figure 63.
Figure 63 - Creating Defined Names by Formula
ii. Create another defined name for Months that refers to the following formula.
=OFFSET($A$3,0,0,Input,1)
4. Add and format the spinner form control. Locate the spinner just to the right of cell E1. Format the spinner with the values shown in Figure 64.
5. Create a new column chart in Excel. Add ARBalance as the range for the data series and Months as the range for the X or Horizontal axis.
The workbook name must be entered as part of the named dynamic range. In this case, the workbook was named "Spinner w Chart." Make sure to substitute the name of your workbook in the formulas entered.6. Enter Accounts Receivable Balance as the Chart Title.
All we need to do is add formatting, then resize and reposition the chart to complete our task. Here are a few tips for making the task easier.
• Resize the chart by highlighting the chart and then dragging the lower right-hand corner.
• Reposition the chart by highlighting the chart and then dragging the chart to the desired location. If you want to snap the chart to specific cells for alignment, hold the ALT key down while dragging the upper left-hand corner of the chart. Drop the corner in the desired cell. Repeat the process on the lower right-hand corner. When you are finished, your chart will align perfectly with the worksheet.
• Format the X-axis labels as Dates such that they display only the month and year. Choose a 10-pt boldface font. Also, consider reversing the date order so that the most recent month appears as the first column in the chart.
• Format the Y-axis labels to the Accounting Format with zero decimal places. Choose a 10-pt boldface font. Enter the following values for the scale.
• Minimum – 2,500,000
• Maximum – 3,500,000
• MajorUnit – 250,000
• Adjust the font size for the chart title and choose appropriate colors for the chart area, plot area, and data series.
With our chart complete, use the spinner to dial in the number of months to display in the chart.