PivotTable<\/strong> in Excel based on data ranges from worksheets in a workbook or other workbooks, provided the datasets have similar column structures.<\/p>\n\n\n\nThis tutorial shows how to consolidate multiple ranges into one PivotTable in Excel.<\/p>\n\n\n\n
How to Consolidate Multiple Ranges Into One PivotTable in Excel<\/h2>\n\n\n\n Suppose we have the regional tablets sales data of a particular technology company in four worksheets of a workbook, as shown below:<\/p>\n\n\n\n <\/figure>\n\n\n\nWe want to consolidate the regional sales data in the four worksheets into one PivotTable.<\/p>\n\n\n\n
We use the following steps:<\/p>\n\n\n\n
\nSelect any cell on the first worksheet.<\/li>\n\n\n\n Open the PivotTable and PivotChart<\/strong> Wizard<\/strong> by doing the following:<\/li>\n<\/ol>\n\n\n\n\nPress the Alt<\/strong> key on the keyboard and release it to activate the Key Tips on the Ribbon.<\/li>\n<\/ul>\n\n\n\n <\/figure>\n\n\n\n\nPress the D<\/strong> key and release it. Notice the instruction on the Ribbon, \u201cContinue typing from the menu key sequence from an earlier version of Office or press ESC to cancel.\u201d<\/li>\n<\/ul>\n\n\n\n <\/figure>\n\n\n\n\nPress the P key and release it to launch the PivotTable and PivotChart Wizard<\/strong>.<\/li>\n<\/ul>\n\n\n\n\nOn the PivotTable and PivotChard Wizard \u2013 Step 1 of 3 <\/strong>dialog box, select the Multiple consolidation ranges<\/strong> option and click Next.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nOn the PivotTable and PivotChart Wizard \u2013 Step 2a of 3 <\/strong>dialog box, select the Create a single page field for me<\/strong> option and click Next.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\n\nOn the PivotTable and PivotChart Wizard\u2013 Step 2b<\/strong> dialog box, click the Range Selector<\/strong> button on the Range<\/strong> box, select the dataset on the first worksheet, including the header row, click the Range Selector<\/strong> button again to go back to the entire dialog box, and click the Add<\/strong> button.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nNotice that the dataset’s reference on the first worksheet is added to the All ranges<\/strong> box on the dialog box.<\/p>\n\n\n\n\nRepeat step 5 as you open the other three worksheets and add the data ranges on the worksheets to the All ranges<\/strong> box of the dialog box.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nNote:<\/strong> You can click the Browse<\/strong> button on the dialog box if you want to open and add ranges from other workbooks<\/p>\n\n\n\n\nOnce all the data ranges have been added to the dialog box, click Next.<\/li>\n\n\n\n On the PivotTable and PivotChart Wizard \u2013 Step 3 of 3<\/strong> dialog box, select the New worksheet<\/strong> option to put the PivotTable report on a new worksheet and click Finish<\/strong>.<\/li>\n<\/ol>\n\n\n\n <\/figure>\n\n\n\nThe multiple ranges from different worksheets are finally consolidated into one PivotTable report on a new worksheet:<\/p>\n\n\n\n <\/figure>\n\n\n\nOnce the PivotTable is created, drag and rearrange the rows as needed. For example, if we want the Samsung Galaxy tablet to be on top of the list, we do the following:<\/p>\n\n\n\n
\nSelect cell A11 containing the item \u201cSamsung Galaxy Tab S8 Ultra,\u201d hover the cursor on the top edge of the cell until you see the drag-and-drop icon:<\/li>\n<\/ul>\n\n\n\n <\/figure>\n\n\n\n\nPress and hold down the left mouse button, drag to cell A5 and release the button when you see a dark green line.<\/li>\n<\/ul>\n\n\n\n <\/figure>\n\n\n\nThe Samsung tablet now becomes the first on the list, as shown below:<\/p>\n\n\n\n <\/figure>\n\n\n\nTo update the PivotTable with new data, right-click any cell in the PivotTable and choose Refresh<\/strong> on the shortcut menu.<\/p>\n\n\n\n <\/figure>\n\n\n\nConclusion<\/h2>\n\n\n\n This tutorial showed how to consolidate multiple ranges into one PivotTable in Excel using the PivotTable and PivotChart Wizard<\/strong>. We hope you found the tutorial helpful.<\/p>\n","protected":false},"excerpt":{"rendered":"You can create a PivotTable in Excel based on data ranges from worksheets in a workbook or other workbooks, provided the datasets have…<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n
Consolidate Multiple Ranges into One PivotTable in Excel<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n