Excel – Filtering A Graph of Series Data In Excel

chartsmicrosoft excelmicrosoft-excel-2010

So I currently have a line graph with 48 different lines, comparing 48 different series of data. The series are generated by changing three variables: One with two possible values, one with 4 possible values, and one with 6 possible values.

One column contains each series, and 3 rows of headings indicate the various combinations of of the 3 different input variables. (Sorry that I can't post a picture, but I just jumped over to this site from stack overflow, and don't yet have the reputation.)

What I'd like to do is create a line graph that I can then filter on the fly for various combinations of the input variables. (Pivot chart style stuff) The problem is that the data isn't, and can't be put into (I don't think) a format that is amenable to excel's pivot functionality. I'm wondering if there's any easy way to do this, without having to break out the VBA editor.

Best Answer

This is pretty easy to do (without VBA), but you'll need a helper row to get it done.

  1. Organize your data into an Excel Table (this just makes referencing your data a bit easier).
  2. Create one additional helper row (usually, I do this in the first row, above the data table).
  3. In the helper row, first column, use Data Validation > List to select the name of each series. This will create a drop-down for you to quickly select any one of your 48 data series.
  4. In the helper row, second-fourth columns, use =VLOOKUP to get the three data points from your chosen row.
  5. Create a Chart and add each of the 48 data series (I'll usually format to a light gray, so they're visible, but not overemphasized).
  6. Add your helper row data as a 49th data series, and format this to highlight.

Now, as you change your selection in the data validation drop down, it will automatically update the chart by highlighting your series of interest.

filtered chart

Note: It doesn't show in this screen grab, but the row above the chart's title row is the helper row. The first column (above name), is a data validation cell, and when selected, activates a drop-down window with all the values from the Name column in the table below.

Related Question