Excel has simple ways to create dynamic charts within spreadsheets. Dynamic charts allow you to change parameters to view the exact portion you need without changing datasets on the already existing charts. In this tutorial, we will see different methods to create dynamic charts in Excel using data filters.
How to Create Dynamic Charts in Excel Using Data Filters: 3 Easy Ways
There are three ways you can create dynamic charts using data filters in Excel. We can simply filter datasets to change charts in the first place. Additionally, we can convert them to tables and create dynamic datasets- this will allow us to use additional table features in the dataset. Identically, we can use the chart filters which can filter data options to create dynamic charts in Excel. Not to mention, we can create new datasets with the help of functions in data filters which will, in turn, create a dynamic dataset on the Excel spreadsheet. Each method has different usages post-modifications. Go through the methods to find the one suitable for you.
For every demonstration, we are going to use the following dataset to create dynamic charts in Excel using data filters.
1. Filtering Dataset to Create Dynamic Charts
In the first method, we are going to insert data filters in the headings of our dataset. Then plot charts. This will create a dynamic chart because you can change different portions of the data from these data filters. To see the detailed guide, follow these steps.
Steps:
- First of all, select the whole dataset (the range B4:D12).
- Then go to the Data tab on your ribbon.
- After that, select Filter from the Sort & Filter group.
- Now, the dataset headers will have filters with them.
- Next, select the dataset again (or you can select a cell from the dataset or a portion of the dataset this time).
- Then go to the Insert tab on your ribbon.
- After that, select Recommended Charts from the Charts group.
- As a result, the Insert Chart box will open up.
- Now select the desired chart for the dataset. Here, we are selecting the clustered column chart for demonstration.
- After clicking on OK, you will automatically have a chart on your spreadsheet.
- Now, modify the graph to make it more presentable.
This chart can already be used as a dynamic chart. We can eliminate portions of the chart by selecting the required portions we need from the data filters.
For example, click on the filter button in cell B4.
And deselect years 2016-2018 as shown in the figure. Then click on OK.
The graph will now automatically change.
This way you can create dynamic charts in Excel using data filters.
Read More: How to Create Dynamic Chart with Multiple Series in Excel
2. Using Chart Filters
When you select charts in Microsoft Excel, the program provides three additional features that help us to modify those charts. These features are – Chart Elements, Chart Styles, and Chart Filters. The third option, Chart Filters, also can work as a data filter and help us create dynamic charts in Excel.
Follow these steps to see how we can utilize these features and make the chart dynamic.
Steps:
- Similar to the previous method, create a chart by selecting the whole dataset and selecting Recommended Charts from the Charts group in the Insert tab.
- Then select the type of chart you want from the Insert Chart. We are selecting the clustered column graph for the demonstration.
- After clicking on OK, the chart will appear on the spreadsheet. Then modify it to your liking.
- Now click on the chart. You can find three additional features appearing on the right of the chart.
- At this instant, click on the Chart Filters button and you will have all the available filtering options.
- You can modify your parameters in the chart now by selecting/deselecting them from this list. For example, we are deselecting the “Revenue” from Series and the years 2014-2017.
- Once you are done with the selection/deselection, click on Apply. Your graph will change according to the parameters you have set.
This is another way you can create dynamic charts using data filters in Excel.
Read More: How to Create Chart with Dynamic Date Range in Excel
3. Utilizing Excel Table
Instead of merely adding data filters on top of datasets, we can convert them to Excel tables. This will automatically create data filters on table headers. If we create a chart after that and change values using data filters, the chart will change as a dynamic one. Converting datasets to Excel tables instead of adding mere filters helps us utilize table tools for the dataset. Such as adding rows to the table will automatically add new parameters to the chart. The dynamic of the chart will be more flexible in this way too.
Follow these steps to see how this method works.
Steps:
- First, select the dataset or a cell in the dataset.
- Then go to the Insert tab on your ribbon.
- After that, select Table from the Tables group.
- Then click on OK in the Create Table Make sure that the My table has headers option is checked.
- Hence, the dataset will now convert to an Excel table.
- Similar to previous methods create a chart now by selecting the whole dataset and selecting Recommended Charts from the Charts group in the Insert tab.
- Then select the type of chart you want from the Insert Chart. We are selecting the clustered column graph for the demonstration.
- After clicking on OK, the chart will appear on the spreadsheet. Then modify it to your liking.
- Finally, you can now use the data filters from the table headers to change the chart parameters. For example, click on the filter in cell B4.
- As a result, a drop-down will appear. Select the parameters you desire for the moment from here. We are deselecting the years 2018-2021 for demonstration.
- After clicking on OK you will notice, that the chart has automatically changed to this.
This way you can create dynamic charts in Excel using data filters by converting datasets to tables.
Read More: How to Create a Dynamic Chart in Excel Using VBA
How to Create Dynamic Charts Without Data Filters in Excel
You can also create dynamic charts by first creating a dynamic dataset from the original dataset. This method can be a bit unorthodox but it can work quite the same as any other one. Additionally, you can follow this method to create dynamic datasets too with the help of data filters.
To create these types of dynamic datasets and charts using data filters in Excel we need to utilize the INDEX and MATCH functions. Follow these steps to see how we can do this.
Steps:
- First of all, select cell I4.
- Then go to the Data tab on your ribbon.
- After that, Select Data Validation from the Data Tools group.
- Thus the Data Validation box will appear. Now select the Settings tab if it isn’t selected already.
- Then under Allow option, select List.
- After, write Expense, Revenue under Source.
- Then click on OK.
- As a result, a data filter will be created in the cell. Select an option for now.
- Now make a header for the years in cell F4.
- Next, select cell G4 and write down the following formula.
=I4
- After that, press Enter.
- Now copy all the years to the range F5:F12.
- Now select cell G5 and write down the following formula.
=INDEX($C$5:$D$12,MATCH(F5,$B$5:$B$12,0),MATCH($G$4,$C$4:$D$4,0))
- After that, press Enter.
- Now select the cell again and click and drag the fill handle icon to the end of the column to fill the rest of the cells with the formula.
- Next, select the range F4:G12 and go to the Insert tab on your ribbon.
- Then select Recommended Charts from the Charts group.
- After that, select the chart you want from the Insert Chart box. We are selecting the column chart for the demonstration.
- Once you press OK you will have the chart on your spreadsheet. Modify it to your liking now.
- You can now select cell I4 and change the option to Revenue.
- And the chart will change automatically as a result.
This is one unique way you can create dynamic charts in Excel without using data filters.
Download Practice Workbook
Conclusion
These were all the methods we can use to create dynamic charts using data filters in Excel. Hopefully, you will be able to create dynamic charts on your own using filters. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know in the comments below.
Related Articles
- How to Make Dynamic Charts in Excel
- Create a Dynamic Chart Range in Excel
- How to Create Min Max and Average Chart in Excel
- How to Dynamically Change Excel Chart Data
- How to Create Dynamic Excel Charts with Drop-Down List
<< Go Back to Dynamic Excel Charts | Excel Charts | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
