How to Do Cross Tabulation in Excel (3 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


How to Do Cross Tabulation in Excel: 3 Suitable Examples

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.

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

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

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

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.

Read More: How to Make a Contingency Table in Excel


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.

how to do cross tabulation in excel

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

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

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

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

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

how to do cross tabulation in excel

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

how to do cross tabulation in excel

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

how to do cross tabulation in excel

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.

Read More: How to Make a Categorical Frequency Table in Excel


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.

how to do cross tabulation in excel

  • 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? variable. It should look something like this shown in the figure.

how to do cross tabulation in excel

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

how to do cross tabulation in excel

  • 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.

how to do cross tabulation in excel

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.

Read More: How to Make a Relative Frequency Table in Excel


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.

Download Practice Workbook


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.


Related Articles


<< Go Back to Frequency Distribution in Excel | Excel for Statistics | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Abrar-ur-Rahman Niloy
Abrar-ur-Rahman Niloy

Abrar-ur-Rahman Niloy, holding a B.Sc. in Naval Architecture and Marine Engineering, has contributed to Exceldemy for nearly 1.5 years. As a leader in Excel, VBA, and Content Development teams, he authored 114+ articles and assisted the Exceldemy forum. Presently, as a project writer, he prioritizes stepping out of his comfort zone, aiming for constant technical improvement. Niloy's interests encompass Excel & VBA, Pivot Table, Power Query, Python, Data Analysis, and Machine Learning libraries, showcasing his commitment to diverse... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo