Excel Pivot Table Input across two Columns-CodePudding

I am surprised this was not asked before; at least I could not find the answer.

I have data for a pivot table in two columns and would like to have the pivot table treat those two columns as if there was only one column. Is there a simple way to do this? Of course I could copy column two at the end of column one but in my actual case I have 2 million rows and then this would not be possible.

Here the simple example:

CodePudding user response：

You can do this by creating two tables and then appending them with power query:

1- Select the range in column A

2- Click the Data menu

3- Click From Table/Range

4- Create the table (OK) (this will open the power query window)

5- From the dropdown Close & Load select Close & Load To ..

6- Select Only Create Connection

7- Select the range in column B

8- Repeat steps 2 to 4

9- Select Append Queries

10- In the dropdown select the first table created

11- From the dropdown Close & Load select Close & Load To

12- Select Pivot Table Report

Now you have a data source for your Pivot table with both columns combined, please note that both columns should have the same name for the Append query to work as desired.