K-means clustering is a machine learning technique that groups similar data points into meaningful clusters. For Excel users, it’s a simple but powerful way to uncover hidden patterns like customer segments, market trends, or pricing opportunities without needing advanced stats knowledge. In this post, we’ll explore how to use it with the Windsor housing prices dataset.
Previously, performing clustering analysis in Excel required complex formulas or manual workarounds. Now, with Python integrated directly into Excel and the ease of prompting through Copilot, this approach is accessible and straightforward. Copilot generates and explains Python scripts for you, streamlining the analysis without messy formulas or VBA.
For this demonstration, we’ll use the Windsor housing prices dataset. You can follow along with the exercise file below:
Preparing the dataset
The first step is to clean our data, starting with normalization. K-means clustering struggles when columns are on completely different scales. In our dataset, lot size is measured in thousands of square feet, price is in the tens or hundreds of thousands, and bedrooms is a single-digit number. If we feed this directly into k-means, the larger numbers will dominate the results, even if they’re not the most important features. Let’s start with this prompt:
Normalize this data to prepare for k-means clustering.
With Python in Excel, Copilot automatically recognized that we needed to scale each numeric column so that all features contribute equally. The code it generated uses standard scaling, subtracting the mean from each column and dividing by the standard deviation. This transforms each column to have a mean of 0 and a standard deviation of 1.
Choosing the number of clusters
Once our data is normalized, the next step is deciding how many clusters (K) we should use in k-means. Choosing K is a classic challenge: pick too few, and your clusters will be overly broad; pick too many, and you’ll end up overfitting noise.
To guide this decision, I used the following prompt for Copilot:
Test K-means clustering with K values from 2 to 6 and show me the silhouette score for each K so I can decide the best number of clusters.
The silhouette score is a metric that tells us how well each point fits within its assigned cluster compared to other clusters. It ranges from -1 to 1:
- Closer to 1 means points are tightly grouped and well-separated from other clusters (good).
- Around 0 means clusters overlap (so-so).
- Negative values mean many points are in the wrong cluster (bad).
Copilot tested k-means clustering for K values from 2 through 6, then plotted the silhouette scores to help identify the best fit. Statistically, K=2 came out on top with the highest score (around 0.32), meaning it gives the clearest separation between groups. As we move to K=3, the score dips, bottoms out at K=4, and then edges upward again for K=5 and K=6, though none surpass K=2’s peak performance.
If we were optimizing purely for the math, K=2 would be the obvious choice. But data analysis is more than just optimizing for metrics. Your real goal is insights that matter. A two-cluster split might be too broad to be useful for many real-world applications. If the goal is to segment customers into personas, categorize products into distinct tiers, or group properties by market potential, a finer segmentation can be more actionable. That’s why I’m going with three clusters.
Running k-means clustering
With the optimal number of clusters chosen (three, in this case) it’s time to run k-means clustering for real and see how our data is grouped. Here’s what I asked Copilot:
Run K-means clustering with 3 clusters, add the cluster number as a new column to my DataFrame, and display the first 10 rows
Copilot used Python to fit the normalized dataset and then assigned each row to one of the three clusters. It then appended a new cluster column to our DataFrame, so we can see exactly which group each property belongs to.
Analyzing cluster characteristics
Once each property is assigned to a cluster, the next step is to understand what makes each cluster unique. I asked Copilot:
For each cluster, calculate the average price, lot size, bedrooms, and bathrooms
The results make the differences between the three clusters easy to see. Cluster 0 contains mid-range homes, with average prices around $72,000, moderately sized lots close to 6,900 square feet, and just under three bedrooms. Cluster 1 represents more budget-friendly properties, averaging $53,000 with smaller lots of about 4,000 square feet and slightly fewer bedrooms. Cluster 2 stands out as the premium tier, with average prices near $96,000, larger lots of roughly 6,000 square feet, and more generous space: about 3.6 bedrooms and 1.9 bathrooms.
Visualizing clusters
Once each row had a cluster label, I asked Copilot to plot lot size against price, color-coded by cluster, with black X’s marking the cluster centers.
Create a scatter plot of lot size vs price colored by cluster, with cluster centers marked.
The result shows three distinct groups: blue in the smaller-lot, lower-price range, green covering mid-to-large lots with varied prices, and orange concentrated in the mid-lot, higher-price zone.
This confirms our earlier choice of three clusters. There’s visible separation, with some overlap, and the “middle” segment adds nuance that two clusters would miss. Plotting the data in its original units makes the chart intuitive and keeps the centroids in real-world terms, ready for naming and deeper business analysis.
Translating into business insights
By this point in the workflow, we’ve normalized the data, chosen a cluster count, assigned each property to a group, and explored those groups statistically and visually. That’s all valuable, but it’s still technical. The next step is translating these results into something a non-technical audience can immediately understand and act on. Here’s my next prompt for that:
Based on the clustering results, give me a plain-language summary of what each cluster represents, the key differences between them, and 2–3 recommendations for how a business (such as a real estate agency) could use these insights for decision-making or strategy.
Copilot returned the following insights:
Copilot’s response simplified the technical results into three distinct groups: mid-range homes for typical families, affordable smaller properties for budget-conscious buyers or investors, and premium larger homes for buyers seeking luxury and status.
This step is critical because it transforms abstract analysis into actionable strategy. Without it, we’re left with charts and averages; with it, we have a clear plan for shaping marketing campaigns, setting prices, and guiding property improvements. It’s the bridge that turns data science into business impact.
Conclusion
In this walkthrough, we used Python in Excel and Copilot to run a complete k-means clustering analysis on the Windsor housing prices dataset. We started by normalizing the data to ensure fair treatment of all features, tested different values of K to find the optimal number of clusters, assigned each property to a cluster, and then profiled and visualized those groups.
The advantages of this approach are clear: Python in Excel removes the old manual and formula-heavy pain points, Copilot automates the coding and explanation process, and visualizations make the results more intuitive. It’s fast, repeatable, and accessible to analysts who aren’t full-time data scientists.
However, k-means clustering has its trade-offs. The results can be sensitive to scaling, the choice of K requires judgment beyond the math, and the technique assumes clusters are roughly spherical in shape, which isn’t always the case in real-world data.
From here, you might refine the analysis by experimenting with different features, testing alternative clustering algorithms like DBSCAN or hierarchical clustering, or integrating external datasets for richer insights.
What do you think. Would this approach work for your data? Share your thoughts or questions in the comments, and I’ll be happy to help.
Leave a Reply