How to Combine Multiple Google Sheets Together (Tutorial)


– [Scott] Today’s video is
brought to you by Tmetric. TMetric is the simple time tracking app to help you manage every
minute of your day. Are you a freelancer
and need to keep track of billable hours? Well, TMetric makes invoicing
those hours to your clients so much easier and best of all, TMetric integrates directly with some of your favorite
tools including Google Docs, Gmail, Asana, Trello, and so much more. To try TMetric for yourself, click the link in the description below. Have you ever been working
within Google Sheets and having multiple sheets of data and wishing that you could
sum them all up together in a separate sheet? Well, in today’s video, I’m gonna show you how to do just that. Hello everyone, Scott
Friesen here at Simpletivity helping you to get more
done and enjoy less stress. And you know, sometimes
there’s good reason why we have multiple sheets for different pieces of information. Maybe this represents different managers or members of your team. Maybe this represents
different sales areas or sales regions, for example. So you may want to keep them separate because people are entering
information just for their area, but you’d like to total them
all up in a separate sheet. Now it’s true, I could take
the information for example, from B and C, I could cut or copy it and bring it over here to A, but I wanna show you a much
better way of doing this, especially if you are gonna be adding more information in the future. So here I’ve got a tab,
a sheet called Total. And the first thing that we need to do is we wanna make sure
that we have our headings that are exactly the
same as the other sheets that we are gonna combine. So I’m just gonna copy
and paste that over here. Now the next step that
we are gonna add here is we’re gonna add a formula. So in the very first
cell, just below date, I’m gonna hit that equal sign. And what we’re gonna do
is we’re gonna add a brace or sometimes better referred
to as a squiggly bracket, not just a regular bracket,
a squiggly bracket. And we’re gonna come over here to Team A, and we’re just gonna select
all of the data that we want. Now we’re gonna hit our semi
colon button on our keyboard, we’re gonna go to Team B,
and do the exact same thing, we’re gonna select sorry, all the information that
we want from this sheet, we’re gonna hit semi colon
once more, go to Team C and once again, we’re gonna select all of that information as well. We’re gonna close things off by hitting that squiggly
bracket once again, at the end of our formula there, hit Enter and now here on our total sheet, we have everything from A, B and C. If we look down here,
says we’ve got 30 rows, now let’s subtract one, right? Because we’ve got our header up here. So we really have 29 rows. So if we go back here to Team
A, remember to subtract one, we’ve got 12 plus six, that’s 18 plus 11. Yeah, that adds up to 29. So we’ve got all of that information here on this particular tab. So that’s great, it looks
like we’re finished right? Well, maybe there’s one problem. Maybe this is all you need for right now. But what if you are planning
to add more information to you know, one of these sheets over here or other members of your team? Let’s say I’m here on Team B, and I’m gonna add a new entry here. Okay? I’m gonna add, let’s add myself, just so it stands out from
the rest of the list here. Let’s give ourselves a state,
let’s say I’m selling binders and let’s say I am an absolutely
horrible sales executive. All right? So I’ve added my new data
here, perfect Team B. If I go over to my total area, if I scan down and I’m not seeing any zeros here, I don’t see my name here, that’s a bit of a problem, right? Because I want to be able to
add information over there and have it show here
but it’s not happening. I’ve made that new entry,
and it’s not showing up here. So let’s see how we can
tweak our formula up here so we can do just that. So a couple of things at first, we’re gonna come in here and we’re gonna look at the
cells that we’re referencing. One of the problems is that we
are saying to finish at E13. We wanna finish at that row but of course, as we’re adding things, we’re gonna be going well beyond that. So the first thing that we
wanna do is come in here and actually remove the row number. We wanna leave the column
of course, column E, but we wanna come in here
and remove the row number. So E7 becomes E and over here E12 just becomes E as well. But watch what happens when I hit Enter and change this formula. So, what happened here? So this is team A, right? That looks like Team A has come over and you may be saying it
didn’t even bring over Team B and Team C. Well, actually, you’d be wrong. But here’s the problem, I have to scroll all the way down to I
think about the thousands, somewhere around there
and yeah, look at that. There’s Team B. And if we scroll down 1000 more, we would get to around Team C. It is grabbing all of
those empty or no cells. Well, that’s a problem, right? That’s certainly not what we want. So let’s go back in here and
alter our formula once again. We’re gonna leave those
Es that the way they are, but what we wanna do is add a query. So at the very beginning of our formula, we are gonna type in the word query and an open bracket, all right? So an open bracket here is gonna allow us to have this query. We’re gonna keep everything
that we left in there, everything that we left
from that original formula minus the row number and we’re
gonna come to the end here and he hit comma. Now we’re gonna hit our quotation marks and we are going to
enter in the following, we’re gonna say select, this is how we’re gonna make sure that we’re only getting
information that contains values. We’re gonna say select asterisk, that’s sort of our wildcard, right? The star. Select where column one, and
that’s capital C-O-L one, that’s looking at column one or column A, in all of those different
sheets that we’re referencing, select star where column one is, oops, Did I put in another space? Is not null. Okay? So whenever it’s gonna
go back and take a look it’s only gonna bring in information where there are things
where there is information in that date column. And lastly, we wanna
hit our quotation marks to close that off, and one more we’re gonna do our bracket to
close off the full equation, let’s hit Enter and hope that this works. Okay, looks like we’ve got
a lot more information here. Do we have all the same
information we had before? We have all the same information and then some, hey, there’s me, there’s my poor sales job of
selling absolutely nothing. So there’s that new entry we made, now it went and grabbed that
information from Team B. Let’s do one more quick check test just to make sure that this works. I’m gonna put in a new
entry here on Team C, this time, let’s say, let’s call this one Jenny Sample also from Washington, let’s say, and she’s selling art and she
is an amazing salesperson. sold $45,000 worth of art the other day. So if I go back to total now, hey, look what’s showing up at the very bottom. So now with that new
formula that we put in on this total sheet, we can continue to add new lines, new information to these sheets and they will total up perfectly here on this final sheet. Well, I hope that you
found today’s video helpful and I would love to hear from you next. What other tips would you like to learn about Google Sheets here on
the Simpletivity Channel? Be sure to let me know in
the comments down below. Thank you so much for
watching today’s video and remember, being productive does not need to be difficult. In fact, it’s very simple.

Leave a Reply

Your email address will not be published. Required fields are marked *