Dynamic Charts in Excel are updated automatically when their chart’s rage in changes. Learn how to create a Dynamic chart in excel in this blog

Create Dynamic Charts in Excel with XLOOKUP

First time ever… Chart Title drop List

In this tutorial I show you how to use the new XLOOKUP function to create a dynamic chart in Excel that switches data depending on the selection from a drop list.

The key to dynamic charts is to create a Data Preparation Table that sits between your raw data and your chart.

I will be creating this functionality in 2 different methods

1. Classic Index& Match Functions
2. XLOOKUP function

I will then create the First Ever Chart Title Drop List to switch chart from the Title directly.

In this project:

• I create a Data Validation drop list
• I create an Index-Match-Match function
• Alternatively, I create an XLOOKUP Function
• I create a column Chart
• Format the Chart Elements
• Create a Dynamic chart Title
• Understand the concept of In-Chart Drop List
• Name a Cell
• Create a Macro
• Test the Interactivity

You can Download the exercise file and follow along by clicking here.

Our Project File Shows

In column A, a list of 5 products, then in columns B to M the sales quantity for each month for each product. We want to create a column chart that shows the sales for a selected month.

Data Preparation Table

I start by copying the labels from A2 to A6 and paste them in cell F10

Creating a Data Validation List

Then in cell B9, I create a drop list for the different months (B1:M1):

Click on the Data Tab then click on Data Validation (alternatively use the shortcut ALT, D, L). From the upper drop list select “List”. Put the blinking cursor in the source box, then click and drag to select all the months in row 1.

In cell G9 I want to create a reference to my drop list by typing:

=B9 ,

accordingly, changes in the drop list in B9 will also change the value of G9.

In cell G10 I start creating a function to extract the sales quantity for each product for the selected month in the drop list.

Method #1: Classic Function: INDEX- MATCH – MATCH

The Index Function extracts a value from an array of values at the intersection of a row number and a column number.

The arguments are as follows:

=INDEX(array,row_num,column_num)

Our array will be all the numbers in the range B2:M6 (locked by hitting F4)

Then we’ll use a Match function to locate the position of each product in column F in the range A2:A6 (e.g. “Computers” are at position #3)

This match function (nested inside the INDEX function) will be:

=MATCH(lookup_value,lookup_array,match_type)

=Match(F10, $A$2:$A$6,0)

Note that the Lookup array is locked by hitting F4 key, so that it does not change when you copy your formula down.

The second Match function will look at your selection in G9 from the drop list then returns the position of the selected month in the range B1:M1. That will be the column number needed for the INDEX function, as follows:

=MATCH($G$9,$B$1:$M$1,0)

Note that the lookup value and the Lookup array are locked by hitting F4 key, so that they do not change when you copy your formula down.

Now if we nest the 2 Match functions in the index function in cell G10 it will look like this:

Now we can copy our function down by dragging from the AutoFill handle.

Changing the month from the drop list returns the numbers for the selected month.

Method # 2: Using the new XLOOKUP Function

This new function was introduced for the first time on 28 August 2019 at noon.

It has 5 arguments: 3 are mandatory arguments and 2 are optional. These arguments are:

=XLOOKUP(lookup_value,lookup_array,return_array,[match_mode],[search_mode])

I’ll be using only the mandatory arguments while nesting 2 XLOOKUP functions in each other. The Inner function, will extract the column of numbers corresponding to the selected month. So if I write in any cell:

=XLOOKUP(G9,B1:M1,B2:M6)

Note:

1. The Lookup value Comes from the drop list cell G9
2. The Lookup Array is the top row B1:M1 having all the months.
3. The return Array are all the numbers B2:M6
4. Because it’s a Dynamic Array Function, using the new calculation engine, then:
   1. I do not need to lock any cell references
   2. The function lives in the single cell where I created it.
   3. The function spills to adjacent cells.
   4. The function is greyed out in the adjacent cells.

This XLOOKUP function created above will be the inner function in another XLOOKUP function. It will be the third argument (The Return Array) of the outer function.

The outer function will be:

=XLOOKUP(F10,A2:A6,XLOOKUP(G9,B1:M1,B2:M6))

The outer XLOOKUP has 3 arguments:

1. The Lookup Value the Product name in F10
2. The Lookup array The range of products in column A
3. The return array: The first Xlookup function

This function needs to be copied down by dragging

To test the functionality, change your selection from the drop list in B9, accordingly the value of G9 changes and all the calculations in G10:G14 update.

Create a Column Chart

Now we want to represent these values graphically by creating a column chart. So, I need to prepare for creating a dynamic chart title by creating a simple formula in cell D9, that combines some text with the name of the selected month coming from cell G9

=”Total Sales for “&G9

Creating a Column Chart

• Select the range F10:G14
• Click on the Insert Tab and select column Chart
• Alternatively, use the shortcut ALT + F1

Let’s format the chart

• Click on one of the Horizontal gridlines >> Hit delete on the keyboard.
• Click on the Value Axis >> Hit Delete on the keyboard.
• Click on the Category Axis >> Home Tab >> Point size 11 + Bold
• Click on one of the Data Points >> Hit CTRL +1 >> Opens the Format Data Series Pane on the right side.
• Drag the “Gap Width” slider to the left (about 150%) to reduce the gap between columns.

• Click on the Fill bucket icon >> Check the Box for “Vary Color by point”

• Click on any one of the Data Points >> All columns are selected >> Click on the green plus sign in the upper right corner of the chart (Chart Elements Button) >> Check the box for “Data Labels” >> Values are added on top of each column.
• Click on any one of these values >> all of them are selected >> Apply basic formatting on the Home Tab.
• Click again on one of the Data Points >> All of them are Selected >> Click on the Format Tab>> Click on Shape Effects >> Click “Bevel” >> select “Round”

Now, let’s create a Dynamic Chart Title

• Click on the Chart Title the hit the F2 key on the keyboard
• Now the mouse pointer is in the Formula Bar: Type “=” sign then click on D9 (the cell in which we prepared for the Title) >> Hit Enter.
• Click on the outer border of the chart and bevel it as we did earlier with the data points.
• Now drag the chart and position it over the preparation table.
• Test by changing the selection in the drop list: The columns update and the Title updates as well.

Chart Title Drop List

So far we make our selection from a drop list in Cell B9 (Outside the chart). We need to take our chart to the next level and make it a lot more impressive by embedding the drop list in the Chart Title.

Stay tuned for Part 2 of this blog post.

Watch the Video for Part 1