Cluster Analysis in Excel: Methods and Step-by-Step Guide

How-to basics · Updated November 21, 2026

Excel for Microsoft 365 does not include a built-in command that automatically performs cluster analysis. For a small, numeric dataset, you can run k-means with worksheet formulas: standardize the variables, assign each row to its nearest centroid, recalculate the centroids, and repeat until assignments stop changing.

What cluster analysis does

Cluster analysis groups observations according to how alike they are on selected variables. Unlike classification, it does not start with known labels such as approved or declined. Instead, it looks for patterns in the data and gives each observation a group, or cluster. A retailer might explore whether customers form groups based on purchase frequency and average order value; a researcher might look for groups of samples based on measured characteristics.

The result depends on the variables and distance measure you choose. A clustering algorithm does not establish that the groups are objectively true, explain why a group exists, or prove that a pattern will predict future behavior. It offers a way to explore similarity in the data. Cluster numbers are labels, not rankings: cluster 1 is not inherently better or more important than cluster 2.

Does Excel have a clustering tool?

Excel’s standard Data Analysis ToolPak offers tools such as regression and descriptive statistics, but it does not provide a general k-means or hierarchical-clustering command. Likewise, there is no built-in Cluster Analysis button in the standard Excel ribbon. Some third-party add-ins and external data-analysis tools offer clustering, but their features, prices, and data-handling practices vary.

For modest datasets, formulas can make k-means understandable and auditable. The trade-off is that you must set up the calculations and repeat the update steps yourself. Formula-based clustering is less convenient for large datasets, many variables, repeated production work, or analysis that needs rigorous model selection.

Prepare the data before clustering

Put each observation in one row and each numeric variable in its own column. Keep a separate identifier column so you can relate cluster assignments back to the original records. For example, a customer table could have an ID, purchase frequency, and average order value. Do not include an ID number as a clustering variable: its numerical size usually has no meaningful relationship to customer similarity.

  • Choose variables that reflect the question you are investigating, rather than adding every available column.
  • Check for blanks, text stored as numbers, duplicate records, and impossible values. Decide how to handle missing values before calculating distances.
  • Look for extreme outliers. Because k-means uses means and squared distances, an outlier can pull a centroid and affect other assignments.
  • Put variables on comparable scales. A variable measured in dollars can otherwise dominate one measured from 1 to 5.

Standardization is a common way to put numeric variables on a comparable scale. It converts a value to its distance from that variable’s mean in standard-deviation units. With observations in rows 2 through 101, raw variable 1 in B2:B101, and its standardized values in D2:D101, enter this in D2 and fill down: =(B2-AVERAGE($B$2:$B$101))/STDEV.S($B$2:$B$101). With raw variable 2 in C2:C101 and standardized values in E2:E101, enter this in E2 and fill down: =(C2-AVERAGE($C$2:$C$101))/STDEV.S($C$2:$C$101). These formulas use sample standard deviations. If a variable has no variation, its standard deviation is zero, so remove it or handle it separately rather than dividing by zero.

Run a basic k-means analysis with formulas

K-means requires you to choose the number of clusters, called k. The steps below use two standardized variables and k = 2 to keep the worksheet readable. The same method extends to more variables and clusters, but the distance formula and centroid table need more terms and rows. Use Excel for Microsoft 365 on Windows; the formulas also work in current Excel for Mac. Ribbon locations can differ on Mac, but this calculation uses worksheet formulas rather than a special ribbon command.

  1. Choose starting centroids. Put the standardized coordinates of two starting observations in J2:K2 and J3:K3. For instance, copy the values from D and E for two different records. Starting with actual observations makes the initial values easy to inspect. Different starting points can produce different results, so it is sensible to try more than one set.
  2. Assign each row to its nearest starting centroid. With standardized values in D2:E101, the two centroids in J2:K3, and initial assignments in F2:F101, enter this formula in F2 and fill down: =IF((D2-$J$2)^2+(E2-$K$2)^2<=(D2-$J$3)^2+(E2-$K$3)^2,1,2). It compares squared Euclidean distances and assigns a tie to cluster 1. Taking square roots is unnecessary for comparing these distances.
  3. Calculate the updated centroids. A centroid is the mean of the standardized values assigned to its cluster. With initial assignments in F2:F101 and the first updated centroid table in L2:M3, enter =IFERROR(AVERAGEIF($F$2:$F$101,1,$D$2:$D$101),J2) in L2, and =IFERROR(AVERAGEIF($F$2:$F$101,1,$E$2:$E$101),K2) in M2. For cluster 2, enter =IFERROR(AVERAGEIF($F$2:$F$101,2,$D$2:$D$101),J3) in L3 and =IFERROR(AVERAGEIF($F$2:$F$101,2,$E$2:$E$101),K3) in M3. The IFERROR fallback retains the previous centroid if a cluster is empty; an empty cluster is also a reason to review the starting points or chosen k.
  4. Reassign observations. With the updated centroids in L2:M3 and the next assignment column in G2:G101, enter this in G2 and fill down: =IF((D2-$L$2)^2+(E2-$M$2)^2<=(D2-$L$3)^2+(E2-$M$3)^2,1,2). Then calculate another centroid table from G2:G101, using the same AVERAGEIF pattern but referring to the new assignment column and the previous centroids as fallbacks.
  5. Repeat and check for stability. Continue in new columns: each round uses the previous round’s assignments to calculate centroids, then assigns each row to the nearest new centroid. Stop when assignments no longer change, or when a chosen iteration limit is reached and you have checked that the solution is stable. Keeping rounds in separate columns avoids circular references and lets you review what changed.

