in Excel you often need to combine multiple Excel sheets into one for example you might have monthly sales tabs that you need to combine into one larger Master tab or you might have multiple Excel files in one folder that you need to consolidate into one large Excel file yet 90% of excel users don't know how to do this the right way that's why in this video I'm going to show you a simple automated solution to solve this problem and save you hours of tedious work first up let's suppose we have this Excel file which you
can download for free right below the video and as you can see we have three different tabs for the monthly sales in January February and March and we want to consolidate these into a total over here one way to do this is simply by taking the data and just control shift down control shift right so we would select each month copy and then paste it in here same thing for all of the other tabs the problem with this method is that it's not Dynamic if something changes in the original data here it's not going to
update in the totals and if we have new tabs they're also not going to be accounted for so it's not a very good method another way to do this is using the V stock formula so equals V stock hit the top key there and what this one allows you to do is stock multiple tables so let's go over to January here we're just going to hit the shift key all the way to March you can see we have the three tabs selected and if they have the same length we can just control shift down control
shift right on that first one and you can see that we've selected from January to March for this range from 82 to F32 we'll close the parenthesis and hit enter there so here under totals we have the fully Consolidated sheet with 94 rows which is basically the sum of these three the problem with this is that when we add new data to March for example at the very bottom it's not going to account for it so let me just put amount here just for it to test you'll notice that we don't actually find it doesn't
grow in values here so the V stock doesn't quite work either same thing goes if we add new tabs they're not going to be accounted for as you can see even though the copy paste and the V Stu can sometimes work they're not ideal Solutions so let's go over power query which is what we would use to automate this entirely so to consolidate this Excel file with these tabs and any future tabs as well we're going to open up a new Excel file with crln once we have that we want to go over to data
under get data here from file we're going to want from an Excel workbook which is going to be the original workbook that we're working with with all of our data in my case it's called monthly sales it might be called something else for you just going to hit on import there and power query should now pop up as you can see it over here we have the three different tabs for each month and we can just select on one and just click on transform data so here we're in the power query editor but we're only
working with January so we actually under appli steps over here we want to go back to the source which is going to be the beginning so let's X those and one once we're at the source we can see that we have the three different tabs over here that's what we want to work with we don't really need any of these columns over here to the side so let's go ahead and select them right click and click on remove columns great now we just want to expand all of the data here by clicking on that and
hitting on okay here you can see all of our data you'll notice though that we have for each of the new sheets there's going to be the whole header row again and same thing down on the bottom so let's first just add the header row to the top part and for this we can simply go over to F use first row as headers just click on that and we can rename this one over here to the month hit enter there and you'll still notice that we have these rows that we don't quite want so we
can just filter those out by going to this drop down over here and whenever it says brand we don't want that and hit on okay there you can see all of our steps are being applied to the side and we no longer have the header row anywhere in our data set now we can just click on close and load awesome now if we click on this drop down you can see that it's merged January February and March into one single table from here let's suppose that the April data comes in let's see if it's able
to account for that so let's go over here to our original Excel file let me just go ahead and add a new tab just by duplicating this and calling this April just to see if it's able to account for it we're going to hit contrl s to save it and then we'll go back to our new data set over here under the queries and connections right click and just hit on refresh which you should find over here we had 91 rows and now we have 122 to that's because if we scroll down we have all
of the April data now as well great now we've learned how to consolidate multiple Excel sheets and the next step is to consolidate entire Excel files into one large one before we do that though now that we've learned how to manipulate data the next step for us would be to visualize it and that's where powerbi comes in which you can learn it by taking our powerbi for business analytics course powerbi is one of the most popular business intelligence tools and in our all-inclusive curriculum we start with data cleaning and transformation using power query then we
get into Data visualization tools followed by ducks or data analysis Expressions which is what you would use to build formulas in power bi then to simulate real work scenarios we'll practice using two extend Ive case studies one will focus on building a p&l dashboard from scratch on Nike while the other will focus on visualizing McDonald's European restaurant operations currently 97% of Fortune 500 companies use powerbi so if you're looking to invest in yourself check out the link in the description below all right back to the video at this point you might think this is all
great but you actually receive receive all of your Excel data in different files like this over here where you have one for each month and you just want to merg it into one Consolidated Master file in this type of scenario the solution is actually fairly similar so we would just go over to a new Excel sheet under data again get data and under from file before we did from an Excel workbook but we now want from a folder go ahead and find the folder where it's located in my case it's over here just going to
click on open there what it's going to do is it's going to find the three different Excel sheets that there are in it and we can simply combine them in this case I'm just going to hit on combine and load as I don't want to make any changes to them I'm going to select the data and I'm just going to hit on okay give it a few seconds and you'll be able to see all of the data merged over here you can see under Source name we have have all three Excel files included now if
we were to add a fourth Excel file to this folder just going of duplicate it over here let me call this something like April as long as all of these headers are the same now if I just click on refresh up over here I should get the April Excel file as well so you can see that we now have the April 24 Excel sheet included in here as well awesome this new trick is hopefully going to save you hours of time when working with data now that you've learned how to manipulate it the next step
is to visualize it which you can learn how to do with this video over here to make an interactive dashboard or by taking our Excel course over here hit the like and that subscribe and I'll catch you in the next one
