How to Create Pivot Table from Multiple Sheets in Google Sheets

Creating a pivot table from multiple sheets in Google Sheets requires you to first consolidate your data into a single sheet. Once you have your data in one place, you can create a pivot table to help you analyze and summarize the data more efficiently. Here's how you can do it:

Step 1: Combine data from multiple sheets into a single sheet

1. Create a new sheet in your Google Sheets workbook where you want to consolidate your data.
2. In the first cell of the new sheet, type the following formula:

   ={Sheet1!A1:C; Sheet2!A2:C}

   Replace Sheet1 and Sheet2 with the names of the sheets you want to combine, and A1:C with the range of the data you want to include from each sheet. The semicolon (;) in the formula means to stack the data from each sheet vertically.
3. Press Enter to apply the formula. Your data from the specified sheets and ranges will now be combined in the new sheet.

Step 2: Create a pivot table from the consolidated data

1. Select a cell in the consolidated data sheet.
2. Click on "Data" in the menu bar and choose "Pivot table."
3. In the "Create pivot table" dialog box, the range will be pre-filled with the data you selected. You can adjust the range if needed.
4. Choose whether you want the pivot table to appear in a new sheet or an existing sheet.
5. Click "Create" to create the pivot table.

Step 3: Customize your pivot table

1. In the pivot table editor on the right side of the screen, you can add rows, columns, values, and filters to customize your pivot table according to your needs.
2. Drag and drop the fields from the "Available fields" section to the corresponding areas (Rows, Columns, Values, Filters) to create your desired pivot table layout.
3. Adjust the settings for each field by clicking on the drop-down arrow next to the field name.

Example:

Example: Sales data from two stores

Let's say we have sales data from two stores in different sheets named "Store_A" and "Store_B". We want to create a pivot table to analyze the total sales by product and store.

Step 1: Combine data from Store_A and Store_B sheets

1. Create a new sheet named "Combined_Data."
2. In cell A1 of "Combined_Data" sheet, enter the formula:

   ={Store_A!A1:E; Store_B!A2:E}

   This will combine the data from both sheets, excluding the header row from the second sheet.

Step 2: Create a pivot table from Combined_Data

1. Select a cell in the "Combined_Data" sheet.
2. Click on "Data" in the menu bar and choose "Pivot table."
3. The range should be pre-filled with the combined data. Choose where you want the pivot table to appear and click "Create."

Step 3: Customize the pivot table

1. In the pivot table editor, drag "Product" to the Rows section.
2. Drag "Store" to the Columns section.
3. Drag "Sales" to the Values section and choose "Sum" as the aggregation type.
4. Now, your pivot table will display the total sales by product and store.