Use Excel Pivot Table to create a Crosstab Table
How to use Excel's Pivot Table to group data into ranges and create a crosstab table for easier analysis.
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, knowledge of Pivot Tables is one of the top requirements of many positions.
Sometimes the Pivot Table may have data fields extended to too many columns or rows, and it is better to group the data at certain intervals for better viewing. Such a table with ranged data is called a Crosstab Table.
Steps
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 Value Field Settings… to define the content value. Content values can be set as count, sum, average, max, min, and so on.
Step 4: Group the row parameters in a desired range. Right-click anywhere in the column under Row Labels and select Group.
Step 5: Enter the group parameters (starting point, ending point, and intervals) and click OK to confirm.
Step 6: Apply the same logic to group the Column Labels. Right-click anywhere in the row of Column Labels and select Group.