0:00
Learn how to build interactive Excel charts using a combo box and dynamic data ranges
0:04
By selecting a name in the combo box, you can instantly view and compare specific data on the
0:09
chart. We'll start with a brief explanation of the offset function, which is used to create dynamic
0:13
ranges. This approach gives you the flexibility to work with changing data while keeping your charts
0:18
responsive and interactive. The offset function allows you to return a reference to a range that
0:23
is a certain number of rows and columns away from a given starting point. It takes five arguments to
0:27
determine the range to reference. Here's what each argument of the formula means. Starting reference
0:33
This is the point from which the offset begins. In this case, it's cell A2. Row offset. This specifies
0:39
how many rows to move from the starting reference. Since B12 is used, the number of rows to move is
0:44
whatever value is in cell B12. If B12 contains a 1, then you'd move one row down from A2, landing on A3
0:50
Column offset. This indicates how many columns to move from the starting reference. Since it's set
0:55
to 0, you don't move horizontally. You stay in the same column. Height. This determines the number of
1:00
rows that the offset range should include. With a value of 1, the range is one row tall. Width. This
1:06
determines the number of columns that the offset range should include. With a value of 1, the range
1:11
is one column wide. To illustrate how the offset function can dynamically change the reference cell
1:16
based on the value in another cell, enter one in B12 cell, then input the formula in D14 cell. D14 will
1:22
display the content of cell A3 because it offsets one row from the base point at A2. Let's enter
1:27
another offset formula in cell D15 with the same arguments, but change the column offset to 1 and
1:32
the column width to 4. This updated formula extends the range across four columns, resulting in a
1:37
single row with four columns. If you enter 2 in B12 cell, D15 will then display the content from the
1:42
range B4 to E4. Similarly, if you enter 3, D15 will show the range B5 to E5. Next, add a clustered
1:50
column chart using the range A8 to E10 as shown on the screen. For now, you can insert the chart by
1:55
selecting any row in the average row. Later, we will use dynamic ranges to adjust the chart's
2:00
display based on the combo box selection. Next, go to the developer tab, select the combo box icon from
2:05
the insert drop-down and insert it on the chart. Right-click on the combo box and choose format
2:10
control. Select range A3 to A8 for the input range and select enter B12 cell for the cell link. The
2:15
dynamic ranges will use the value of cell B12 to determine the row offset. Adjust the combo box's
2:20
width and height. Also rename chart title. Next, go to the formulas tab and select define name. Name
2:27
the range names. In the refers to box, enter the formula shown on the screen. Repeat these steps
2:32
to define another dynamic range. Name the range data. In the refers to box, enter the formula shown
2:37
on the screen. Right-click on the chart and click select data from the context menu. Edit first
2:42
series in the edit series box. Enter the dynamic range name names into the series name box. Enter
2:47
the second dynamic range name data in the series values box. Click ok. Now, edit the horizontal
2:53
category axis labels and select the top heading range B2 to F2 and press ok. Clicking the downward
2:59
pointing arrow on the combo box shown on the graph will update the chart based on the selected name
3:04
Thanks for watching. Please like, share, and subscribe to my channel
