How To Create Dynamic Charts Using Filtered Tables In Excel

Did you know that your charts will dynamically adjust as you filter the underlying data? Let’s see how:

You can download the file here to follow along. If you see a preview, look for the download button in the upper right hand corner.

Here I have a simple, properly structured data range:

DC1

I want to create a chart and have it automatically modify as I filter my data. The first think that I want to do is convert my data range into an Excel Table. There are many benefits of using tables rather than data ranges. You can view my post on the benefits of Tables here.

To do so, all I need do is click anywhere in my data range and use the keyboard shortcut Ctrl + T. A dialog box will appear with my range defined and a check box in “My table has headers”. Click OK and now I have a proper table in Excel:

DC2

One of the benefits of using tables is the automatic inclusion of filters.

Next, I want to create a chart from my data. The simplest way to create a basic chart in Excel is to highlight the data that you want included in your chart and use either of the following keyboard shortcuts:

Alt + F1 – if you want the chart on the same worksheet
F11 – if you want the chart on a new worksheet

I chose columns A and C:F for my chart and used Alt + F1:

DC3

So now, every time I filter my table, the chart will dynamically adjust to that data:

DC4

Try it with different data and various chart styles and see how it comes out.

Happy Excelling!

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

4 Responses to How To Create Dynamic Charts Using Filtered Tables In Excel

  1. Marik January 9, 2018 at 9:50 am #

    What about if I already have a set of data in the style of table (but not a “table”). Can I still make this work?

    • Michael Rempel January 9, 2018 at 7:06 pm #

      Yes, it should work with a standard data range with filters.

      • Marik January 10, 2018 at 5:22 am #

        So. I have a data range which is linked to an array of sorts display figures (sort of “splits up” the data) and this intermediate array is linked to a graph. So data range — data splitting array — graph.

        The graph is currently setup to display all date from the range. However it is actually made up of different groups of like data. I now want to filter the source data so that the graph displays only whats filtered from the range so that I can do: filtered data range — filtered data splitting array — graph showing required groups corresponding to the split.

        Does this make sense?

        Thanks.

Leave a Reply