When it comes to data
analysis, let users decide how they want to view their data in an Excel chart.
For example, you have a list of the monthly total sales from three stores over
the past year in cells A6:E17, with the months listed in column A, and the
corresponding sales for Stores One, Two, and Three in columns B, C, and D,
respectively. You can create a chart for the data that compares monthly sales
for the entire year; or, you can create a combo box that lets the user decide which
month’s data to chart. Here’s how to set up the combo box:

  1. Enter the following formula in cell B2:
    =INDEX(A6:A17,$A$2)
  2. In B1 enter Month, in C1 enter Store One, in D1
    enter Store Two, and in E1 enter Store Three.
  3. Copy the formula in B2 to C2:E2.
  4. Select View | Toolbars | Forms.
  5. On the Forms toolbar, click the Combo Box
    control, and click and drag where you want the control to appear.
  6. Right-click the Combo Box and select Format
    Control.
  7. Under the Control tab, enter A6:A17 in the Input
    range text box.
  8. Enter A2 in the Cell Link text box and click OK.
  9. Use the Chart Wizard to create a chart for the
    series B1:E2.

When the user selects a month
in the combo box, the number for the month appears in A2, while the
corresponding sales for that month displays in C2:E2 and the chart.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic’s free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.