For more than two clusters, add a centroid row for every cluster and compare the distance to each one. For more variables, add the squared difference for each standardized variable to every distance calculation. Keep a clear record of which columns hold each iteration; a formula copied with a wrong centroid reference can silently give misleading assignments.

Choose k and assess the result

There is no universally correct number of clusters. A practical approach is to run the analysis for several plausible values of k, then compare both numerical fit and usefulness. One common measure is within-cluster sum of squares: for every observation, calculate its squared distance to its assigned centroid and sum those distances. This quantity cannot increase when k increases, so a smaller value alone does not prove that a larger number of groups is preferable.

An elbow plot charts that sum against k. Look for a point where adding more clusters produces a noticeably smaller improvement, while treating the bend as a guide rather than an automatic answer. Also check whether clusters are large enough to interpret, whether they differ on variables relevant to the question, and whether the pattern remains broadly similar when you try different starting centroids. A cluster containing one unusual record may be a useful outlier signal, or simply evidence that the model needs attention.

Interpret clusters carefully

After the assignments stabilize, summarize each cluster in terms people can understand. Compare the original, unstandardized variables by cluster, not just the standardized values used to calculate distances. A table of cluster counts and average purchase frequency or order value can help describe the groups. Be clear that these are descriptive summaries of the records used to build the clusters, not proof of a cause or a guarantee about future observations.

Names such as frequent low-spend customers can be convenient shorthand if the summary supports them, but retain the numeric cluster label and definitions in the workbook. Cluster labels can switch between runs because the algorithm has no fixed meaning for cluster 1. If you compare results over time, match groups by their characteristics rather than assuming the same number always refers to the same kind of group.

Common problems and limitations

  • Unscaled variables: A measurement with a large numeric range can dominate distance calculations. Standardize suitable numeric columns, and document the transformation.
  • Outliers and skew: Means can be pulled by extreme values. Inspect distributions and consider whether a transformation or a different method is more appropriate; do not remove records solely because they are inconvenient.
  • Mixed or categorical data: Ordinary Euclidean distance on numeric codes for labels such as region or product type usually creates meaningless comparisons. Use an approach designed for mixed data or encode variables only when the resulting distance has a defensible interpretation.
  • Unstable starts: K-means can settle on different solutions from different initial centroids. Repeat with sensible starting points and compare the resulting group sizes and summaries.
  • Wrong shape for k-means: K-means favors compact groups around their means. It may not represent irregularly shaped groups or data with very different densities well.
  • Formula errors: Check that each iteration uses the intended assignment and centroid columns, that all rows are included, and that no cluster is empty. A workbook can calculate correctly while still answering a poorly framed question.

When to use another tool

Excel is useful for learning the mechanics, exploring a small table, or sharing a transparent calculation with colleagues. For many rows or variables, repeated analysis, automated reporting, or more advanced clustering methods, a statistical package or a programming workflow in R or Python is generally more suitable. Those environments offer more ways to repeat runs, evaluate solutions, and document analysis, though they still require informed choices about data preparation and method.

Excel can remain part of that workflow: use it to inspect or prepare a dataset, then use an appropriate analysis tool and bring back summaries or assignments for reporting. Before sending sensitive records to an add-in or external service, check your organization’s data rules and the provider’s terms. A clustering result should not be treated as a decision about an individual without appropriate review and context.

Frequently Asked Questions

Can Excel automatically cluster data?
Not with a standard built-in clustering command in Excel for Microsoft 365. You can build a basic k-means worksheet with formulas, use a suitable add-in, or analyze the data in a dedicated statistical tool.

Is cluster analysis the same as sorting or grouping rows?
No. Sorting orders records by a chosen column, and manual grouping applies labels you already know. Clustering assigns groups based on similarity across selected variables and a chosen distance method.

How many clusters should I choose?
There is no fixed answer. Compare plausible values using measures such as within-cluster sum of squares, inspect group sizes and descriptions, and ask whether the groups are useful for the purpose. An elbow plot is a diagnostic, not a definitive rule.

Do I need to standardize every variable?
Not in every analysis, but scaling matters when variables use different units or ranges. Standardization is common for distance-based clustering; decide deliberately if a variable’s scale should carry more weight, and explain that choice.

Why do my cluster labels change when I rerun the analysis?
K-means can reach different solutions from different starting centroids, and the numeric labels themselves have no fixed meaning. Compare the cluster profiles and assignments, and try multiple starts before drawing conclusions.

Can I use text categories such as department or region?
Not as ordinary numbers in the Euclidean-distance formulas above. Numeric codes for categories imply distances that may not exist. Choose a method and distance measure suited to categorical or mixed data, or analyze numeric variables separately.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.