While static Excel charts are adequate for basic data analysis, they fall short when you want to explore trends, compare scenarios, and truly interact with your information. Here is where dynamic visualization in Excel comes in clutch. In this post, we will go over the techniques to transform your ordinary spreadsheets into powerful tools for data discovery. Whether you are a beginner, a data scientist, or an advanced Excel user, the tricks below will help you create interactive charts and dashboards that can respond to your needs in no time.

Methods for dynamic charts in Excel

Before we dig in, let's do a quick overview of the various methods for creating dynamic charts in Excel.

Excel tables

Suitable for beginners, this is the easiest method for creating basic dynamic charts in Excel. When you convert a database into a table, your data range becomes dynamic. As you add or remove rows, the table (and any charts linked to it) automatically expands or contracts. The only con is you have limited control over complex filtering and data manipulation. We'll start here though, for sake of example, in this guide.

OFFSET formula

The OFFSET function lets you define a dynamic range that shifts based on criteria. For example, you can create a chart that shows the last 12 months of data, even as your dataset grows. While it gives you more control over data range than tables, it can be complex to set up and may become slow with large datasets.

Dynamic Array formulas

Back in 2018, Excel announced functions like FILTER, SORT, UNIQUE, etc. to let you create dynamic arrays that automatically update. They are very powerful and flexible, allowing you to perform complex filtering without breaking a sweat. However, it requires a learning curve and may have compatibility issues with older Excel versions.

VBA (Visual Basic for Applications)

Advanced users can consider VBA for complex scenarios. It gives you ultimate flexibility and customization, and you can write code that controls your chart. It does require VBA programming knowledge and can be time-consuming to develop and maintain.

The best method will depend on your Excel skills and requirements. In the example below, we will focus on Excel tables, explore slicers, chart customization, and more.

Create dynamic charts using Excel tables

Let’s imagine you have sales data for employees. Before handing out yearly bonuses over Christmas, you want to determine how everyone has performed during the year. Once the data is in your Excel workbook, you can turn it into a table and analyze it easily. Let’s get started.

1. Open Microsoft Excel and create a new workbook to enter the data you'll need.
2. In the example below, I have added relevant columns such as sales person, along with their monthly and yearly sales. You could always convert it into a chart, but that’s not dynamic, so let's convert it into a table first.
3. Click and drag to select all the cells containing your data, including the header row.
4. Select Insert tab at the top and then Table.
5. Excel will automatically detect your data range. Make sure it's correct and that the My table has headers box is checked. Click OK.

Excel formats your data into a table. You will notice filter buttons (small arrows at the top to filter your data) and table styling for better readability. You can always tweak it from the Table Styles menu under Table Design.

Insert dynamic charts

Once your table is ready, glance over the recommended charts or insert one based on your preferences.

1. Decide what type of chart best suits your data, such as a column chart to compare sales by region.
2. Click anywhere on your Excel table and select Insert at the top.
3. Select a chart you want to insert, glance over the preview, and click OK.
4. That’s it. The chart will appear in your Excel sheet. You can rename the title for better clarity.

From now on, whenever you make tweaks to the sales data or even add another employee to the list, Excel will automatically update the chart accordingly.

Enhancing your data visualization

There are several ways to customize and unlock interactive filtering on your charts. Let’s go over the customization options first.

When you insert a chart, glance over the number of options appearing beside it. You can enable or disable chart elements like Axes, Axis Titles, Chart Title, Data Labels, Data Table, Error Bars, Legend, Trendline, and Gridlines.

You can even tweak the style and color, plus filter values to easily suit your needs, which can come quite in handy. Suppose you want to view only the monthly data here. Open the Filters menu, disable the checkmark beside Yearly Sales, and click Apply.

You could also hide new members on the chart to compare senior staff performance more effectively. The combinations are vast and flexible to meet your needs.

Explore slicers

If you want to take your data visualization further, insert slicers. They remain connected to your table and let you filter the chart dynamically. Slicers are helpful when you frequently deal with large datasets.

1. Open your Excel workbook and select Slicer at the top.
2. Pick a relevant property and select OK.
3. You could choose a salesperson's name from the slicer and compare their data with others in no time.

Conditional formatting

You can also explore conditional formatting to highlight salespeople who have monthly sales of less than 80000, for example.

1. Select your Excel table and click Conditional Formatting.
2. Expand Highlight Cells Rules and click Less Than.
3. Type 80000, and pick the formatting style (the default is 'Light Red Fill with Dark Red Text').
4. Select OK.

From now on, whenever someone's monthly sales dip below 80000, Excel will highlight that cell with a red color.

Pro tip: Create an interactive dashboard

When you have a large dataset with multiple charts and slicers, you may want to create a central dashboard and move all the charts there for better data visualization.

1. Create a new sheet in your existing Excel workbook and rename it as Dashboard.
2. Move all your charts and slicers and unlock data insights like a pro.

Data storytelling made easy

Your Excel journey is simply incomplete without mastering the art of dynamic visualization. By using slicers, filters, timelines, and an interactive dashboard, you can transform your data into compelling stories in minutes. The guide above is just an example. Don’t be afraid to experiment with different chart types, explore multiple customization options, and tailor your visualizations to specific needs.

In addition to dynamic charts, Copilot Pro subscribers can use Microsoft's AI chatbot to gain insights from data through text prompts. Check out our dedicated guide on using Copilot with Microsoft Excel.