Among all of the amazing enhancements in Excel, one that really resonates with the core of Excel is dynamic arrays. Spreadsheets became popular primarily because of their tactile ability to visually compute and display changing results in real-time.
Dynamic arrays open up numerous new possibilities for this type of visual, interactive analysis, not least of which is the capability to easily construct interactive data visualizations in Excel.
In this post, we’ll explore how to create a dynamic bar chart driven by a dynamic dropdown, checkboxes, and a top N analysis. To follow along, you can download the exercise file below:
We have a dataset detailing deposits by branch and region, and I’m planning to use bar charts to compare total deposits across branches. Let’s add some interactivity to make this chart just a bit more interactive and user-friendly. Here are a few ways we can achieve it using dynamic arrays.
If you’re new to dynamic arrays and dynamic array functions such as UNIQUE(), FILTER(), and SORTBY(), consider checking out my book Modern Data Analytics in Excel before proceeding:
Example 1: Dropdown-driven visualization
First, I’ll create a dropdown-driven visualization. This will allow the user to select a specific region, and total deposits for that region will be visualized.
The first step is to establish a data source for the dropdown range. I aim to make this as dynamic as possible by building it directly from the table’s source data. There are several methods to achieve this, but I’ll start by creating an intermediate dynamic array of the regions, listed alphabetically from A to Z.
=SORT(UNIQUE(deposits[Region]), 1)Next, go to the Formulas tab on the ribbon and open the Name Manager.
Create a named range called regions. Instead of linking this range statically, make it dynamic! You can achieve this by appending a # to the end of the first cell in the reference range within the Name Manager. In my case, it’s =setup!$A$3#.
I’ll place this in a worksheet named setup and will either hide it or make it less conspicuous within the workbook. It will be used solely to set up the dropdown for the visualization.
Next, open a new worksheet where we’ll establish the dropdown area for the chart. Select the cell where you want the user to choose the data, then navigate to Data > Data Validation on the ribbon. Choose “List” for the validation criteria, and link the source directly to our named, dynamic range regions:
Go ahead and test it. You’ll see that we now have a dynamic dropdown list. If you add or remove any categories from the source data, the dropdown will update automatically.
That’s already pretty cool, but let’s take it a step further and use this to drive a data visualization. The first step will be to prepare the data to be plotted. I’ll use the FILTER() function to retrieve records from the selected region, combined with the SORT() function to arrange the resulting records by total deposits from high to low. This will create a more aesthetically pleasing bar chart.
=SORT(FILTER(deposits, deposits[Region] = dropdown!B1), 3, -1)
With the source data defined and selected, go ahead and add our data visualization by going to the ribbon and choosing Insert > 2-D Column > Clustered Column.
To clean up this bar chart a bit, I’m going to do a couple of things. One adjustment I like to make to my charts is to remove the gridlines, which can be done by clicking the large plus sign to the upper right of the chart to access Chart Elements, and then simply deselecting Gridlines.
Another one of my standard quick fixes is to add a dynamic title to the chart. I’ll do this by inserting a helper cell into my plot and writing the following formula:
=CONCAT("Total deposits for ", B1, " region")
From there, I’ll link the chart title to this formula and then ultimately hide this reference in a cell underneath the chart.
Excel automatically added both the branch and region categories to our labels, which I find unnecessary. So, I’m going to right-click on my chart, select “Select Data” to fix this.
From here, go to Edit under Axis labels:
I am going to adjust the source of the axis labels just to include the branch names:
Great work! You should be all set now. Try clicking the dropdown to see what you get.
Great work! For the next examples, you’ll follow many of the same steps to refine the chart results, such as adding interactive titles and adjusting the axis labels. But let’s explore a few more options for making this basic bar chart dynamic—that’s the main goal of this post.
Example 2: Checkbox-driven visualization
For our next chart, instead of having the user select from a dropdown by region, we’ll provide checkboxes. This way, users can select all the branches they want to include on the chart, like this:
To get started, I am going to create a dynamic list of branch names. This can be done simply by referencing the structured table column name of the Branch Name column:
Next, I’m going to select the cells next to these branch names and go to Insert > Checkbox. Please note that this creates a static set of checkboxes and might need to be adjusted depending on the number of branches. I haven’t found a way to dynamically add them yet, so if anyone knows how, please let me know!
Now, we’re going to filter the data based on which checkboxes have been checked. We can accomplish this with the help of the OFFSET() function. This function allows us to look over to the right one column, identify which checkboxes are in a TRUE state, and then filter the data to include only those branches that are selected. Finally, we’ll sort the results from high to low:
=SORT(FILTER(deposits, OFFSET(A1#, , 1) = TRUE), 3, -1)
Follow the same steps as previously to add the plot, clean it up, and so forth. This time, I’m just going to add a static title, but feel free to get creative here! You should end up with something like this:
Great work. This approach could be useful in many scenarios where users want to focus their attention on comparing various branches ad hoc.
Example 3: Top N-driven visualization
Last but not least, let’s explore a common method for filtering the data: a Top N analysis. This approach will look something like this:
To get started, add an area at the top of the worksheet for the user to define the value of N. You could even add data validation here to ensure the input is appropriate. I am going to focus on the function, which is the most complex one we’ve seen thus far:
=SORT(FILTER(deposits, deposits[Total Deposits ($)] >= LARGE(deposits[Total Deposits ($)], B1)), 3, -1)
In this setup, the FILTER() function is used to select rows from the deposits table where the values in the Total Deposits ($) column are greater than or equal to the nth largest deposit. This threshold is determined by the LARGE() function using the value specified in cell B1, allowing us to return only the values up to the limit of N.
Finally, as always, we sort the resulting deposits from high to low using the SORT() function.
Go ahead and follow the same steps to create this visualization. This time, you might want to add a dynamic chart title so it displays the top N values. You’ll end up with something like this:
Conclusion
In all these examples, dynamic arrays were effectively used to subsegment and filter the rows we wanted to keep as the source for the data, which made building dynamic interactive visualizations much easier. As you consider building interactive visualizations, think about how users might interact with them. Consider what might happen to the charts if data is added that you’re not expecting.
For instance, will new categories or outlier values disrupt the structure of your plot? Therefore, it’s crucial to test thoroughly and ensure that the visualization meets the user’s needs and that they are comfortable using it.
What questions do you have about building interactive visualizations in Excel with dynamic arrays? Let me know in the comments.
Leave a Reply