Integrating Python into Excel unlocks a wide range of data visualization options, though interactivity with Python-generated plots remains somewhat limited.

In Excel, Python plots are essentially static images, lacking features like tooltips or the ability to explore series data. That said, with a bit of creativity, it’s possible to introduce some interactivity, such as filtering, into these visualizations. In this post, we’ll explore how to build a checkbox-driven visualization using Python in Excel. You can follow along using the exercise file provided below:

In this example, we’re using the mpg dataset, and my aim is to create a scatterplot that compares mileage and weight, with points colored according to their origin. I want to enable the user to choose which origin entries to display in the plot. To start, we’ll extract a list of unique values from the origin column using the UNIQUE() function in Excel:

This dynamic array will form the foundation for letting the user choose which unique values to include or exclude. We’ll use the checkbox feature as the mechanism for users to indicate their selections.

To get started, go to the Insert tab on the ribbon and add checkboxes next to each unique value. Unfortunately, these checkboxes don’t automatically adjust if the array size changes, but everything else we implement from this point forward should dynamically adapt to updates in the data.

Now, let’s add some clever functionality using the spill operator and the OFFSET() function. Our goal is to generate a list of the values the user has checked—essentially, identifying which checkboxes are set to TRUE. To do this, we’ll start with the dynamic spill range in K1, offset it by one column to the left, and filter it to include only the rows where that column is TRUE. To prevent errors if nothing is selected, we’ll display a friendly message, “Please make a selection,” as a fallback.

=FILTER(K1#, OFFSET(K1#, , -1) = TRUE, "Please make a selection.")

Let’s see it in action! As you’ll notice, this list of values updates dynamically based on the user’s checkbox selections—great job!

Now that we have a dynamically selected set of categories in Excel, it’s time to bring this into Python. Due to some quirks in how Python in Excel imports data, we’ll need to use a force_to_list() function, like the one below, to ensure the result is always stored as a list regardless of how many items the user selects.

Python in Excel treats single-element selections as a different data type than multi-element selections, so this step levels the playing field, handling both cases consistently and always returning a list.

Feel free to add this function somewhere in the worksheet—perhaps in the first row like below.3 Alternatively, you could place it in a helper worksheet, as long as we account for Python’s left-to-right, top-to-bottom read order. We’ll need this function in place when it’s time to read in our data, which is the next step.

Alright, we’re in great shape for our final approach! Let’s start by loading the mpg dataset into a DataFrame. Next, we’ll filter this dataset to include only the categories the user has selected—this is where our force_to_list() function comes into play. We’ll keep only the rows in mpg_filtered where the origin column values are in the user-defined list, using the isin() method. Finally, we’ll create the scatterplot to visualize the results.

mpg_df = xl("mpg[#All]", headers=True)

mpg_filtered = mpg_df[mpg_df['origin'].isin(force_to_list(xl("M1#")))]
sns.scatterplot(x='weight', y='mpg', hue='origin', data=mpg_filtered)
plt.title('MPG vs weight by origin')

Let’s give it a test run. It looks like we’ve successfully built a basic, dynamic, and interactive visualization using Python in Excel!

There’s definitely room to polish this further—think adjusting the axes, adding a dynamic title, or locking in colors so each category stays consistent across different origin combinations. But those are just finishing touches. We’ve already built a solid checkbox-driven visualization that dynamically adapts to changes in data and user inputs.

This could be a fantastic starting point for a simple dashboard or report, particularly for features that are tough or flat-out impossible in native Excel. For instance, with this approach, we can conditionally color scatterplot points without the annoyance of extra helper columns that require constant adjustment whenever the number of unique categories changes, like we’d have to do in native Excel.

What questions do you have about building checkbox-driven interactive visualizations with Python in Excel, or about using Python in Excel more broadly? Let me know in the comments below.

The post Python in Excel: How to create checkbox-driven visualizations first appeared on Stringfest Analytics.