The following dataset will be used to create dynamic charts in Excel using data filters.
Method 1 – Filtering the Dataset to Create Dynamic Charts
Steps:
- Select the whole dataset (B4:D12).
- Go to the Data tab.
- In Sort & Filter, select Filter.
- The dataset headers will have filters.
- Select the dataset again.
- Go to the Insert tab.
- In Charts, select Recommended Charts.
- The Insert Chart box will open.
- Select a chart for the dataset. Here, the clustered column chart.
- Click OK and the chart will be displayed.
- Modify the graph.
This chart can be used as a dynamic chart.
Click the filter button in B4.
Uncheck years 2016-2018. Click on OK.
The graph will automatically change.
Read More: How to Create Dynamic Chart with Multiple Series in Excel
Method 2 – Using Chart Filters
Steps:
- Create a chart by selecting the whole dataset and choosing Recommended Charts from the Charts group in the Insert tab.
- Then select a type of chart in Insert Chart. Here, the clustered column graph.
- Click OK and the chart will be displayed.
- Click the chart. Three additional features will be displayed.
- Click Chart Filters to see the available filtering options.
- You can modify your parameters. Here, “Revenue” and the years 2014-2017 were unchecked.
- Click Apply. Your graph will change.
Read More: How to Create Chart with Dynamic Date Range in Excel
Method 3 – Utilizing an Excel Table
Steps:
- Select the dataset or a cell in the dataset.
- Go to the Insert tab.
- In Tables, select Table.
- Click OK in Create Table. Make sure that My table has headers is checked.
- The dataset is converted to an Excel table.
- Create a chart by selecting the whole dataset and choosing Recommended Charts from the Charts group in the Insert tab.
- Select a type of chart in Insert Chart. Here, the clustered column graph.
- Click OK and the chart will be displayed.
- Use the data filters from the table headers to change the chart parameters. For example, click the filter in B4.
- Select your parameters. Years 2018-2021 were unchecked.
- Click OK and the chart will automatically change.
Read More: How to Create a Dynamic Chart in Excel Using VBA
How to Create Dynamic Charts Without Data Filters in Excel
To create dynamic datasets and charts without data filters in Excel, utilize the INDEX and MATCH functions.
Steps:
- Select I4.
- Go to the Data tab.
- In Data Tools, select Data Validation.
- In the Data Validation window, select Settings.
- In Allow, select List.
- In Source, enter Expense, Revenue.
- Click OK.
- A data filter will be created in the cell.
- Create a header for the years in F4.
- Select G4 and enter the following formula.
=I4
- Press Enter.
- Copy all the years to the range F5:F12.
- Select G5 and enter the following formula.
=INDEX($C$5:$D$12,MATCH(F5,$B$5:$B$12,0),MATCH($G$4,$C$4:$D$4,0))
- Press Enter.
- Select the cell again and drag down the fill handle to fill the rest of the cells with the formula.
- Select F4:G12 and go to the Insert tab.
- In Charts, select Recommended Charts.
- Select a chart in Insert Chart. Here, the column chart.
- Click OK and the chart will be displayed.
- Select I4 and change the option to Revenue.
- The chart will automatically change.
Download Practice Workbook
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!
