EASILY Combine Multiple Excel Sheets Into One With This Trick

0:00in Excel you often need to combine

0:02multiple Excel sheets into one for

0:04example you might have monthly sales

0:07tabs that you need to combine into one

0:09larger Master tab or you might have

0:12multiple Excel files in one folder that

0:15you need to consolidate into one large

0:18Excel file yet 90% of excel users don't

0:22know how to do this the right way that's

0:24why in this video I'm going to show you

0:26a simple automated solution to solve

0:29this problem

0:30and save you hours of tedious work first

0:34up let's suppose we have this Excel file

0:36which you can download for free right

0:38below the video and as you can see we

0:40have three different tabs for the

0:42monthly sales in January February and

0:44March and we want to consolidate these

0:47into a total over here one way to do

0:50this is simply by taking the data and

0:52just control shift down control shift

0:55right so we would select each month copy

0:58and then paste it in here same thing for

1:00all of the other tabs the problem with

1:03this method is that it's not Dynamic if

1:05something changes in the original data

1:07here it's not going to update in the

1:09totals and if we have new tabs they're

1:12also not going to be accounted for so

1:14it's not a very good method another way

1:16to do this is using the V stock formula

1:19so equals V stock hit the top key there

1:23and what this one allows you to do is

1:25stock multiple tables so let's go over

1:27to January here we're just going to hit

1:30the shift key all the way to March you

1:32can see we have the three tabs selected

1:34and if they have the same length we can

1:36just control shift down control shift

1:39right on that first one and you can see

1:41that we've selected from January to

1:43March for this range from 82 to

1:47F32 we'll close the parenthesis and hit

1:50enter there so here under totals we have

1:53the fully Consolidated sheet with 94

1:56rows which is basically the sum of these

1:59three the problem with this is that when

2:01we add new data to March for example at

2:03the very bottom it's not going to

2:05account for it so let me just put amount

2:07here just for it to test you'll notice

2:10that we don't actually find it doesn't

2:11grow in values here so the V stock

2:14doesn't quite work either same thing

2:16goes if we add new tabs they're not

2:18going to be accounted for as you can see

2:21even though the copy paste and the V Stu

2:24can sometimes work they're not ideal

2:26Solutions so let's go over power query

2:29which is what we would use to automate

2:31this entirely so to consolidate this

2:34Excel file with these tabs and any

2:37future tabs as well we're going to open

2:39up a new Excel file with

2:41crln once we have that we want to go

2:44over to data under get data here from

2:49file we're going to want from an Excel

2:52workbook which is going to be the

2:54original workbook that we're working

2:56with with all of our data in my case

2:59it's called monthly sales it might be

3:01called something else for you just going

3:03to hit on import there and power query

3:06should now pop up as you can see it over

3:08here we have the three different tabs

3:10for each month and we can just select on

3:13one and just click on transform data so

3:16here we're in the power query editor but

3:19we're only working with January so we

3:21actually under appli steps over here we

3:24want to go back to the source which is

3:26going to be the beginning so let's X

3:28those and one once we're at the source

3:30we can see that we have the three

3:32different tabs over here that's what we

3:34want to work with we don't really need

3:36any of these columns over here to the

3:39side so let's go ahead and select them

3:41right click and click on remove

3:45columns great now we just want to expand

3:48all of the data here by clicking on that

3:51and hitting on okay here you can see all

3:54of our data you'll notice though that we

3:57have for each of the new sheets there's

4:00going to be the whole header row again

4:02and same thing down on the bottom so

4:04let's first just add the header row to

4:06the top part and for this we can simply

4:09go over to F use first row as headers

4:13just click on that and we can rename

4:15this one over here to the month hit

4:18enter there and you'll still notice that

4:21we have these rows that we don't quite

4:22want so we can just filter those out by

4:25going to this drop down over here and

4:28whenever it says brand we don't want

4:30that and hit on okay there you can see

4:33all of our steps are being applied to

4:35the side and we no longer have the

4:38header row anywhere in our data set now

4:41we can just click on close and load

4:44awesome now if we click on this drop

4:47down you can see that it's merged

4:48January February and March into one

4:51single table from here let's suppose

4:54that the April data comes in let's see

4:57if it's able to account for that so

4:59let's go over here to our original Excel

5:02file let me just go ahead and add a new

5:05tab just by duplicating this and calling

5:07this April just to see if it's able to

5:10account for it we're going to hit contrl

5:12s to save it and then we'll go back to

5:16our new data set over here under the

5:19queries and connections right click and

5:22just hit on refresh which you should

5:24find over here we had 91 rows and now we

5:28have 122 to that's because if we scroll

5:31down we have all of the April data now

5:34as well great now we've learned how to

5:37consolidate multiple Excel sheets and

5:40the next step is to consolidate entire

5:42Excel files into one large one before we

5:46do that though now that we've learned

5:48how to manipulate data the next step for

5:50us would be to visualize it and that's

5:53where powerbi comes in which you can

5:55learn it by taking our powerbi for

5:58business analytics course powerbi is one

6:02of the most popular business

6:03intelligence tools and in our

6:06all-inclusive curriculum we start with

6:08data cleaning and transformation using

6:11power query then we get into Data

6:14visualization tools followed by ducks or

6:18data analysis Expressions which is what

6:20you would use to build formulas in power

6:23bi then to simulate real work scenarios

6:27we'll practice using two extend Ive case

6:30studies one will focus on building a p&l

6:33dashboard from scratch on Nike while the

6:36other will focus on visualizing

6:39McDonald's European restaurant

6:42operations currently 97% of Fortune 500

6:46companies use powerbi so if you're

6:49looking to invest in yourself check out

6:51the link in the description below all

6:54right back to the video at this point

6:56you might think this is all great but

6:58you actually receive receive all of your

7:00Excel data in different files like this

7:03over here where you have one for each

7:05month and you just want to merg it into

7:08one Consolidated Master file in this

7:11type of scenario the solution is

7:12actually fairly similar so we would just

7:15go over to a new Excel sheet under data

7:18again get data and under from file

7:22before we did from an Excel workbook but

7:25we now want from a folder go ahead and

7:28find the folder where it's located in my

7:31case it's over here just going to click

7:33on open there what it's going to do is

7:35it's going to find the three different

7:37Excel sheets that there are in it and we

7:40can simply combine them in this case I'm

7:42just going to hit on combine and load as

7:44I don't want to make any changes to them

7:47I'm going to select the data and I'm

7:49just going to hit on okay give it a few

7:52seconds and you'll be able to see all of

7:55the data merged over here you can see

7:57under Source name we have have all three

8:00Excel files included now if we were to

8:03add a fourth Excel file to this folder

8:06just going of duplicate it over here let

8:08me call this something like April as

8:10long as all of these headers are the

8:12same now if I just click on refresh up

8:15over here I should get the April Excel

8:18file as well so you can see that we now

8:20have the April 24 Excel sheet included

8:23in here as well awesome this new trick

8:26is hopefully going to save you hours of

8:28time when working with data now that

8:31you've learned how to manipulate it the

8:33next step is to visualize it which you

8:36can learn how to do with this video over

8:38here to make an interactive dashboard or

8:41by taking our Excel course over here hit

8:44the like and that subscribe and I'll

8:45catch you in the next one