How to Consolidate Data from Multiple Excel Tabs into One Sheet: Step-by-Step Guide

Microsoft Excel is a powerful tool for organizing, analyzing, and storing data. One common task that many users face is consolidating data from multiple tabs (worksheets) into a single comprehensive sheet. This can be particularly useful when dealing with monthly reports, regional sales data, or any scenario where data is segmented across different sheets. In this blog, we’ll explore how to master the art of data consolidation in Excel.

How to Consolidate Data from Multiple Excel Tabs into One Sheet

Step 1: Prepare Your Data


Before consolidating, ensure that the data across all tabs is structured consistently. This means that the columns should have the same headings and data types.

For example, if you have sales data across multiple tabs, each tab should have columns like “Date,” “Product,” “Quantity,” and “Sales” in the same order.

 

Step 2: Identify the Range of Data to Consolidate

 

Determine the range of cells that you want to consolidate from each tab. It’s important that the range is consistent across all tabs for accurate consolidation.

 

Step 3: Use the Consolidate Feature


Excel’s Consolidate feature can combine your data into one sheet. Here’s how to use it:

1. Create a new sheet where you want the consolidated data to appear.
2. Go to the “Data” tab on the Excel ribbon.
3. Click on “Consolidate” in the Data Tools group.
4. Choose the function you want to use for consolidation (e.g., Sum, Average, etc.).
5. Click on “Add” to select the range from the first tab.
6. Repeat step 5 for each tab you want to consolidate.
7. Check the “Top row” and “Left column” boxes if your data has labels.
8. Click “OK” to consolidate.

 

Step 4: Use Formulas to Consolidate Data

If you need more control over the consolidation process, you can use formulas:

1. In the new sheet, type a formula that references the corresponding cell in the first tab you want to consolidate. For example, `=Sheet1!B2`.
2. Copy this formula across the row or down the column.
3. Adjust the formula for the next row or column to reference the next tab (e.g., `=Sheet2!B2`).
4. Repeat this process for all tabs.

 

Step 5: Use Power Query for Advanced Consolidation

For more advanced scenarios, Power Query is a powerful tool:

1. Go to the “Data” tab and select “Get Data” > “From Other Sources” > “Blank Query.”
2. In the Power Query editor, use the “Append Queries” feature to combine data from different tabs.
3. Load the appended query to a new worksheet.

 

Examples of Consolidating Multiple Excel Sheets

 

Example 1: Consolidating Monthly Sales Data

Imagine you have sales data for each month on separate tabs named “Jan,” “Feb,” “Mar,” etc. Each tab has columns “Product,” “Quantity,” and “Sales.”

Using the Consolidate feature, you can sum up the “Quantity” and “Sales” for each product across all months into a new sheet.

 

Example 2: Creating a Summary Report

You have different tabs with expense data for various departments. Each tab has columns “Expense Type,” “Amount,” and “Date.”

By using formulas or Power Query, you can create a summary report on a new sheet that shows the total expenses for each department.

 

Conclusion


Consolidating data from multiple tabs into one comprehensive sheet in Excel can significantly improve your data analysis efficiency. Whether you choose to use the built-in Consolidate feature, Excel formulas, or Power Query, the key is to ensure that your data is well-structured and consistent across all tabs. With the steps and examples provided in this guide, you’re now equipped to master data consolidation in Excel and take your data management skills to the next level.

 

Live Excel Support Transcript Example:

Below is a live transcript of one of our excel experts helping a customer to consolidate Multiple Excel Sheets

