K-Means Clustering in Excel: Solver and Easier Methods

How-to basics · Updated November 18, 2026

Yes—Excel Solver can be used to model k-means clustering, but Excel has no built-in k-means command, and a Solver model can be awkward and unreliable for larger datasets. For most small spreadsheets, a practical approach is to calculate distances to cluster centers, assign each row to its nearest center, recalculate the centers, and repeat until the assignments stop changing.

Solver is most useful when you want to experiment with an optimization model and understand its constraints. If your goal is simply to group observations, an iterative worksheet or a dedicated statistical tool is usually easier to maintain.

What k-means clustering does

K-means is an unsupervised method: it groups observations using their numerical features, without needing a known category for each row. You choose the number of groups, called k, and the algorithm seeks centers that make observations within each group as close to their center as possible.

For example, a shop might group products using two columns: average monthly sales and average selling price. With k = 3, the result is three product groups whose members have relatively similar values on those measures. The groups do not come with labels such as “premium” or “slow-moving”; a person interprets the patterns after clustering.

The standard objective is to minimize the sum of squared Euclidean distances between each observation and its assigned center. Each center is the arithmetic mean of the observations assigned to it. Because changing an assignment changes a center, and changing a center can change assignments, the algorithm alternates between those two operations.

How the k-means algorithm works

A typical run follows these steps:

  1. Choose k and select starting centers, often by choosing observations from the dataset.
  2. Calculate each observation’s distance to every center.
  3. Assign each observation to its nearest center.
  4. Recalculate every center as the mean of the observations assigned to it.
  5. Repeat the distance and mean calculations until assignments stop changing, or a chosen stopping rule is reached.

This process usually improves the objective at each assignment-and-update cycle, but the final grouping depends on the starting centers. A run can settle on a local solution rather than the best grouping possible across all assignments. Repeating the process with different starting centers is therefore a useful check.

For a small example, consider points (1,1), (1,2), (2,1), (8,8), (8,9), and (9,8), with k = 2. If the starting centers are (1,1) and (8,8), the first three points go to one group and the last three to the other. Their updated centers are (1.33, 1.33) and (8.33, 8.33), respectively, with the displayed values rounded to two decimal places.

Prepare the data before clustering

Use one row per observation and one numeric column per feature. Keep identifiers, names, and notes in separate columns; do not include them in the distance calculation. Remove or resolve blank and invalid feature values first, because missing values can prevent meaningful distance calculations.

Scale can strongly affect the outcome. Suppose one feature is annual income measured in thousands and another is a rating from 1 to 5. The income values may dominate Euclidean distance simply because their numerical range is larger. Standardizing features, commonly by subtracting each feature’s mean and dividing by its standard deviation, can make their contributions more comparable. Whether to scale depends on the meaning of the data: if a feature should have greater influence, preserving or deliberately weighting its scale may be appropriate.

K-means is sensitive to outliers and works best when group centers and averages are meaningful. Extreme values can pull a center away from the bulk of its group. It also requires numeric features; converting categories such as color or region to arbitrary numbers does not make those numbers meaningful distances.

A practical worksheet method in Excel

For Microsoft Excel for Microsoft 365 on Windows, arrange a small two-feature dataset with IDs in A2:A11, feature 1 in B2:B11, and feature 2 in C2:C11. Put three current centers in J2:K4, with feature 1 in column J and feature 2 in column K. The following example uses k = 3; adjust the ranges and center rows to fit your data.

  • In D2, calculate the distance to center 1 with =SQRT(($B2-$J$2)^2+($C2-$K$2)^2). In E2 and F2, use the same pattern with center rows 3 and 4. Fill all three formulas down through row 11.
  • In G2, return the nearest center number with =MATCH(MIN(D2:F2),D2:F2,0). Fill the formula down. A result of 1, 2, or 3 identifies the assigned group.
  • In a separate block, calculate the updated center coordinates. For group 1’s feature 1 mean, use =AVERAGEIF($G$2:$G$11,1,$B$2:$B$11). For its feature 2 mean, use the same formula with $C$2:$C$11 as the average range. Repeat with criteria 2 and 3 for the other groups.

Keep the updated means separate from the current centers. Copy the updated means and paste them as values over J2:K4, then recalculate and inspect the assignments again. This avoids a circular reference: if the current centers depend directly on assignments that depend on those same centers, Excel may report a circular calculation. Continue until the group numbers no longer change. If a group has no members, AVERAGEIF has no values to average; reinitialize that center, then continue.

Using square-root distances in the worksheet is convenient for reading results. For assigning an observation, comparing squared distances gives the same nearest center and avoids square roots. Either way, the k-means objective itself is based on squared distances.

How to model k-means with Solver

