XLSTAT: Clustering big datasets using k-means then AHC in Excel

Tutorial explaining how to segment large datasets in Excel using XLSTAT by combining k-means clustering with Agglomerative Hierarchical Clustering (AHC) to obtain final clusters.

Jul 23, 2026
Knowledge

Title

XLSTAT: Clustering big datasets using k-means then AHC in Excel

Answer

XLSTAT: Clustering big datasets using k-means then AHC

Dataset for Example

The data are from the US Census Bureau and describe the changes in the population of 51 states between 2000 and 2001. The initial dataset has been transformed to rates per 1000 inhabitants, with the data for 2001 serving as the focus for the analysis. Our aim is to create homogeneous clusters of states based on the demographic data we have available. This dataset is not very big but it will illustrate how to deal with much bigger dataset.

Note: if you try to re-run the same analysis as described below on the same data, as the k-means method starts from randomly selected clusters, you may obtain different results from those listed hereunder. To fix the seed, go to the XLSTAT Options, Advanced tab, then check the "fix the seed" option.

Setting Up

Open XLSTAT.
Click on the Analyzing data menu and select the k-means clustering feature:

Once you've clicked the button, the k-means clustering dialog box appears.

In the General tab:

Select the data on the Excel sheet with the mouse. (Note: There are several ways of selecting data with XLSTAT - for further information, please check the tutorial on selecting data) In this example, the data start from the first row, so it is quicker and easier to use the "column selection" mode. This explains why the letters corresponding to the columns are displayed in the selection boxes (C to H).
The Total population variable was not selected, as we are interested mainly in the demographic dynamics. The last column was not selected as it is fully correlated with the column preceding it. The observations labels were selected as they are available.
We set the number of groups to be created to 25. In the case of much bigger dataset you may use a bigger number.
The selected criterion is "Determinant(W)" as it allows to remove the scale effects of the variables.

In the options tab we increased the number of repetitions to 50 in order to increase the quality and the stability of the results.

In the Outputs tab we select only the Centroids which we will use in the AHC, the results by class as it will give us the samples within each class and the results by object to get the table of the sample with an attribution variable.

Click on the OK button to launch the computations.
The results are displayed on a new Excel sheet.

Agglomerative Hierarchical Clustering on the results of the k-means clustering

We are going to work on the Class centroids table.

Another important table is the table containing the information about which states are clustered together.

Open XLSTAT.
Click on the Analyzing data menu and select the Agglomerative Hierarchical Clustering feature:

In the General tab:

You need to select the data to cluster. Select the original variables describing the 25 classes in the Class centroids .
We will use the Proximity type : Dissimilarities and the Euclidian distance , as well as the Ward’s method as the agglomeration method.
We have the name of the variables included in the selection so we tick the option Column labels and select the Row labels that are the cluster number (1-25).
We will use the Row weights option and select the column Sum of weights of the same table Class centroids .

In the Options tab we confirm we want to cluster the rows as the classes are in rows but in this type of clustering (AHC after k-means) you need to include the Within-class variances . You will find this information in the same table as before: Class centroids, in the last column Within class variance .

We can select all the Outputs for this analysis.

Finally in the tab Charts, select all the charts. Pay special attention the the dendrogram type and select the option Vertical.
Click on the OK button to launch the computations.
The results are displayed on a new Excel sheet.

Results of the Agglomerative Hierarchical Clustering

In the results of the AHC, look at the two dendrogams that give you the composition of the 3 clusters. You can see how the 25 clusters are clustered in three final clusters.

Also you can see the decomposition of the variance.

You can finally use the table obtained in the AHC to recode the table obtained in the k-means clustering so as to have the final results. Go to XLSTAT / Preparing data / Coding .

You need to select the column Class from the classification table obtained in the k-means clustering as the variable to recode. Select the table Result by object from the AHC including the name of the columns as the coding table. You then select the option Column labels .

To append the new column to the first table select the option Range and the first cell next to the table. Also untick the option Display report header so as to not have anything else displayed.

Finally you obtained the results of the classification for the all the states.

URL Name

6567-clustering-big-datasets-using-k-means-then-ahc