Hi everyone, Kevin here today, we’re going to look at how you can make a pivot table in Excel using data that’s on multiple worksheets and even multiple workbooks. Here for example, I have order data for the Kevin Cookie Company, but it’s broken out by year on separate worksheets and some of the data is even on a completely separate workbook. I want to analyze all of this data together. We’re going to look at how you can do that. If you’d like to follow along, I’ve included a sample workbook down below in the description. Let’s check this out. Here I am in Excel and I want to analyze all of this order data for the Kevin Cookie Company using something called pivot tables. If you’ve never heard of pivot tables before, it makes analysis really easy and you don’t even have to use any formulas or functions. To get a really solid understanding of how pivot tables work, be sure to check out the video in the top right-hand corner or down below in the description. With all of this data on this sheet, to analyze all of it, I can copy the data from 2020, I could copy the data from 2021, 2022, and I can even jump into another workbook, copy that data, and then paste it all into one aggregated workbook, but that’s a lot of work and maybe some of the data changes. So instead, we can use something called power query to really simplify all of this. The one thing that you have to ensure is that you have all of the same headers across all of your different tables on your different worksheets. To use power query, up on the top tabs, let’s click on data, and all the way over on the left-hand side, there’s a category called get and transform data. Let’s click on this dropdown that says get data and right towards the bottom, there’s an option that says launch power query editor. So, what do you use power query for? Well, power query makes it really easy to get data into Microsoft Excel and then also to transform your data. And what does transform mean? Well, with transform, you can clean up your data and also format it in an automated way. If you want to learn all about power query, I’ve included a video in the top right-hand corner and also in the description that goes in depth on how to use power query. In this video, we’re simply looking at how you can bring multiple worksheets into a pivot table. Let’s click on this option right here. This opens up the power query editor and currently it’s empty in here. We haven’t actually brought any data in yet. To change that, up on the home tab over on the right-hand side, there’s an option for new source. Let’s click on this and then go down to file. Now, one thing to call out is you can bring in data from all sorts of different locations. You could pull it in from a database, from Azure, from different online services. There are many different options here, but I just want to pull in data from different worksheets. So here I’ll go to file and then select Excel workbook. This opens up the Windows file picker, and right here, I can see my workbook titled pivot table multiple worksheets. This is the workbook that I currently have open. And if you’re following along and you downloaded the sample workbook, you should see this as well. Let’s select this workbook and then click on import in the bottom right-hand corner. This opens up the power query navigator, and here I can see all of the different worksheets that are contained within my workbook. Here I have my 2020 tab and I can see all of the data that’s within this worksheet. Here’s 2021 and here’s 2022. I would like to bring all this data together so I can analyze it as one. Right up here, I can select multiple items. Let’s check this box, and I want to bring in the 2020 data, 2021, and also 2022. With all of those checked, let’s go down and click on okay. And right here, I can see that it pulled in all of my data from those three years, but I do notice that it’s missing 2023, and it turns out that that data is in a completely separate workbook. Let’s go back up to new source, then click on file, and then once again, let’s select Excel workbook. Here, I’ll select the 2023 workbook and then click on import. And there’s the 2023 data. I’ll select this. And just like we did earlier, let’s click on okay. I now have all of my data in the power query editor, some of it from different worksheets and some from an entirely different workbook. And currently I have four different queries that show me all the data by year, but I want to combine or aggregate all of this data together. Up on the home tab on the ribbon, let’s go over to append queries, then click on this dropdown and there’s the option to append the queries as new. Let’s select that. This opens up a dialogue where I can now append all of these queries together. There’s an option where I can just append two of them, but over here I have four. So, I’ll select three or more tables. I see that it’s already pulled over 2023. So, I’ll select 2020, 2021 and 2022, and then pull these over. I want 2023 to appear at the end. So, I’ll move it down the list. Here I have all of my tables. I’ll click on okay. We are making some good progress. Here, I have a query that lists out all of the data across all of these four years. Right now it’s called append1, and that’s not a great name. Over here on the right-hand side, we have all of the different query settings and here I can give it a better name. Why don’t we call this all years, since it is combining all of the years. One of the great things about Power Query is that you can transform your data, for example, let’s say you want to filter out certain items, or maybe you want to split a column, maybe you want to configure the data type for different columns, for example, for the revenue and cost, I’ll set this to currency. You can go through, make all of these different changes, and then when you import data in the future, these changes will apply to that new data. It’s extremely powerful. And again, if you want to learn about all of the Power Query capabilities, be sure to check out the video in the description. For now, this data is fairly clean and I don’t need to make many changes. In the top left-hand corner, let’s go up to close and load, and then select close and load to. This now opens up a dialogue asking me how I would like to import the data, and I have a few different options here. I want to view it as a pivot table. So, I’ll select pivot table report, and I want to place it on a new worksheet. Down here, let’s click on okay. This has now created a new pivot table, but the sheet is currently called 2022 and that’s not very descriptive. Let’s double click on that and I’ll type in all years pivot, just to really make it clear what this is. Then I’ll hit enter. Let’s now click into the pivot table and over on the right-hand side, let’s make sure that you’re in the pivot table fields view. When you click on that, here you see all the different pivot table fields. Let’s click into the all view, and here we see different data models for each year. Here we have 2020, 2021, 2022, 2023, and down at the very bottom, we also have this consolidated view with all of the years. Here, I can expand that, and here I have all of my different fields or all of the different column headers. Let’s say I want to see the revenue across all of the years. Here, I can take revenue and I’ll drag that down into values and right up here, we can see that the revenue across those four years was a little bit above $8 million. Like I’ve always said, the cookie business is a good one to be in. Let’s now say that I want to see the revenue by year. Over on the right-hand side, there’s also a field for the order date. I can click on that and drag it down to the rows and here I can now see the revenue by year, so again, this is looking at each individual sheet and it’s pulling it all together into one consolidated view. If any one of these worksheets or even the workbooks changes, all of that data will be reflected here in this pivot table. That’s extremely powerful. Back over on the right-hand side, here I’ll scroll down and let’s say that maybe I want to know who the customer was that helped drive some of this revenue. I can take the customer ID and I’ll drag it down to the top of rows. And here I see the customer ID, the year, and the associated revenue. One challenge is here I just see the customer ID, but I would much rather have the customer name. That’s a lot easier to really understand who it was. If I go back to any one of these worksheets and I look at all of the data, here you’ll see we don’t have the customer name included in any of these tables, but there is a worksheet titled customers and within customers, there’s a customer ID and there’s also a customer name. If I go back to one of these worksheets with all of the order data, and here you’ll see the customer ID is here. So, if only I could connect this customer ID in the order data with the customer table where I have the customer ID and the customer name. To do that, first let’s turn this customer data into a table. To turn it into a table, go to the top tabs and click on insert, and here’s the option to insert a table. When I hover over this, you’ll also see that the shortcut key is control T. Let’s press control T. This opens up a dialogue that allows me to turn this data into a table. It correctly identifies all of the data and my table does have headers. Here, let’s click on okay. This has now turned it into a table and currently the name is set to table1. That’s not descriptive. So, let’s give it a better name here. I’ll double click in and let’s call this customers and then hit enter. That’s a lot better. Next, let’s go back to the pivot table down below. Back within the pivot table view. Next, we need to connect the customer’s data to all of this order data based on that customer ID that appears in both the order data and in the customer table. To do that, let’s go up to the top to pivot table analyze, and right in the calculations group, there’s an option that says relationships. With this, we can establish a relationship between all of that order data and the customer table. Let’s click on this. This opens up a table and let’s click on new, and right here, I can now define the relationship. Here, I’ll select the all years view or this data model that we created earlier, and then it’s going to relate to this customers table. I will select that. Now that we’ve selected these two, we have to define how do we want to connect these two tables and the one column in common between these two is the customer ID. Here, I’ll select the customer ID, and then in the customers table, I’ll also select the customer ID. So that’s how we connect these two items. Next, let’s click on okay. Now that we’ve defined these relationships, let’s close out this window and within the pivot table fields, we now see the table customers. Let’s expand that, and here I see the customer name. I can drag this down as a row into my pivot table and I’ll remove the customer ID. Let’s also collapse some of these dates. Here, I’ll collapse it down to the year, and now I see the customer name with all the years and the associated revenue. So that’s how you can pull together multiple worksheets, multiple workbooks, and even related data into one pivot table, so you can analyze all of your data in just one place. It’s extremely powerful. All right, well, hopefully that saves you a lot of time. So maybe you could get around to visiting one of our Kevin Cookie Company locations. To watch more videos like this one, please consider subscribing and I’ll see you in the next video.