A Solver formulation introduces binary assignment variables: one variable for each observation-and-cluster pair, equal to 1 when that observation belongs to that cluster and 0 otherwise. Add a constraint for each observation requiring its assignment variables to sum to 1. Add a nonempty-cluster constraint for each group if every group must contain at least one observation.

Let each cluster also have feature-center variables. Calculate the squared distance from every observation to every center, then minimize the sum of each assignment variable multiplied by its corresponding squared distance. In worksheet terms, the objective can be built from assignment cells and distance cells using SUMPRODUCT. The distance cells depend on the center variables, so the objective combines discrete assignment choices with nonlinear expressions.

In desktop Excel, Solver is an add-in. On Windows, enable it through File > Options > Add-ins; choose Excel Add-ins beside Manage, select Go, and check Solver Add-in. Then open Data > Solver. On Mac, enable the add-in through Tools > Excel Add-ins, then look for Solver on the Data tab. Solver is not a standard k-means feature in Excel for the web, so use desktop Excel for this workflow.

In Solver Parameters, set the objective cell to minimize, select the assignment and center cells as changing cells, and enter the assignment, nonempty-group, and binary restrictions. The exact setup depends on the size and layout of the workbook. Standard Solver is intended for relatively small models, and a model with many observations can quickly create too many decision variables and constraints for a convenient workbook solution.

Why Solver is not always the best choice

The Solver model is not a simple linear program: its distances depend on center variables, and those distances are multiplied by binary assignment variables. Solver’s GRG Nonlinear engine can work with nonlinear formulas, while the Evolutionary engine is intended for problems that are not smooth. The choice of engine and model structure matters, and neither makes a complicated model automatically easy to solve.

Integer restrictions add another challenge. The model can take longer as observations and clusters increase, and the nonconvex objective can make results sensitive to starting values. A Solver result should be treated as a candidate solution, not proof that Excel found the globally best clustering. For a small dataset, compare the result with several starting centers and check whether the groups make sense.

There is also a misconception worth clearing up: the Excel Analysis ToolPak does not provide a standard k-means command. Solver is a general-purpose optimization add-in, not a dedicated clustering tool. If a workbook only needs a repeatable clustering procedure, Solver’s extra variables and constraints may add complexity without adding useful insight.

Check and improve the clustering

Do not judge a result only by whether the formulas stop changing. Review the group sizes, the centers, and the original feature values. An empty group, a group containing almost every row, or a center pulled toward an extreme observation can signal a poor choice of k, unsuitable scaling, or a bad initialization.

Useful checks include:

  • Run the worksheet procedure again with different starting centers and compare the groups and total within-cluster squared distance.
  • Try a few plausible values of k. More groups always give the algorithm more freedom, but extra groups are not automatically more useful.
  • Inspect unusual observations and confirm that units, missing values, and feature scales are handled intentionally.
  • Describe each cluster using its center and a few representative rows, then decide whether the groupings answer the practical question.

If different starts produce very different groupings, that instability is useful information. It may mean the data do not contain clearly separated groups, or that the chosen features and distance measure do not represent the distinctions you care about.

Alternatives to Solver

The worksheet iteration method is often the most transparent Excel-only option for a modest dataset: its formulas expose distances, assignments, and updated centers. It still needs care with initialization, empty clusters, and repeated copying of values. It is less convenient when you need to process many rows, rerun the analysis frequently, or automate a large number of starts.

For larger or repeatable analyses, a statistical environment such as R or Python offers dedicated clustering implementations and more control over initialization, scaling, and diagnostics. Excel can still be useful for preparing and reviewing the data. Choose a tool based on the size of the dataset, the need for reproducibility, and whether colleagues need to inspect the calculation—not just on whether Solver can express the objective.

Frequently Asked Questions

Does Excel have a built-in k-means function?
No standard k-means command is included in Excel for Microsoft 365. You can implement the iterative steps with worksheet formulas or use the Solver add-in to express an optimization model.

Can Excel Solver solve k-means exactly?
Do not assume it will find the global optimum. The model has discrete assignments and nonlinear distance terms, so the result can depend on settings and starting values. Repeated runs and independent checks are sensible.

How do I choose the number of clusters?
There is no universally correct k. Compare a few plausible values, review cluster sizes and centers, and ask whether the groups are useful for the question at hand. A numerical fit measure can help, but it cannot decide whether the clusters are meaningful.

Do I need to standardize variables for k-means?
Not always, but features with much larger numerical ranges can dominate Euclidean distance. Standardize when differences in units or scale should not determine the grouping; preserve scale when its relative influence is intentional.

Why does my Excel clustering worksheet show an error?
Check for blank or nonnumeric feature cells, incorrect ranges, and cluster assignments that leave a group empty. An empty group has no mean center, so initialize it again before continuing. Also ensure the current centers are not formula-linked in a circular way to their own assignments.

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.