{"id":15563,"date":"2023-04-27T17:28:52","date_gmt":"2023-04-27T17:28:52","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=15563"},"modified":"2024-03-26T11:06:23","modified_gmt":"2024-03-26T11:06:23","slug":"consolidate-multiple-ranges-into-one-pivottable-in-excel","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/","title":{"rendered":"Consolidate Multiple Ranges into One PivotTable in Excel"},"content":{"rendered":"\n
Consolidate-Multiple-Ranges-into-One-PivotTable-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

You can create a 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\n

This 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\n

We 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

    \n
  1. Select any cell on the first worksheet.<\/li>\n\n\n\n
  2. Open the PivotTable and PivotChart<\/strong> Wizard<\/strong> by doing the following:<\/li>\n<\/ol>\n\n\n\n
      \n
    • Press 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
      \"Graphical<\/figure>\n\n\n\n
        \n
      • Press 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
        \"Graphical<\/figure>\n\n\n\n
          \n
        • Press the P key and release it to launch the PivotTable and PivotChart Wizard<\/strong>.<\/li>\n<\/ul>\n\n\n\n
            \n
          1. On 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
              \n
            1. On 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
                \n
              1. On 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
                \"Graphical<\/figure>\n\n\n\n

                Notice 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

                  \n
                1. Repeat 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
                  \"Graphical<\/figure>\n\n\n\n

                  Note:<\/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

                    \n
                  1. Once all the data ranges have been added to the dialog box, click Next.<\/li>\n\n\n\n
                  2. 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
                    \"Graphical<\/figure>\n\n\n\n

                    The 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\n

                    Once 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

                      \n
                    • Select 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
                      \"Graphical<\/figure>\n\n\n\n
                        \n
                      • Press 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\n

                        The Samsung tablet now becomes the first on the list, as shown below:<\/p>\n\n\n\n

                        \"\"<\/figure>\n\n\n\n

                        To 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

                        \"Graphical<\/figure>\n\n\n\n

                        Conclusion<\/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":"\nConsolidate Multiple Ranges into One PivotTable in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Consolidate Multiple Ranges into One PivotTable in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-27T17:28:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-26T11:06:23+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png\" \/>\n<meta name=\"author\" content=\"Christopher Sirali\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Christopher Sirali\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"5 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\"},\"author\":{\"name\":\"Christopher Sirali\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/b303a8b219a448ca09f1e9eed3903370\"},\"headline\":\"Consolidate Multiple Ranges into One PivotTable in Excel\",\"datePublished\":\"2023-04-27T17:28:52+00:00\",\"dateModified\":\"2024-03-26T11:06:23+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\"},\"wordCount\":543,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\",\"name\":\"Consolidate Multiple Ranges into One PivotTable in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png\",\"datePublished\":\"2023-04-27T17:28:52+00:00\",\"dateModified\":\"2024-03-26T11:06:23+00:00\",\"description\":\"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png\",\"width\":728,\"height\":330},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Consolidate Multiple Ranges into One PivotTable in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/b303a8b219a448ca09f1e9eed3903370\",\"name\":\"Christopher Sirali\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/21aaff468311813001dc2723f1937cf0?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/21aaff468311813001dc2723f1937cf0?s=96&d=mm&r=g\",\"caption\":\"Christopher Sirali\"}}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Consolidate Multiple Ranges into One PivotTable in Excel","description":"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"Consolidate Multiple Ranges into One PivotTable in Excel","og_description":"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.","og_url":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/","article_published_time":"2023-04-27T17:28:52+00:00","article_modified_time":"2024-03-26T11:06:23+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png"}],"author":"Christopher Sirali","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Christopher Sirali","Est. reading time":"5 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/"},"author":{"name":"Christopher Sirali","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/b303a8b219a448ca09f1e9eed3903370"},"headline":"Consolidate Multiple Ranges into One PivotTable in Excel","datePublished":"2023-04-27T17:28:52+00:00","dateModified":"2024-03-26T11:06:23+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/"},"wordCount":543,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/","url":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/","name":"Consolidate Multiple Ranges into One PivotTable in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png","datePublished":"2023-04-27T17:28:52+00:00","dateModified":"2024-03-26T11:06:23+00:00","description":"Learn how to create a PivotTable in Excel based on data ranges from multiple worksheets and consolidate them into one table. Download the file now.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2023\/04\/word-image-15563-1.png","width":728,"height":330},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/consolidate-multiple-ranges-into-one-pivottable-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Consolidate Multiple Ranges into One PivotTable in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/b303a8b219a448ca09f1e9eed3903370","name":"Christopher Sirali","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/21aaff468311813001dc2723f1937cf0?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/21aaff468311813001dc2723f1937cf0?s=96&d=mm&r=g","caption":"Christopher Sirali"}}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/15563"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=15563"}],"version-history":[{"count":1,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/15563\/revisions"}],"predecessor-version":[{"id":15578,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/15563\/revisions\/15578"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=15563"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=15563"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=15563"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}