|
|
|
Interactive Charting by Combo Box or Checkbox.Would you like to let someone decide what series to plot on your chart? Here are examples showing how to use a Combo Box (Dropdown Box) or Checkboxes to let the user decide what series to display. My samples both use the following data in A5:D9.
Y1 Y2 Y3
1 3 9 5
2 3 4 7
3 4 5 9
4 5 5 6
Select One Series Using a Combo Box
Put Y1, Y2, and Y3 in a 3 cell high x 1 cell wide range; I used H2:H4. If the Forms Toolbar is not visible, right click on a commandbar, in between buttons, and select Forms from the pop up menu. Click the Combo Box (or List Box) button, and draw your control on the worksheet. Right click on the control and select Format from the pop up menu. Set the Input Range to $H$2:$H$4 and the Cell Link to $H$1. In E5 enter this formula: =OFFSET(A5,0,$H$1) Drag the small black square in the lower right of the highlighted cell downward to copy this formula into the range down to E9. Select any of the options in the Combo Box to fill E5:E9 with data; in fact try a few selections to make sure the cells are changing. Select A5:A9, and hold down Ctrl while selecting E5:E9, so a discontiguous region is selected. Start the Chart Wizard, and make whatever kind of chart you need, with whatever formatting looks nice. When you are done, select any of the items from the drop down to change the chart. Select Any Combination of Series Using Checkboxes
If the Forms Toolbar is not visible, right click on a commandbar, in between buttons, and select Forms from the pop up menu. Click on the Checkbox button, and draw a control above Y1 in the worksheet. Repeat for Y2 and Y3. Right click on each control and select Format from the pop up menu. Set the Cell Link to a convenient cell; I used B3:D3 which line up above the Y1-Y2-Y3 labels in B5:D5. In E5 enter this formula: =IF(B$3,B5,"") Drag the small black square in the lower right of the highlighted cell to the right to fill E5:G5 with this formula. In E6 enter this formula: =IF(B$3,B6,NA()) Drag the small black square in the lower right of the highlighted cell to the right to fill E6:G6 with this formula, then drag it down to fill E6:G9 with the formula. Try out the checkboxes to see how the data in E5:G9 changes. If the checkbox is checked, the appropriate cell in B3:D3 is True, and the formula returns the data from the original range. If the check box is unchecked, the cell in B3:D3 is False, which leads to blanks in the header row and #N/A values under that. Select A5:A9, and hold down Ctrl while selecting E5:G9, so a discontiguous region is selected. Start the Chart Wizard, and make whatever kind of chart you need, with whatever formatting looks nice. When you are done, check any combination of Checkboxes to change the chart. ChartByComboBoxOrCheckBox.zip contains a zipped Excel workbook with both of these examples. ChartAgainstStandard.zip contains a zipped Excel workbook showing how to use a listbox or combobox to select which sheet contains data for a charted series. ChartByListBox.zip contains a zipped Excel workbook showing how to use two listboxes to select which sheet and which column on that sheet contain data for a charted series. |
Peltier Technical Services, Inc.Excel Chart Add-Ins | Training | Charts and Tutorials | PTS BlogPeltier Technical Services, Inc., Copyright © 2017. All rights reserved. |
