How to Make a Dynamic Chart in Excel

Manually updating charts in Excel every time your data changes is tedious and a recipe for mistakes. Creating a dynamic chart that automatically reflects new data entries is the solution. This tutorial will walk you through three different methods for building dynamic charts in Excel, from a simple, beginner-friendly approach to more advanced, interactive techniques.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly is a Dynamic Chart in Excel?

A dynamic chart in Excel is a chart that is linked to a dataset in a way that allows it to automatically update and resize as you add or remove data. Unlike a static chart, which is fixed to a specific cell range, a dynamic chart expands or shrinks its source data reference without you having to manually adjust it each time.

The benefits are significant:

• Saves Time: You set it up once, and it updates forever. No more weekly or monthly chart adjustments.
• Reduces Errors: Eliminates the risk of forgetting to update a chart range and presenting old, inaccurate data.
• Creates Interactive Reports: Allows you and your colleagues to add new data and see the impact on visualizations immediately.

Method 1: Using Excel Tables (The Easiest and Best Way)

For most situations, using a formatted Excel Table is the simplest and most effective way to create a dynamic chart. When you convert a range of data into an official Excel Table, any charts based on that table automatically recognize when new rows or columns are added.

Step-by-Step Guide to Using Tables

Let's say you have a simple dataset tracking monthly website traffic for your blog. It might look something like this:

Step 1: Convert Your Data Range into an Excel Table

First, click any cell within your data range. Then, navigate to the Insert tab on the ribbon and click Table. Alternatively, you can use the keyboard shortcut Ctrl + T (or Cmd + T on a Mac).

A prompt will appear asking you to confirm the data range. Make sure the "My table has headers" box is checked if your first row contains labels like "Month" and "Sessions," then click OK.

Your data range will now be formatted as a table, often with alternating row colors and filter options.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 2: Insert a Chart Based on the Table

With any cell in your new table selected, go back to the Insert tab. From the Charts group, choose the chart type you want. For this data, a Clustered Column Chart is a good fit.

Excel will instantly generate a chart based on the data in your table.

Step 3: Add New Data to See It Update

This is where the magic happens. Simply type a new row of data directly below your existing table. For our example, let's add "April" with "27,000" sessions. As soon as you press enter, you'll notice two things:

• The table automatically expands to include the new row.
• The chart immediately updates to include the data for April.

That's it! Your chart is now fully dynamic. Any new rows you add will automatically appear in the chart, saving you a huge amount of manual work.

Method 2: Using Named Ranges with Formulas (The Classic Approach)

Before Excel Tables became mainstream, the go-to method for creating dynamic charts was using formulas to define a "Named Range." This method is more complex but offers a deeper level of control. It's built around two key functions: OFFSET and COUNTA.

• COUNTA(range): This function counts the number of cells in a range that are not empty. We'll use it to find out how many rows of data we have.
• OFFSET(reference, rows, cols, [height], [width]): This function returns a reference to a range based on a starting point, offset by a certain number of rows/columns, and with a specified height/width.

Step-by-Step Guide to Using Named Ranges

We'll use the same website traffic data, but this time, it's just a regular data range, not an Excel Table.

Step 1: Open the Name Manager

Navigate to the Formulas tab and click on Name Manager. This will open the dialog box where we will create our dynamic ranges.

Step 2: Create a Named Range for the Chart Labels (Months)

In the Name Manager, click New.

• In the Name field, type ChartLabels.
• In the Refers to field, enter the following formula:

=OFFSET(Sheet1!$A$2, 0, 0, COUNTA(Sheet1!$A:$A)-1, 1)

Let's break that down:

• Sheet1!$A$2: This is our starting point - the first month.
• 0, 0: We don't want to move down any rows or across any columns from our start.
• COUNTA(Sheet1!$A:$A)-1: This is the dynamic part. It counts all non-empty cells in column A (the month names and the header) and subtracts 1 for the header. This gives us the total number of data rows to include.
• 1: We want our range to be 1 column wide.

