Skip to content

Create a Dynamic Graph in Excel

Sometimes it’s nice to have a graph that you can update simply by changing a single cell in Excel.

For example, let’s say you want to see the sales numbers for January, and then you want to update the graph to show the sales numbers for February instead.

Here is how you can build a dynamic graph in Excel that will update by changing a single cell.

Step 1 – Store the Data

Step one for creating any graph in Excel is to make sure that you store the date within your workbook.

It doesn’t matter where you put it as long as the data is somewhere in your spreadsheet.

Once you have your data, you can move on to step two.

Step 2 – Create a Selection Space

Step two is to pick a cell where the user can update their selection for the graph.

In this example, you want the user to be able to select a different month to update the graph. So you can simply pick a cell and make it obvious to the user that this is where they can update their selection.

Step 3 – Create the Graph Data

Now that you have the original data and the user selection space in your workbook, you can create the middle data layer, which will directly power the graph.

This new data layer is what will show up on the graph, and it is what will be updated when the user changes their selection.

In other words, when the user changes their selection, this will update the new data layer, which will then update the graph. This is because the new data layer will pull from the original data based on the user’s selection.

For example, if the user selects January, the middle data layer will only reflect the data for January. And if the user selects February, it will show the data for February.

To create this middle data layer, you will use the INDEX and MATCH functions for your formula.

If your spreadsheet appears as you see below, this is what your formula will be for cell B9.

=INDEX(B3:E3, MATCH($D$9, $B$2:$E$2, 0))

This formula searches for the column containing the selected month and then grabs the corresponding values for each region from that column.

Once you type in the formula for B9, you can use the fill handle to drag the formula down for B10:B12 as well.

Then, when the user updates the selected month, the values are taken from a new column to update the middle data layer.

Step 4 – Create the Graph

At this point, you have:

  • The original data
  • The selection space
  • The middle data layer

You are now ready to create the graph.

To create the graph, select the data, and go to Insert Bar or Column Chart > Clustered Column Chart.

This will insert the bar graph you need, and you can now format it to look the way you want.

Now, whenever you or someone else updates the selected month, the graph will update automatically!

And that is how to create a dynamic graph in Microsoft Excel!

See It In Action

Check out the video below to see the whole process from start to finish!

This Post Has 2 Comments

  1. This is really so cool! I’ve tried this out in a spreadsheet of mine and it was super effective! Thanks for direction on this!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top