Cross Tabulation is a very common model used in statistical analysis. It is helpful for summarizing a longer dataset and making decisions for categorical features. In this tutorial, we will discuss Cross Tabulation and how to do one in Excel.

**Table of Contents**hide

## Download Practice Workbook

You can download the workbook with all the examples used for demonstration and try it yourself from the download link below.

## Overview of Cross Tabulation

**What Is Cross Tabulation?**

Cross tabulation is a statistical model that follows similar patterns. It is also known as contingency tables, cross tabs, etc. It is a quantitative analysis method where we can analyze the relationship between different variables. When studying for the identification of patterns or trends, and correlation between parameters, going through raw data is tiring and repetitive. Thankfully, cross tabs can help us get out of those situations by simply stating parameters such as repetitions of different variables compared with other ones.

**Why Do We Use Cross Tabulation?**

This statistical model helps determine the correlation between variables and how they change from one grouping to another. Then again, the tabulation is also helpful for summarizing a bigger dataset. This helps prevent looking at and analyzing raw datasets and going through each row individually. This also helps us find important pieces of information relatively easily, such as the most valuable employee from a list of employeeâ€™s performances, which product is the most demanding in the current market, etc.

**Example of Cross Tabulation**

There are subtle uses of cross tabulations every day in our life. The nutrition labels or charts behind food packages are examples of cross-tabulations. If you classify some choices of a group of people by their genders or age group, these can be called cross tabulations. For example- pet choices for different genders, opinions based on different ethnic groups, sports performance by age, etc. The possibilities are endless.

## 3 Suitable Examples to Do Cross Tabulation in Excel

In this tutorial, we are going to illustrate three examples of cross tabulation and how to do one in Excel. To summarize we are going to use the Excel pivot table tool that can easily organize the data for us. And thus creating a cross tabulation in Excel based on raw datasets. Although the examples donâ€™t vary much in comparison, these were given keeping in mind that you can do your own cross tabulation in Excel irrespective of your knowledge of pivot tables.

### 1. Cross Tabulation of Player Positions by Teams

In our first example, we are going to do a cross tabulation of the following dataset in Excel.

This dataset contains a list of players, their teams, and the positions they play in.Â We are going to make a cross tabulation on how each position is distributed between two teams. Follow these steps for a detailed guide.

**Steps:**

- First, select the columns you want to base your cross tabulation on.

- Then go to the
**Insert**tab on your ribbon and click on**PivotTable**under the**TablesÂ**group.

- As a result, a box will pop up. Now, select whether you want your cross tab to be in the existing worksheet or a new one, and then click on
**OK**. We are selecting a new worksheet for the table as shown in the figure.

- After that, go to the
**PivotTable Fields**section on the right side of the spreadsheet. Here, you will find the two selected variables- Team and Position. - There, click and drag the
**Team**to the**Rows**Then do the same for**Positions**, but this time drag it to both**Columns**and**Values**field.

- Once you are done, Excel will automatically organize the pivot table to look something like this.

- To eliminate the null values, right-click on any cell of the table and select
**PivotTable Options**from the context menu.

- Now in the
**PivotTable Options**box check the**For empty cells show**option under**Format**in the**Layout & Format**tab and put in the value**0**in it.

- Finally, click on
**OK**.

The cross tabulation is now complete for the dataset, which will look something like this.

**Interpretation of the Result**

From the cross tab above, here is what we can interpret:

- A total of 4 players are from the Bulls and 5 players are from the Lakers.
- There are a total of 3 Center positioned players on the list. 2 of them are from the Lakers and 1 is from the Bulls.
- Both teams have one player who plays as PG.
- There is only one player who plays as SF in the dataset and he plays for the Bulls.
- At the same time, there is 1 player who plays as SG in the Bulls and two from the Lakers.

### 2. Cross Tabulation of Cars Owned by Customer Age

Now letâ€™s take a look at a different dataset where there is a possibility of grouping in variables.

This dataset contains a list of people of different ages who own cars from different companies. We are going to use a pivot table to make a cross tabulation of the type of cars owned by different age groups. Follow these steps to see how you can do that.

**Steps:**