Click OK.

Step 3: Create a Named Range for the Chart Values (Sessions)

Click New again in the Name Manager.

• In the Name field, type ChartValues.
• In the Refers to field, enter this similar formula:

=OFFSET(Sheet1!$B$2, 0, 0, COUNTA(Sheet1!$B:$B)-1, 1)

This formula works the same way but references column B for the session data. Click OK and then Close the Name Manager.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Create a Chart and Link it to the Named Ranges

First, insert a column chart just based on your initial data (A1:B4). Now, we'll edit its source data.

Right-click the chart and choose Select Data. In the dialog box that opens, click on your data series ("Sessions") and then click Edit.

In the Edit Series dialog, locate the Series values field. It will currently contain something like =Sheet1!$B$2:$B$4. Replace that entire reference with your named range for values. You must include the sheet name!

=Sheet1!ChartValues

Click OK. Back in the Select Data Source dialog, edit the Horizontal (Category) Axis Labels. Replace the existing range with your other named range:

=Sheet1!ChartLabels

Click OK. Your chart is now powered by dynamic named ranges. Add new data for "April," and you'll see the chart instantly update to include it, just like the table method.

Method 3: Creating Interactive Charts with Form Controls

A different kind of dynamic chart is one where the user can choose what data to display. We can achieve this using form controls like checkboxes to show or hide different data series.

Step 1: Add the Developer Tab to the Ribbon

If you don't have it already, you'll need the Developer tab. Go to File > Options > Customize Ribbon. On the right side, check the box for Developer and click OK.

Step 2: Insert Check Boxes

Go to the Developer tab, click Insert, and under Form Controls, select the Check Box.

Click and drag on your sheet to draw a checkbox. Right-click it and choose Edit Text to label it "Show Google." Repeat the process to create a second checkbox labeled "Show Facebook."

Step 3: Link Check Boxes to Cells

Right-click the "Show Google" checkbox and select Format Control. In the Control tab, click inside the Cell link box and select a helper cell, for instance, E1. Click OK.

Now, when you check the box, cell E1 will display TRUE, when unchecked, it will display FALSE. Do the same for the "Show Facebook" checkbox, linking it to cell F1.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Create a Helper Data Table

We'll now create a shadow data table that is controlled by our checkboxes. In a separate area of your sheet, set up headers for your new table. In the first cell under the new "Google" header, enter the following formula:

=IF($E$1, B2, NA())

This formula says: "If cell E1 (our TRUE/FALSE switch) is TRUE, then show the value from B2. Otherwise, return the #N/A error."

In the first cell under the new "Facebook" header, use a similar formula referencing the other original data and its checkbox cell:

=IF($F$1, C2, NA())

Drag these formulas down to match your original data. Your sheet will now look something like this:

The NA() function is crucial here. When Excel charts encounter an #N/A value, it simply ignores that data point, effectively hiding it from the chart without creating an unsightly gap at the zero line.

Step 5: Create a Chart from the Helper Table

Finally, select your helper data table and insert a line chart. Now, you can toggle the checkboxes on and off, and the corresponding data series will appear and disappear on your chart in real-time, making for a truly interactive dashboard experience.

Final Thoughts

Creating dynamic charts transforms your Excel reports from static documents into living, breathing tools that update alongside your data. Whether you use the simple and robust Table method, the classic Named Range formula technique, or interactive form controls, you’re saving yourself time and improving the accuracy and professionalism of your work.

Ultimately, wrangling data from multiple sources like Google Analytics, Shopify, Facebook Ads, and Salesforce to build these reports in Excel is still a huge time commitment. We built Graphed to remove this workflow friction completely. Instead of building pivot tables and formulas, you can connect your data sources in a few clicks and build a dynamic dashboard by simply describing what you want to see - like “Show me my revenue from Facebook Ads and Google Ads on a line chart for the last 90 days.” Our AI generates the interactive, real-time charts for you instantly so you can get back to analyzing, not just reporting.