create pivot table from 2 sheets excel

Merge 2 Sheets: Create Pivot Table in Excel

By Matthew Lewis
AD

When working with large datasets in Excel, it's often necessary to analyze and compare data across different worksheets. A common method for doing this is to create a pivot table from two or more sheets. Pivot tables allow you to summarize and manipulate data quickly and efficiently, making them an invaluable tool for data analysis. In this guide, we'll walk you through the process of creating a pivot table from two sheets in Excel.

Before we dive into the steps, it's important to ensure that your data is structured properly in both sheets. Each sheet should have a clear header row, with no blank columns or rows within your data range. Once your data is ready, let's get started.

Preparing Your Worksheets

To create a pivot table from two sheets, you first need to ensure both sheets are ready. Make sure your data is clean and formatted consistently, with no errors or missing values. It's also a good idea to freeze the top row on each sheet to keep your headers visible when working with large datasets.

AD
How to Make and Use Pivot Table in Excel
How to Make and Use Pivot Table in Excel
Create PivotTable from Multiple Sheets in Excel – Top 2 Methods
Create PivotTable from Multiple Sheets in Excel – Top 2 Methods
How to Create a Pivot Table in Excel | Beginner Excel Guide | Printable Visual Tutorial
How to Create a Pivot Table in Excel | Beginner Excel Guide | Printable Visual Tutorial

To freeze the top row on a sheet, simply click on the row below the headers (e.g., row 2), then go to the "View" tab in the ribbon, and click on "Freeze Panes." Select "Freeze Top Row" from the dropdown menu. This will keep your headers visible as you scroll down through your data.

Combining Data from Two Sheets

Before creating a pivot table, you need to combine the data from both sheets into a single table. To do this, you can use a simple formula to insert the data from one sheet into the other. Let's assume your data starts in cell A1 of both sheets, with headers in row 1.

How to Create Pivot Tables in Excel
How to Create Pivot Tables in Excel
How to Make Basic pivot Table in Excel with examples.
How to Make Basic pivot Table in Excel with examples.
Remove Data Model from Pivot Table in Excel (with Easy Steps) - ExcelDemy
Remove Data Model from Pivot Table in Excel (with Easy Steps) - ExcelDemy

In the sheet where you want to combine the data (let's call it Sheet2), enter the following formula in cell A2 (or A3, if your headers are merged):

=IFERROR(INDEX(Sheet1!A:A,ROW()-1),"")

AD

Press Enter, and Excel will pull in the data from cell A1 of Sheet1 into cell A2 of Sheet2. Drag the formula down to copy it for the rest of your rows, and Excel will automatically adjust the row number in the formula. If Sheet1 has fewer rows than Sheet2, the blank cells in Sheet2 will be filled with an error, which will be converted to blank using the IFERROR function.

How to Merge Two Pivot Tables in Excel (with Quick Steps) - ExcelDemy
How to Merge Two Pivot Tables in Excel (with Quick Steps) - ExcelDemy
the pivot tables training poster is shown in blue and green, with instructions on how to
the pivot tables training poster is shown in blue and green, with instructions on how to
Learn Excel Pivot Tables in just 1 Hour!
Learn Excel Pivot Tables in just 1 Hour!
the pivot table guide is shown in green and white, with information on it
the pivot table guide is shown in green and white, with information on it
Excel pivot tables tutorial for beginners
Excel pivot tables tutorial for beginners
How to Make a Wind Rose in Excel: 2 Easy Methods - ExcelDemy
How to Make a Wind Rose in Excel: 2 Easy Methods - ExcelDemy

Using Index and Row Functions

The INDEX function in Excel allows you to access a specific cell in a range, based on its row and column number. In our formula, INDEX(Sheet1!A:A,ROW()-1) refers to the cell in column A of Sheet1 that corresponds to the current row number minus one. For example, if the active cell is A5, the INDEX function will return the value from cell A4 of Sheet1.

The ROW() function returns the row number of the current cell. In our formula, ROW()-1 ensures that we start pulling data from the first row of Sheet1, instead of the header row. IFERROR is used to convert any errors (e.g., if there are fewer rows in Sheet1 than in Sheet2) into blank cells.

AD

Combining Data with Additional Columns

If your data in Sheet2 has additional columns that you want to include in the pivot table, you can use a similar approach to combine the data. Let's say your data in Sheet2 has two additional columns (Column B and Column C), and you want to include them in your pivot table. In cell B2 of Sheet2, enter the following formula:

a table with data and pivot tables on it
a table with data and pivot tables on it

=IFERROR(INDEX(Sheet1!B:B,ROW()-1),"")

And in cell C2, enter:

AD

=IFERROR(INDEX(Sheet1!C:C,ROW()-1),"")

Drag these formulas down to copy them for the rest of your rows, just like you did with the A column formula. Now, your combined data in Sheet2 will include columns A, B, and C from Sheet1, as well as any additional columns from Sheet2 that you want to keep.

Creating the Pivot Table

Now that your data is combined into a single table in Sheet2, you can create a pivot table to analyze and summarize your data. Select any cell within your combined data range, then go to the "Insert" tab in the ribbon, and click on "PivotTable." In the Create PivotTable dialog box, ensure that the data range is correct, and choose where you want to place the pivot table (you can place it on a new sheet or in an existing sheet). Click OK to create the pivot table.

Once your pivot table is created, you can customize it by dragging and dropping fields into the Rows, Columns, Values, and Filters areas. To add a slicer for filtering data, click on any cell within the pivot table, then go to the "Analyze" tab (under "PivotTable") in the ribbon, and click on "Insert Slicer." Select the field you want to filter, and click OK. Now, you can use the slicer to filter your pivot table data quickly and easily.

Refreshing Your Pivot Table

If you make changes to your data in Sheet2 (or Sheet1), you can update your pivot table to reflect those changes. To refresh the pivot table, simply right-click on any cell within the pivot table, and select "Refresh" from the context menu. Alternatively, you can go to the "Analyze" tab (under "PivotTable") in the ribbon, and click on "Refresh." This will update your pivot table with the latest data from Sheet2.

You can also set your pivot table to refresh automatically whenever you make changes to your data. To do this, right-click on any cell within the pivot table, and select "Change Data Source" from the context menu. In the Change PivotTable dialog box, click on the Options button. In the Options dialog box, select "Refresh data when opening the file" under "Refresh," and click OK. Now, your pivot table will automatically update whenever you open the workbook.

Creating a pivot table from two sheets in Excel is a powerful way to analyze and compare data across different worksheets. By combining data and using pivot tables, you can unlock valuable insights and make data-driven decisions. As you continue to work with pivot tables, you'll discover endless possibilities for customizing and manipulating your data. So go forth, and happy pivoting!

AD