Combine 4 Sheets

November 20, 2017 - by Bill Jelen

Excel combine several worksheets into a single worksheet. Each worksheet might have a different number of records from day to day, so formulas are not the way to go. Instead, a little-known tool called Power Query will let you merge the data simply and quickly.

Watch Video

Doug: How to combine four sheets where each has a different number of rows?
Use Power Query
Format each worksheet as a Table with Ctrl + T
Rename the tables
For each table, new query From Table. Add a custom column for Region
Instead of Close & Load, choose Close & Load to... Only Create a Connection
Use New Query, Combine Query, Append. 3 or More Tables. Choose the Tables and Add
Close and Load and the data appears on a new worksheet
For the one table with extra column: the data shows up for only that sheet's records
For the one table where the columns were in the wrong order: Power Query worked correctly!
Easy to Refresh later

Auto-Generated Transcript

Learn Excel from MrExcel Podcast Episode
2178 merge for worksheets today's
question via YouTube from Doug has this
situation we has four sheets where each
sheet is a region with sales data and
the number of records change monthly and
right now Doug is trying to use formulas
but when the number of rows changed that
becomes a nightmare right so I said hey
Doug can we use power query if you have
Excel 2010 or Excel 2013 it's a free
download from Microsoft or it's built
into 2016 and office 365 it says yes all
right so here's what we have we have
four reports the central region the east
region the south region and the west
region and each one has a different
number of records like here in the South
Region we have 72 records in the east
region 193 records and this is going to
change right
every time we run this report will have
a different number of records now I've
made some assumptions here first that
there's not a column called central and
then also I'm gonna be completely evil
here and take the South Region I want to
try and screw it up I'm gonna take the
profit column cut it and paste it how to
reverse those and then all right so we
have one where the columns are reversed
and then another one where we're gonna
add an extra column gross profit percent
so this is gonna be profit divided by
revenue in an ideal world these are all
shaped exactly the same but as I learned
recently I was doing a seminar down in
North Carolina if they're not all right
someone had a situation well you know
halfway through the year things changed
and they added a new column or move
columns around we were really happy to
see that power query was able to deal
with this all right so we're gonna take
each of these reports and make it into
an official table format as table so
that's control tea or you could use a
name range for me control t is the
easier way to go and what they do here
is they call this table one I'm gonna
rename this to be called central and
then we go on to East control T click OK
and this is gonna be called East now hey
on an earlier podcast I showed how if
these have been four separate files we
could have used power query just to
combine files but that doesn't work when
they're four separate or four worksheets
in the same same book so well there we
go and then
like this control-t a little tedious to
set this up the first time but boy
there's gonna be awesome
every time you have to update this later
on so we're going to do is we're going
to choose this first table central
region and if you're in 2010 or 2013 and
downloaded power query you're gonna it's
gonna have its own tab but in 16 in
Excel 2016 is actually getting
transformed which is the second group in
office 365 is now getting transform
which is the first group and so we're
gonna say they were going to create this
data from a table or range all right and
there is our data now we don't have a
region field and the combined files
would have added the region field so in
this case I'm just gonna add a column a
new custom call the headings gonna be
region and this one is going to be what
was this central right like that
click OK alright now here's the
important part when we're done this with
this we're gonna go home not choose
close and load we're gonna open the
drop-down close and load to only create
a connection click OK
perfect we have our connection only now
the next thing we have to do is repeat
these steps for the next three regions
and now that would be really a bit
boring to you so let's just speed up the
video to 10x for this
alright there we are for connections set
up now here's where we're going to do
the magic I'm gonna insert a new blank
worksheet and I'm gonna say get data
combine queries and I want to append two
queries from this workbook and I'm gonna
say three or more tables and the
available tables are Central through
West click Add BAM click OK and then we
can close and load and what we have here
is we have a superset of all of the
records in all of the tables all right
and where we tried to screw it up where
I purposely tried to screw up by
reversing cost of goods sold and profit
down in what was that that was Central
East South in the South Region I'll just
go check those right and it looks like
yeah generally feels right they used the
heading to figure it out because the
profit is always higher than cost of
goods sold and so that worked and then
down here in the West where we added
gross profit percent we actually get
that data for the tables that had it and
for the tables that didn't have it we
just get null which is perfect alright
now duck
here's what you're gonna do so the next
time that you have some more data and
I'll just let's create some some extra
records here we'll just add some ABC
with a date of today and all retail and
it's called Doug's new records and just
some garbage out here let's just put in
a hundred all the way across in the
interest of time okay so now because
this is a table the table automatically
expands to the new records which is
beautiful had they been named range I
would have had a redefine that's why I
really like the table instead of the
name range but we come back here to the
resulting workbook with 563 rows loaded
and I click refresh
and bam now I have 572 Rose loaded
including let's see if we can find them
in here
Doug's new records right there at the
end of the South Region
isn't that just an awesome awesome way
to go yes it definitely takes longer to
set up the first day we're up to seven
minutes already if I hadn't sped that up
to 10x but once it's set up now life is
gonna be super super easy from here on
out way this is where I usually promote
my own book but no this time let's talk
about this awesome book Emma's for data
monkey by Ken polls in Miguel Escobar
everything I learned about power query I
learned from this book look at the eye
on the top right hand corner for more
information about that book all right
wrap up topics in this episode Doug how
to combine four sheets where each sheet
has a different number of rows we can
use power query make sure to format each
worksheet as a table with ctrl T or use
named ranges but I prefer ctrl T rename
the tables from each table choose new
query from table add a custom column for
a region and then instead of close and
load choose close and load to only
create a connection do that for all four
queries and then new query combined
query append choose three or more tables
choose the tables and click Add
now some older versions of power query
you couldn't do three or more tables you
have to do two and then do another query
to add the third one and then do another
query to add the fourth one either way
it would be more hassle that way I'm
glad that they added the three or more
tables close and load this time close
and load to the worksheet and and then
later on if you add more data to any of
the four tables just go back to your
query and click refresh and you're good
to go
power query and amazing new feature from
Microsoft I love it I thank Doug for
saying that question it well thank you
for stopping by we'll see you next time
for another net cast from MrExcel

Download File

Download the sample file here: Podcast2178.xlsm

Title Photo: GLady / Pixabay