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:
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:
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:
So now, every time I filter my table, the chart will dynamically adjust to that data:
Try it with different data and various chart styles and see how it comes out.