Use Excel Pivot Table to create a Crosstab Table
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.
Step 1: Prepare the data.
Step 2: Insert a Pivot Table and define the data range.
Step 3: Decide the row and column parameters and content values.
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.
Step 4: Group the row parameters in a desired range. Right click anywhere in the column under the Row Labels, and select “Group”.
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”.
