Categories
Excel

Create Dynamic Chart using Named Ranges and a Combo Box

To make your chart truly interactive, you can use one or more dynamic ranges in your chart and then use a drop-down list from the Forms Controls to reveal the figures your readers want to peruse.

Starting with some data such as that shown in the following figure, you will add a dynamic range that will be used as a data source for the chart.

The dynamic range will be linked to a drop-down list you can use to view one student’s test results from those of a group of students. You will use the drop-down list to select the name of the student whose results you want to view.

Use the formula =AVERAGE(B6:B11) in cell B12 and copy it across to cell F12, as shown in the figure:

Create Dynamic Ranges

Create a dynamic range by selecting Formulas » Define Name, and call it STUDENTS. In the Refers To: box, type the following:

=OFFSET($A$5,$G$6,1,1,5)

Create another dynamic range called STUDENT_NAME, and in the Refers To: box, type the following:

=OFFSET($A$5,$G$6,0,1,1)

The use of the cell reference $G$6 in the OFFSET formula forces the referenced ranges for STUDENTS and STUDENT_NAME to expand both up and down as the number in $G$6 changes.

Create Clustered Column Chart

Now create a clustered column chart using the range A11:F12 as shown in the figure above.

Next, edit the chart data to use the dynamic named ranges:

  1. Right click on the chart and click Select Data from the context menu. The Select Data Source dialog box will be displayed.
  2. Select the first series (Patel) from the Legend Entries (Series) tab and click Edit button.
  3. In the Edit Series box:
    • Enter Workbook_name!STUDENT_NAME in the the Series name: box.
    • Enter Workbook_name!STUDENTS in the
      Series values box.
    • Click OK to close the Edit Series box.
  4. Now, click the Edit button from the Horizontal (Category) Axis Labels tab and select the top heading B5:F5 and press OK.

Inserting Combo Box

At this point, you need to insert a Combo Box from the Forms Controls:

  1. Go to the Developer tab and select the Combo Box icon from the Insert drop-down menu to insert it on the sheet.
  2. Right-click on the combo box and choose Format Control from the context menu.
  3. Enter $A$6:$A$11 for the input range and enter $G$6 for the cell link.
  4. Click OK.

Clicking the downward-pointing arrow on the Combo Box shown in the figure will change the name of the student and show his test results:

Charts and Graphs