{"id":4182,"date":"2019-04-03T17:09:53","date_gmt":"2019-04-03T17:09:53","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=4182"},"modified":"2024-03-26T11:48:37","modified_gmt":"2024-03-26T11:48:37","slug":"pivot-table-from-multiple-worksheets","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/pivot-table-from-multiple-worksheets\/","title":{"rendered":"Create Pivot Table from Multiple Worksheets"},"content":{"rendered":"\n
If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you need are integrated with Excel.<\/p>\n\n\n\n
In earlier Excel versions, you had to use PowerPivot to achieve the same result.<\/p>\n\n\n\n
In this tutorial, we\nhave three sheets: Customer<\/strong>, Order<\/strong>, and Payment<\/strong>.<\/p>\n\n\n\nCustomer<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nOrder<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nPayment<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nAll the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.<\/p>\n\n\n\nEach of these tables\nhas a column Order_id<\/strong> with unique\nvalues.<\/p>\n\n\n\nCreating and naming tables<\/h2>\n\n\n\nFirst, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Customer<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nOrder<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nPayment<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nAll the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.<\/p>\n\n\n\nEach of these tables\nhas a column Order_id<\/strong> with unique\nvalues.<\/p>\n\n\n\nCreating and naming tables<\/h2>\n\n\n\nFirst, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Order<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nPayment<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nAll the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.<\/p>\n\n\n\nEach of these tables\nhas a column Order_id<\/strong> with unique\nvalues.<\/p>\n\n\n\nCreating and naming tables<\/h2>\n\n\n\nFirst, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Payment<\/strong><\/p>\n\n\n\n<\/figure>\n\n\n\nAll the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.<\/p>\n\n\n\nEach of these tables\nhas a column Order_id<\/strong> with unique\nvalues.<\/p>\n\n\n\nCreating and naming tables<\/h2>\n\n\n\nFirst, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
All the necessary data are split between them. We have to combine them using a tool that comes with PivotTable.<\/p>\n\n\n\n
Each of these tables\nhas a column Order_id<\/strong> with unique\nvalues.<\/p>\n\n\n\nCreating and naming tables<\/h2>\n\n\n\nFirst, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
First, let\u2019s create a table from each of them. Click the\ndata inside the first sheet (Customer<\/strong>)\nand navigate to Insert >> Tables\n>> Table<\/strong> or use Ctrl + T<\/strong>.<\/p>\n\n\n\nCreate tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Create tables on other sheets (Order<\/strong> and Payment<\/strong>).<\/p>\n\n\n\nClick inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Click inside each table and navigate to Table Tools >> Design >> Properties<\/strong> and change their names to Customer<\/strong>, Order<\/strong>, and Payment<\/strong> accordingly.<\/p>\n\n\n\n<\/figure>\n\n\n\nInserting PivotTable<\/h2>\n\n\n\nIt\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
It\u2019s time to insert a Pivot Table. Click the first Table and navigate to Insert >> Table >> PivotTable<\/strong>. Normally you would click OK and start working with a PivotTable.<\/p>\n\n\n\nBut this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
But this time check the checkbox Add this data to the Data Model<\/strong> in order to work with multiple tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nRename the new sheet to PivotTable.<\/p>\n\n\n\nNow, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Rename the new sheet to PivotTable.<\/p>\n\n\n\n
Now, if you look at the PivotTable Fields, you will notice\nthat there are additional buttons there: Active<\/strong>\nand All<\/strong>.<\/p>\n\n\n\nClick All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Click All<\/strong> to display all tables.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\nCreating relationships<\/h2>\n\n\n\nAl columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We can\u2019t use these tables\nyet, because Excel doesn\u2019t know what are\nthe relations between them.<\/p>\n\n\n\n
Al columns are related to each other with the Order_id<\/strong> column, which is the same in all three tables.<\/p>\n\n\n\nClick the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Click the PivotTable and navigate to PivotTable Tools >> Calculations >> Relationships<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Click it to open the Manage Relationships<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nInside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Inside this window, we\nare going to create two relationships. Click the New<\/strong> button to create the first relationship.<\/p>\n\n\n\nThe first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
The first one is going to be Payment<\/strong> to Order<\/strong>. Remember that these relationships are identified by the Order_id<\/strong>. Create the relationship as you can see in the image below and click OK<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nThe second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
The second relationship is between Payment<\/strong> and Customer<\/strong>.<\/p>\n\n\n\n<\/figure>\n\n\n\nWe don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
We don\u2019t have to create a relationship between Order<\/strong> and Customer<\/strong> because there is an implicit relation through the Payment<\/strong> table.<\/p>\n\n\n\nAll the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
All the relations that we create are inside the Manage Relations<\/strong> window.<\/p>\n\n\n\n<\/figure>\n\n\n\nClick the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Click the Close<\/strong> button.<\/p>\n\n\n\nYou won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
You won\u2019t notice any difference inside the PivotTables Fields<\/strong>, but all the work\nwas behind the scenes. Without relations,\nthe pivot wouldn\u2019t behave the way we would like.<\/p>\n\n\n\nPivotTable Fields<\/h2>\n\n\n\nLet\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Let\u2019s take a look at the fields of our table. We don\u2019t need Order_id<\/strong> anymore. It was necessary for relations between the tables and now it\u2019s not important anymore.<\/p>\n\n\n\n<\/figure>\n\n\n\nWhat we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\nDrag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
What we are going to do now, is to display the sales split between customers grouped by states and product types in different product categories.<\/p>\n\n\n\n
Drag table fields into Drag fields as shown in the image below.<\/p>\n\n\n\n<\/figure>\n\n\n\nYour PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Your PivotTable should look like this.<\/p>\n\n\n\n<\/figure>\n\n\n\nExcel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Excel 2013 and later versions have this powerful feature that was reserved only for PowerPivot users in the previous versions, where you can pick different fields from different worksheets<\/a> into the same PivotTable.<\/p>\n\n\n\nJust remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nCreate Pivot Table from Multiple Worksheets<\/title>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\t\n\t\n\t\n
Just remember that there always has to be some relation between these tables, otherwise, Excel is going to return strange results.<\/p>\n","protected":false},"excerpt":{"rendered":"
If you are using Excel 365 you can create PivotTable from data that are coming from several different worksheets. All the tools you…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\n