Multi-way crosstabs tutorial in Excel

This tutorial shows how to create two types of multiway cross tables (or crosstabs) that XLSTAT can help you create. XLSTAT can work with different data formats including flat files (txt or csv for example). This tutorial is based on data that are already available in Excel format. You can download the data by clicking on the Dataset button.

Data to create multi-way crosstabs

The data correspond to an extensive survey that was run in a business school, on four different campuses, in order to evaluate the alcohol consumption of students and the potential social and psychological impact of COVID. The data are real-life data, but their origin is kept confidential upon request of the authors, and many variables have been removed as they are useless for this tutorial. The variables with a red label correspond to synthetic indexes that were computed on some of the removed variables.

Our goal here is to have a first look at some of the dimensions, by constructing pivot tables presenting in a simple way the key information.

Setting up side-by-side multi-way crosstabs in XLSTAT

For the first analysis, we want to quickly see how gender, living, and campus influence the level of alcoholic dependency (Resdrink is a summary index indicating the level of consumption of alcohol, the lower it is the more the student drinks; the scale is from 1 to 5), and if there is an interaction between living or gender with campus for Resdrink. We should expect that there is no interaction, as while the campuses are in very different regions, the students come from many different regions or countries on each campus.

• Go to Describing data and choose Multiway crosstabs generator.

• Select the whole table and choose the side-by-side option.

• Click OK and on the second dialog box, select the following options (Totals cannot be computed for the Side by side layout):

• Select gender and living for the row variables and campus for the columns of the crosstab.

• Select Resdrink to compute its mean.

Note: You can change the order of the variables using the up/down arrows, and you can type the first letter of a variable to reach it faster in the lists).

• Click OK to let XLSTAT compute the crosstab. It is then displayed. Use the XLSTAT magic wand to color the values on a blue-> light blue scale.

An expected result is that men consume more alcohol than women, but it is surprising to see that students that live with their partner drink on average more than students that live in a flatshare. We can also see that the consumption of alcohol is lower on campus 3 but very similar on the 3 other ones. On campuses 3 and 4, we see that living with flatmates is a factor in lower consumption. On campus 3, living with parents has a similar impact, but not on campus 4. These results should be checked for their significance, using either chi-square tests or ANOVA-type analyses.

Setting up nested multi-way crosstabs in XLSTAT

• Imagine now that we want to see if there is an interaction between gender and living.

• Open again the dialog box and choose the Nested layout.

Then choose the same variables as with the side-by-side layout.

• Click OK to generate the new cross table.

In the above table, we see those men living with their partner or “other” are those that drink the most, while women tend to drink more when they live with flatmates. There is clearly an interaction effect between gender and living as we can see that living with a partner, has a positive effect on alcohol consumption for men and a negative for women.

There can be a third-level interaction (gender*living*campus) given that the previous is mostly true for campuses 3 and 4. These results should be checked for their significance, using either chi-square tests or ANOVA-type analyses. the white cells correspond to interactions that do not exist in the data.