Use Excel Pivot Table to create a Crosstab Table

msofficeshare logo

File to download:  None

Related article:  Convert a Crosstab Table to Database data format

 

Pivot Table is extremely useful for massive data analysis.  It allows users to slice and dice the data in many useful ways.  In fact, if you are looking for a career in the business fields, you may see that the knowledge of using Pivot Table is one of the top requirements of many positions.

However, sometimes the Pivot Table may have data fields extended to too many columns or rows and it is better to group the data with certain intervals for better viewing.  Such table with ranged data is called Crosstab Table.

 

The following steps show how to create a Crosstab Table with the use of Pivot Table.

crosstab table logo
pic1 crosstab table

Step 1:  Prepare the data.

pic2 crosstab table

Step 2:  Insert a Pivot Table and define the data range.

pic3 crosstab table

Step 3:  Decide the row and column parameters and content values.

pic4 crosstab table

Click the down triangle and select the “Value Field Settings…” to define the content value.  Content values can be set as count, sum, average, max, min …, etc.

pic5 crosstab table
pic6 crosstab table

Step 4:  Group the row parameters in a desired range.  Right click anywhere in the column under the Row Labels, and select “Group”.

pic7 crosstab table
pic8 crosstab table

Step 5:  Enter the group parameters (starting point, ending point, and the intervals) and click “OK” button to confirm.

Step 6:  Applied the same logic to group the Column Labels.  Right click anywhere in the row of the Column Labels, and select “Group”.

pic9 crosstab table
pic10 crosstab table