Excel Video 96 picks up where we left off in Excel Video 95. First, notice how changing the background color of the check boxes makes the chart look better and allows us to remove the legend, which makes the plot area bigger. It’s also helpful to remove the legend since the legend lists series that may not be shown on the chart.
As we get into the detail of Excel Video 96, watch how it’s helpful to split the screen horizontally. You can see the data on the top half of the screen and the names on the bottom half of the screen at the same time.
Watch how I define a name for each series of data in the chart (DrA, DrB, etc.) and also name the cells that are tracking whether the value is true or false (ShowA, ShowB, etc.) that we turned on and off with the check boxes in Excel Video 95. Note that I defined a blank range of cells (G4:G8) and named that Blank. Now I need to set up SeriesA, SeriesB, etc. SeriesA is simply an IF formula that checks to see if ShowA is true. If ShowA is true, Excel plots DrA on the chart. If ShowA is false, Excel plots Blank on the chart, and since Blank is blank cells, no data is shown on the chart for SeriesA. The same logic is used for B, C, D, and the Specialty Average.
The last step is to use the Select Data Source window to assign SeriesA to the first series, SeriesB to the second series, etc. Now every time the chart updates, Excel will look to see if the ShowA, ShowB, etc. cells are true or false and display the series accordingly.
I typically wouldn’t go to this much work for a chart I’m only planning on using one time unless I really needed to impress whoever was using the chart. If I had a monthly commitment, such as a board meeting or a series of reports to partners or clients, or had physicians with access to run the data themselves, I’d gladly invest the time up front to make the chart as user-friendly and powerful as I can.
