How to Analyze Two Columns of Data as One in Excel

A pivot table is a powerful tool that allows you to summarize and analyze large amounts of data in Excel. It lets you rearrange, filter, group, and aggregate data by dragging and dropping fields into different areas of the table.

One of the common tasks that a pivot table can help you with is analyzing two columns of data as if they were a single column. For example, suppose you have a sales data set that contains two columns: Product and Quantity. You want to know the total quantity sold for each product, regardless of the order date or customer name. A pivot table can easily do this by putting the Product field in the Rows area and the Quantity field in the Values area.

However, what if you have two columns of data that are related, but not exactly the same? For example, suppose you have a survey data set that contains two columns: Gender and Rating. You want to know the average rating for each gender, as well as the overall average rating for all respondents. A pivot table can do this by putting the Gender field in the Rows area and the Rating field in the Values area, but this will only give you the average rating for each gender separately. How can you get the overall average rating as well?

One way to solve this problem is to create a new column that combines the two columns of data into one. For example, you can create a column called Gender_Rating that concatenates the values of Gender and Rating, separated by a dash. For example, if a respondent is Male and gives a rating of 4, the value of Gender_Rating will be Male-4. Then, you can use this new column as a field in the pivot table, and group it by the first part (Gender) or the second part (Rating) as needed.

Procedures

To create a pivot table that analyzes two columns of data as one, follow these steps:

1. Create a new column that combines the two columns of data into one, using a formula such as =A2&“-”&B2, where A2 and B2 are the cells that contain the values of the two columns. Copy this formula down for all the rows in the data set.
2. Select any cell in the data set, and go to the Insert tab. Click on PivotTable, and choose a location for the pivot table (such as a new worksheet).
3. In the PivotTable Fields pane, drag the new column (such as Gender_Rating) to the Rows area. This will create a list of all the possible combinations of the two columns of data.
4. Drag the same column (such as Gender_Rating) to the Values area. This will create a count of how many times each combination occurs in the data set.
5. To group the data by the first part of the column (such as Gender), right-click on any cell in the Rows area, and choose Group. In the Grouping dialog box, enter a dash (-) in the Starting at and Ending at boxes, and click OK. This will create a group for each value of the first part of the column, and show the count of each combination within the group.
6. To group the data by the second part of the column (such as Rating), right-click on any cell in the Rows area, and choose Ungroup. Then, right-click on any cell again, and choose Group. In the Grouping dialog box, enter a dash (-) in the By box, and click OK. This will create a group for each value of the second part of the column, and show the count of each combination within the group.
7. To calculate the average of the second part of the column (such as Rating), drag the same column (such as Gender_Rating) to the Values area again. Right-click on any cell in the Values area, and choose Value Field Settings. In the Value Field Settings dialog box, choose Average from the Summarize value field by list, and click OK. This will show the average of the second part of the column for each group.

Explanation

To illustrate the procedures above, let’s use a sample data set that contains the responses of 20 people to a survey question: How satisfied are you with our service? The data set has two columns: Gender and Rating, where Rating is a number from 1 to 5, with 1 being the lowest and 5 being the highest. The data set looks like this:

We want to create a pivot table that analyzes the two columns of data as one, and shows the following information:

• The total number of respondents for each gender
• The total number of respondents for each rating
• The average rating for each gender
• The overall average rating for all respondents

To do this, we first create a new column called Gender_Rating that combines the values of Gender and Rating, separated by a dash. The formula for the first row is =A2&“-”&B2, and we copy it down for all the rows. The new column looks like this:

Next, we select any cell in the data set, and go to the Insert tab. We click on PivotTable, and choose a location for the pivot table (such as a new worksheet). In the PivotTable Fields pane, we drag the Gender_Rating column to the Rows area and the Values area. This creates a pivot table that looks like this:

This shows the count of how many times each combination of Gender and Rating occurs in the data set. To group the data by Gender, we right-click on any cell in the Rows area, and choose Group. In the Grouping dialog box, we enter a dash (-) in the Starting at and Ending at boxes, and click OK. This creates a group for each value of Gender, and shows the count of each combination within the group. The pivot table now looks like this:

This shows the total number of respondents for each gender. To group the data by Rating, we right-click on any cell in the Rows area, and choose Ungroup. Then, we right-click on any cell again, and choose Group. In the Grouping dialog box, we enter a dash (-) in the By box, and click OK. This creates a group for each value of Rating, and shows the count of each combination within the group. The pivot table now looks like this:

This shows the total number of respondents for each rating. To calculate the average rating for each gender, we drag the Gender_Rating column to the Values area again. We right-click on any cell in the Values area, and choose Value Field Settings. In the Value Field Settings dialog box, we choose Average from the Summarize value field by list, and click OK. This will show the average of the second part of the column for each group. The pivot table now looks like this:

This shows the average rating for each gender and the overall average rating for all respondents. We can see that the average rating for females is 3.5, and the average rating for males is 4. We can also see that the overall average rating is 3.75.