Customer: Hello.
Technician: Hello, Tiffany.
Customer: Hi. How are you?
Technician: Good. How about you?
Customer: Good. So, I just have questions. I’m looking at a document, and I have some data, and I need to pull over from several tabs. Yeah. Compile all of it into one using some type of probably XLOOKUP, I just don’t know the exact formula. I just don’t know how to do it. So, I don’t know the easiest way, and I’ve never done this before, how it works, but should I share my screen with you, or?
Technician: You can share your screen, the one you see now, or you can join the session in the link I provided you.
Customer: Okay. Let me share my screen here.
Technician: Okay. I see it.
Customer: Okay. So, what I’m doing is, all of these tabs have individual income statements per location, and some of them have this account, all in account, cost of goods, so I want to segment off January through August for each of these locations, just these categories, so I kind of, I just highlighted them right here. This is the data that I need, and then, anyway, that’s all I need to pull, is that, this data from each of these sheets into one list.
Technician: Can you, I mean, I don’t know if this is what you want, so go to C11.
Customer: Go where?
Technician: C11, over there.
Customer: Uh-huh. C, one more. Oh, C, uh-huh. Type equals, and then go to where you want to pull the data, just highlight all of that.
Technician: Actually, you know what, that account, hold on, I don’t need this account, this is just the label. Just equals and highlight, highlight the whole thing, what you need. Enter.
Customer: Is that what you needed?
Technician: Yes, but I need it for, I need it set up so that I have each, I don’t know where to put this, each location name, all these locations, there’s about 21 of them down here, that data for each of them across January through August, for these three accounts. So do you need to paste, like, another three rows for each?
Customer: So I need to, how do you format this, or how do you pull it out by location? Like 55101, 102104, you need those three for each, right?
Technician: Yes, for each.
Customer: Yes, I mean, you can just copy and paste, and we can just adjust the reference to the table. Is that okay? How do you paste this and just do the same thing over and over for each location?
Technician: Can you go to C11?
Customer: Mm-hmm.
Technician: It depends if it’s the same, see the formula up there, it’s referencing the sheet, 101 and then B27 to B29. Is everything referenced the same? It’s probably easier just to do what you just did. Let me see.
Customer: Let’s see, that one doesn’t have anything here. It would look like, here’s the-
Technician: How about this? Let’s just go back.
Customer: Uh-huh.
Technician: Yeah. Just go down, go to, like, 17, row 17 or 16. Just type equals, just go back to where you were, where you want to pull.
Customer: The next one that I want to pull?
Technician: Yeah, yeah. Uh-huh. And highlight, including the titles, all the way down.
Customer: All the way down?
Technician: No, no, wherever you want to pull. Just right here, yeah. Yeah, press enter.
Customer: Is that okay, like this?
Technician: Well, that’s for…
Customer: Okay, so that’s for that individual location, right?
Technician: Yeah.
Customer: And this one now has a new account that looks like it goes here, so some of them have similar accounts, some don’t. If it all… Like, all in all, I need the cost of sales for each of these, each of these accounts, and there may be one or two, like, extra accounts in some of these sites just because they use another one, but, like, I’m trying to figure out a way to, like, this all in accounts payroll category needs to be up top so I can see for the month of January everything that we took in from this account.
Technician: Yeah.
Customer: And then everything that we took in across all locations in this account and everything we took in across all the locations in this account, so it’s basically kind of four or five different accounts. And I need it just all organized into one sheet, though.
Technician: Yeah. Yeah. I mean…
Customer: So I understand pulling the information over here, I’m just thinking, like, how do I lay it out in a way that I can get each of these locations in here?
Technician: Okay. So I see. So this is what? So this is basically these locations, right? I’m going to take these locations, I’m going to put those here, and then I need these accounts. Let’s see. These accounts, basically, now, instead, it’s going to be across the top. I’m just thinking, if I take this, equals, how do I do this, equals from, I’ll be… Transpose. Paste across the top there, so now I’ve got these accounts, I’ve got, oh, let me do one more. You can do the same thing, like, copy what you want and then transpose it.
Customer: Okay. So those are four unique accounts, right? And then I need the… This is data now.
Technician: You can delete B. But I need it from January to August. Each of these locations, how much they took in for each of these accounts, and then laid out January through August.
Customer: Yeah. So let’s just do something simple, like, just go to C11, equals C11, so I can understand what you’re trying to get.
Technician: It’s equals. So let’s go to PEN55101. Where is that at? Where is it? 101PEN?
Customer: Yeah, the sheet is right here.
Technician: Okay. So where’s the amount that you’re looking for in this table?
Customer: For the online accounts, for that category, that’s going to be right here, January through August.
Technician: Okay. Hit Escape. Let’s hit Escape. Go back to the information you need.
Customer: I need this online accounts payroll. In a row, not in a column.
Technician: Yeah. For 101PEN. So let’s go to 101PEN tab again. And copy that whole row.
Customer: Just right here?
Technician: Yeah. Just the first row. I don’t know if it’s going to work. Just the data. Just copy the whole data. For all three accounts or just for that one account?
Customer: That’s just one. Just one account? Copy. Looks like static information. And then go to sheet one. Yeah. And then go to, yeah, paste special. And can you paste it with transpose? I don’t see any options. But can you paste it across the row instead of the column? Let’s see here. Do undo, yeah. Okay. Paste special. I don’t see your options. Can you move it to the other screen? You have two screens? Oh, sorry. Or you can just move it over. Hold on. Let me just do entire screen. Okay. Can you do a transpose? Do you see an option?
Customer: Yeah, that one. This one?
Technician: Okay. No. Is there, like, a paste transpose? Secure fees. My paste transpose worked. So let’s go back and copy the static data.
Customer: Okay. It’s right here. It’s already copied. Copy again. And then let’s go back to your sheet. And then go to, yeah, C11. Right click, paste transpose.
Technician: Paste special. Right here?
Customer: Yeah, click okay. It should be on the right. I don’t know why it’s pasting that way. Well, hold on. Let’s copy this. Let’s copy this now.
Technician: Yeah.
Customer: Okay, there it goes. You can just cut and paste.
Technician: Okay. So you can just cut and paste this instead of, like, referencing things. So you have some extra data there. But the thing is, like, this is, so this is the accounts, but this is each month. So how do I show the months? There’s a difference between the actual account and the months. I wonder if I can use the accounts as a filter. Here. What if I did that? I’m not understanding. So what if I use this as an option to filter, to just select one account, and then it showed the data January through August for each of these? Because really what I need in here is I also need this data is for January through August. That’s what these numbers are coming from. But that’s for this individual account at this location. But I need this account, too, January through August for this location.
Technician: Yeah, and what you just pasted is that one accounts, the 55101, January to August.
Customer: Right, exactly. It only has this account. I have the data for these accounts here under this location. I think it might be a lot of copy and pastes if you want. Like, how about you cut B10 and move it over to after August, B10? I think the title should be 55101, all accounts payable, and then January.
Technician: Just lost you. Okay, now what?
Customer: So C, yeah, should put a January in the end. No, I think, yeah, that works, too. I think that’s it. I see January, and then have all the accounts January data here for this account, and then have February. No, no. Or here, you can do this. You can do 101 pen, January. No, that’s not going to work. Yeah, 101 pen, January to August. And when you paste it, you don’t have to transpose it. But I think you want it like this. So just rename B10 to 5501, all accounts payable, dash January, and then do the same. Yeah. And then keep on going down. Copy and paste until you get to August, and give them the month. And then after that, it’s just a matter of copy and paste. Keep on pasting. So just move from side to side until you get to, yeah. Oh, shoot. Yeah, but then, I mean.
Technician: Then it’s just a matter of copy and paste. It’s going to be a long row. It’s going to be a bunch of columns, January to August, January to August, January to August. But I want to show each account. I’m just thinking if I need to go like.
Customer: You can also do it the other way. Like 101 pen, January. 101 pen, February. Or actually, I think the way you did it is fine. I just don’t understand.
Technician: Yeah. But then how am I going to show the data for the rep? Okay. So like if 1800 M has. All in accounts, payroll. Here. I mean, it doesn’t actually. So let’s say, okay. 1800 1801 K has all in accounts, payroll. All right. Now I’m copying that data over. January, February, March, April. Right. It has that, but then. What way do I show the other accounts? Because the other accounts. You know, it’s got this payroll plus payroll taxes, plus this other expense. Yeah. Show as well. Keep on going down. So. Copy and paste B. If you can go back. Leave that there. Yeah. Copy. B. A 10. All the way down to August. Just highlight. All the way to August. Yeah. Copy that. And then paste it in gene. And Jay. Oh, you’re saying just like, make it go. The next account. The next account. And then what? And then like, in order to see the, to see it in a better view, just run a pivot table on it or something, like put all the data together. Now you can just do a lot of copies and pastes. And then you can sum up everything. That’s that’s what you’re looking for. I was hoping there was an easier way to pull in the data though. For each per each account. Huh? So I can pull in each of these locations, have these accounts. Or may not have these accounts, but they’re all labeled the same. Right. Right. And on accounts payroll here, that’s got two. Of them, this has got three of them and I need January through August. For each of these three accounts. Per location. Look at the locations. I need each, you know, there’s three or four different accounts. I need data from, and I need it over January to August. So I need to be able to pull it into this January to August. It just seems like January to August for four different accounts. That’s a lot of. Rose. Or a lot of columns going across. I don’t want that many columns. And let’s, I mean, you can also do it. Let’s see if this works. I mean, I can copy it. I can go like this and have each, you know, all the locations for each account and have, okay, here’s. You know, this account. And then here’s. You know, this account. That’s actually probably how I should do it. And then just use this for January. Yeah. I don’t think there’s an easier way, but. And then have it. Yeah, that’s probably the easier way. And then have each sub account here. Yeah. You probably shouldn’t skip any. No blank roles. Or you should maybe put them in. It’s its own tab. Put each of these accounts in its own sheets. Yeah. So that you can know. At the end of the day, I do want to add up. Each of these three accounts, because the way that it is. This account is the primary. Here’s the sub accounts. I need the total for all in cost of goods. Yeah. For each of these. So. Okay. I’ll play with it. I’ll play around with it that way. Okay. I’ll copy paste it over that way. I’ll just lay it out. I’ve got each locations. And then I’ve got the data. Is there a chance to data in the other sheet that you’re copying? Pasting is going to change. No, the data, when it comes to these specific accounts, the data looks the same. Okay. So the copy and paste is fine. If it does, if it would change and you would probably do an equals. And then highlight. And do a reference. You know, in the, in the beginning of the call, we did an equals. And then we highlighted. Yeah, because if I did, let’s say for, let’s see. 2100 pen has this on accounts. That one. So I would do. You’re saying for 2100 pen. I would go. Yeah. It’s probably better in case something changes. Right. Right. But then again, I mean, nothing. Yeah. Nothing’s going to change. Yeah. That’s that’s what data as well. Okay. But yeah, I mean, I can reference that. Yeah. Okay.