- First of all, select the columns for cross tabulation.

- Then go to the
**Insert**tab on your ribbon. - Now select
**PivotTables**from the**TablesÂ**group.

- Consequently, a pivot table box will pop up. Now select where you want your cross tab to be, then click on
**OK**.

- Next, go to the
**PivotTable Fields**on the right side of the spreadsheet and click and drag**Age**to the**RowsÂ**field. - Then click and drag
**Car**to both the**Columns**and**Values**This should look something like this in the figure.

- As a result of these steps, the pivot table will automatically appear like this at the intended place.

- To remove null values, right-click on any of the cells on the pivot table and select
**PivotTable**options from the context menu.

- After that, in the
**PivotTable Options**box, select the**Layout & Format**Now check the**For empty cells show**option and put a**0**in the field.

- After clicking on
**OK**the pivot table will look something like this.

- To group the ages. right-click on any of the row labels and select
**Group**from the context menu.

- Next, select the starting, ending, and the intervals of the age group you want, and then click on
**OK**.

Finally, the pivot table will have the illustration of cross tabulation which will look something like this.

**Interpretation of the Result**

The contingency table above can be used to come up with the following decisions:

- There are a total of 3 people in all of the 25-34,35-44,45-54 age groups and 2 people belong to the 55-64 age group.
- Of the three people in 25-34 age category, one owns BMW, one owns Toyota and the other owns Volkswagen.
- One person from the 35-44 age category owns one BMW, one owns a Toyota and the other has a Volkswagen.
- In our next age category of 45-54, two of them own Cadillac and one owns Toyota.
- Finally, in our last age group, one person owns a BMW and another one owns Cadillac.
- It can also be easily said that Cadillac is popular among people of higher age and people on the younger side prefer Volkswagen more than their older counterparts. Other cars do not have any age-specific owners.

### 3. Cross Tabulation of Vaccination Status by Age

In our third example, we will use a similar dataset but differentiated by text values in the cells.

The dataset contains a list of children, their age, and their vaccinated status. We are going to do a cross tabulation based on this dataset in Excel and interpret our result in the end. Follow these steps for a more detailed guide.

**Steps:**

- First of all, select the columns for cross tabulation.

- Then go to the
**Insert**tab on your ribbon and select**PivotTables**from the**TablesÂ**group.

- After that, select where you want to put the cross tab in and then click on
**OK**.

- Now go to the
**PivotTable Fields**on the right side of the spreadsheet. Click and drag**Age**to the**Rows**Do the same twice for the**Vaccinated? v**ariable. It should look something like this shown in the figure.

- As a result, a pivot table will pop up in the spreadsheet that illustrates a cross tabulation.

- To eliminate the null values, right-click on any of the cells of the table and select
**PivotTable Options**from the context menu.

- Next, select the
**Layout & Format**tab, check**For empty cells show**option under**Format**, and put the value**0**in the field.

- After clicking on
**OK**the cross tabulation will look something like this.

**Interpretation of the Result**

Finally, we can come to these decisions from the table:

- There is at least one child in every age group from 11 to 18, except 13.
- A total of 15 children were in the dataset. 7 of these children arenâ€™t vaccinated. While 8 of them are.
- The most non-vaccinated children are of age 18, the number of them is 5. None of the 18-year-olds are vaccinated.
- Similarly, all 12-year-olds and 14-year-olds are vaccinated. Which is also the dominant age group in terms of vaccinated numbers.
- The rest of the age groups have only one member in them. Of them, two are vaccinated and two arenâ€™t.

## Things to Remember

- While selecting columns from a dataset for pivot tables, make sure to select the whole columns with the headers for the pivot table.
- Put the correct variables in the correct fields. You can still work around that, but it involves unnecessary steps for just cross tabulations.
- If you want to group the row labels, only click on the cells in the row labels (the first column of the pivot table). Otherwise, the option will not appear on the context menu.

## Conclusion

These were different scenarios of how to do cross tabulation in Excel. Hope you have got a grasp of it and can do your own cross tabulations in Excel. I hope you found this guide helpful and informative. If you have any questions or suggestions, let us know below.

For more guides like this, visit **Exceldemy.com**.