Excel: Charts that change
When you’re viewing complex data in Excel, it can help to see it more clearly if you limit what you see on the screen to what you are actually interested in seeing.
In this article I’ll explore an Excel solution that displays one of three charts and the associated data on the screen according to an option you choose from a dropdown list. The solution uses some handy Excel tools and it is driven by a simple Visual Basic macro.
Create the charts
To get started, enter some data into a range covered by A1 to D6 in your worksheet. I used data for a series of offices: North, South East, Central, Rural and Bayside in Column A and then provided information on the Sales, Items Sold and Profit in columns B, C and D. Column A and Row 1 are used to store the headings and the data is in the range B2:D6.
To create the charts, you will want one for each set of data. Select A1:B6 to and create a chart based on the data for Sales. Then select A1:A6, hold the Ctrl key and select C1:C6 and create a chart for the Items Sold. Finally, select A1:A6, hold the Ctrl key and select D1:D6 and create a final chart to show the Profit. For now, you can place these charts on the same worksheet as the data. Format the charts neatly.
To add the relevant data to each chart, select each chart in turn, right click and choose Chart Tools > Layout > Data Table and choose Show Data Table. Only add a Data Table if it is an option for that chart type - it isn't an option for a Pie chart, for example.
Create the display sheet
Return to your data sheet, select the chart for Sales, right-click it and choose Cut. Return to the display worksheet and paste the chart into cell B2. Repeat the process and add the Items Sold chart into cell B3 and the Profit chart into cell B4.
Add the dropdown list
Return to the display worksheet and click in cell B1 to create the dropdown list here. Choose Data > Data Validation > Data Validation > Settings tab. From the Allow dropdown list, choose List, make sure the In-cell dropdown checkbox is enabled and click in the Source area and type =ChartList and click Ok. This displays a dropdown list in the cell B1 which will show the three entries: Sales, Items Sold and Profit.
Now the basic display worksheet is complete you can format it by choosing View tab and disable the Gridlines and Headers checkboxes. This neatens up the display.
Add the code
Into this code dialog, type this code and then choose File > Close and Return to Microsoft Excel:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
To test the macro, click the dropdown list in cell B1 and as you select an item from it, the worksheet should change to display the appropriate chart for that data. If it doesn't, make sure you have used the same names for the column headings as you used in the macro.
How it works
The macro then checks to see if the value in cell B1 is Sales. If so, it displays row 2. If it is Items Sold, it displays row 3, and if it is Profit, it displays row 4. Any time a cell in the worksheet changes, the macro will run and check cell B1 and update the display.
(c) 2017, Helen Bradley, All Rights Reserved.