Python in Excel: How to do hierarchical clustering with Copilot

Hierarchical clustering is a technique that groups similar data points into clusters based on their attributes, forming a hierarchy or tree-like structure. Imagine organizing customers based on their purchasing behaviors or demographics to discover distinct segments you can target differently.

For business users who rely on Excel, hierarchical clustering is a valuable tool because it reveals meaningful customer groups that aren’t immediately obvious. However, doing this type of analysis traditionally in Excel was cumbersome—if not impossible—due to Excel’s limited statistical functionality.

Now, with Python and Copilot built directly into Excel, sophisticated clustering methods like hierarchical clustering become accessible even if you’re not a programmer. Copilot helps generate Python code for you, significantly reducing the barrier to entry and enabling you to quickly gain insights from your data without leaving Excel.

In this post, we’ll explore hierarchical clustering using the Mall Customers dataset. Follow along with the exercise file below:

This dataset contains demographic and spending information for customers of a shopping mall, including their age, gender, annual income, and a special “spending score” assigned by the mall based on purchasing habits.

It’s an excellent dataset for hierarchical clustering because it clearly demonstrates how customers naturally group into segments based on financial behavior and spending patterns… exactly the type of insight businesses can use to drive targeted marketing strategies or personalize offers.

Preparing and beginning the analysis

If you’re new to using Advanced Analysis in Copilot, I recommend checking out this post first. Otherwise, let’s jump into Advanced Analysis mode and get started with our first Copilot prompt:

“Use the annual_income and spending_score columns to perform hierarchical clustering. Normalize the data before clustering.“

We’re using these two columns because they’re directly tied to customer spending power and purchasing behavior: key factors in customer segmentation. Normalizing ensures that income and spending score are compared on equal footing, preventing one factor from dominating just because of larger numerical values. This step leads to more meaningful and balanced clusters, setting us up for accurate and actionable insights.

When you run the first prompt, Copilot will generate a dendrogram: a tree-like diagram that shows how the clustering algorithm grouped your data step-by-step. Each merge in the tree represents two clusters being joined, with the height of the merge (the Y-axis) showing the “distance” or dissimilarity between them.

In this case, the X-axis lists every customer in the dataset, which can make it very long and cramped. By default, Copilot may give you a narrow plot, so you might need to ask it to make the figure wider. You can also tweak the Python code directly by adjusting the figsize parameter to make the labels and structure more readable.

Even though the plot can look busy, it’s full of useful information. You can see which groups of customers are most similar, where there are natural “break points” between clusters, and how distinct those clusters are. However, Copilot inside Excel can’t actually “see” the dendrogram image it just generated, so it can’t tell you the best number of clusters directly from the plot.

Interpreting the results with Microsoft Copilot

That’s why our next step is to paste the image into Microsoft Copilot (which can process images) and use this follow-up prompt:

“I’ve performed hierarchical clustering and generated the following dendrogram. Based on this dendrogram, determine the optimal number of clusters.”

We’re doing this because deciding how many clusters to use is critical. Too few and you lose meaningful differences between customers, too many and you overcomplicate the segmentation. The dendrogram gives a visual way to make that decision, and 365 Copilot can help interpret it.

Copilot decided the optimal number of clusters was 4, based on where the largest vertical gap appears in the dendrogram. Cutting the tree there creates four well-separated groups, each representing customers with distinct income and spending patterns.

Summarizing the cluster results

Our next step is to return to Excel Copilot and run this prompt:

“Assign cluster labels to the original DataFrame based on hierarchical clustering with 4 clusters. Add the labels as a new column and group the data by cluster to show the mean annual_income and spending_score for each cluster.”

Grouping by cluster and showing the average annual income and spending score for each segment will give us a quick, high-level profile of our customer groups, making it much easier to interpret their characteristics and plan targeted actions.

Summarizing business takeaways

Now that we’ve labeled and summarized our clusters, the next logical step is to turn those numbers into action:

“Provide business interpretations for each cluster, suggesting marketing strategies based on their income and spending behavior.”

By having Copilot help us with business interpretation, we bridge the gap between analytics and decision-making, making the analysis useful for real-world marketing strategy.

Conclusion

Hierarchical clustering matters because it turns raw customer data into practical segments you can actually market to… directly inside Excel. With Python and Copilot, advanced segmentation becomes fast and repeatable, so teams can move from “Who are our customers?” to “Which offer should each group see?” without leaving their familiar workflow.

That said, clustering depends heavily on variable selection, with subjective dendrogram cuts revealing patterns but not causality. To enhance analysis, you can enrich features with recency, frequency, channel, or region, and assess cluster stability using silhouette scores or resampling, while validating value through controlled experiments. Alternative models like decision trees, random forests, or logistic regression can classify or predict outcomes, while PCA reduces dimensionality and gradient boosting models like XGBoost capture complex patterns.

It’s impressive that we accomplished this in Excel, with exciting opportunities to explore advanced models in Python with Copilot’s help.

The post Python in Excel: How to do hierarchical clustering with Copilot first appeared on Stringfest Analytics.