Use Multiple Consolidation Ranges to merge multiple tables into a pivot table
If you want to subtotal the sales of a product from January to December, and each month's sales are a separate worksheet, you need to use the Multiple Consolidation Ranges to merge multiple tables into a pivot table in Excel.
Chapters:
0:00 Use Multiple Consolidation Ranges to merge multiple tables into a pivot table
2:03 Adjust the rows and columns of the page to show as a table
2:36 Modify the header name
3:15 Replace the Count with the Sum
3:28 Remove Subtotal Row
3:38 Adjust the order of the fields
Description:
Use Multiple Consolidation Ranges to merge multiple tables into a pivot table
Merging multiple tables into a pivot table is relatively simple, and the trouble is adjusting the desired subtotaled results. The items that need to be adjusted usually include pages, rows, and columns, and the items under the page need to be renamed and reordered.
One. Use Multiple Consolidation Ranges to merge multiple tables into a pivot table
1. Switch to the first worksheet we want to merge into the pivot table (for example, 9), hold down Alt, press D, P once on the keyboard, respectively, to open the "PivotTable and PivotChart Wizard" window, and select "Multiple Consolidation Ranges"; Click "Next" to go to step 2, "Please specify the number of page fields you want" and select "Create a single page field for me" for "How many page fields do you want?"; Click "Next" to switch to the Add Table ranges window, select the range of 9, then the selected range will be automatically filled into the input box under "Range", click "Add", then this range will be added to "All Ranges", continue to select the ranges of other tables and add them to "All Ranges", after adding the table, click "Next"; Switch to the "Where do you want to put the PivotTable report" window, select "New Worksheet", and click Finish, then the selected tables are merged into the PivotTable.
2.If we select "I will create the page fields" for "How many page fields do you want?", we can select 1 to 4 page fields and add one page field for a table. For example, add the worksheet 9 to "Field one", and add the worksheet 10 to "Field two". After selecting the 9 and 10, select 2 for "How many page fields do you want?", enter 9 under "Field one", and enter 10 under "Page two".
Two. Adjust the Pivot Table and Subtotal
(1) Adjust the rows and columns of the page to show as a table
1. Drag "Column" from the list box of "Columns" to the list box of "Rows", and then drag "Page 1" from "Filter" to "Columns"; Right-click one of the cell in the table (for example, A5), select "PivotTab;e Options" in the pop-up menu, open the "PivotTable Options" window, select the "Display" tab, check "Classic PivotTable Layout (Enables dragging of fields in the grid)", and click "OK", the pivot table will be displayed in tabular form.
2. Among them, shows the clothing number in the Row, and shows the remaining three fields in the Column, namely "Product Name, Price and Sales"; Items 1 to 3 under Page 1 are the counting results of each field in the original table September to November.
(2) Modify the header name
1. Select cell C3 where "Page 1" is located, select the "Analyze" tab, select "Page 1" in the "Active Field" input box in the upper left corner of the screen, and enter "Month"; Select cell C4 where Item 1 is located, hold down Ctrl, click C4, enter 10, and click C5 to exit the input state.
2. In the same way, replace "item 2 and Item 3" with "10 and 11".
(3) Replace the Count with the Sum
1. Right-click cell A3, and select "Summarize values by → Sum" in the pop-up menu, then the calculation type will change from "Count" to "Sum".
2. Remove Subtotal Row
(4) Adjust the order of fields
Select cell E4 where "9" is located, move the pointer to the right border of E4, the pointer will change to a cross with an arrow, hold down the left button, drag to the right to column C, and release the left button when the thick green line stops on the right side of column C, then adjust the order.
Watch video Use Multiple Consolidation Ranges to merge multiple tables into a pivot table online without registration, duration hours minute second in high quality. This video was added by user Loinsure 05 June 2024, don't forget to share it with your friends and acquaintances, it has been viewed on our site 111 once and liked it 0 people.