Stack / unstack data in Excel

This tutorial shows how to easily switch from stacked to unstacked data formats, and vice versa, in Excel using the XLSTAT software.

Goal of this tutorial

We have obtained results from an experiment with one column per group. We wish to run a one-way Analysis of variance on these results. The data has to be transformed so that an ANOVA can be applied.

Dataset for stacking variables

The dataset contains three columns, each one attached to a group of patients.

Stacking variables

After opening XLSTAT, select the XLSTAT / Preparing data / Data Management command, or click on the corresponding button of the Preparing data menu (see below).

Once you've clicked on the relevant button, the dialog box appears. Select the method, we use stack. Select the data you want the stack function to be applied to in the data field.
In this analysis, it is possible to choose to stack only certain columns in your dataset. If you check this option, a second interface appears when launching the analysis allowing you to select which variables you wish to stack, the other variables will be added after the table and duplicated for each “group”.

Once you have clicked on the OK button, the results are displayed on a new sheet.

Results of the stacking process

The only result that is displayed is the transformed dataset. Instead of having 3 columns, it contains 2 columns, the first one indicating the group and the second one the value of the transformed variable.

You can now apply a one-way ANOVA on the obtained dataset. Here is a detailed tutorial on one-way analysis of variance.