Let's talk about Excel 3D formulas. Did you ever need to
reference the same cell of the same range or multiple worksheets? So for example, you're
summing up the sales for different products, and each product has its own tab. One way of writing the formula is to reference each tab separately, but that can get annoying. There is a more convenient
way of writing the formula and that's to use a 3D-reference. You're going to end up with a
super short flexible formula. For those of you that know how to do this, stay with me, because I have
a bonus tip for you from Bob. That's Bob Umlas, so you
know it's going to be good. (upbeat music) Here in this example, I have sales information
for different products put on different tabs. So first one is the game products, then we have utility
and then productivity. Now notice that the structure
of the tabs is the same. I have months on one side, a title here, data and a total on the bottom. Now let's see, I want to
add up the sales information for all of these products for each month. Well, one way to do this is, just to start off with the equal sign. Go to each tab, click on the
cell we want put in a plus. Next tab, cell, plus. Next tab, cell, Enter. Now if you have more sheets you'll have to do this for all the cells. Now that's fine, but one, it can get really annoying if you have a lot of sheets,
it's also not dynamic. So if we ended up getting a
new sheet with a new product, so let's say product health, we'd have to make sure that
we update our formulas. There is a better way of doing this and that's called using 3D formulas. Because the great thing about 3D formulas is that they're dynamic. The moment you add a
new sheet in the middle of your start and end sheets, your values will update automatically. Here's how you write
these type of formulas. You start off with the first
sheet we want to include. So I'm going to go with product game, and let's just click on
to cell that we want. This is going to help us
give the correct structure for the sheet name, 'cause notice mine has
single quotation marks around the name, and that's because I have
a space in the sheet name. If you don't have a space there, you're not going to see
the single quotation marks. Now since we want to add these up, we're going to use the SUM function. The starting sheet is this sheet, so for 3D formulas you just
need to mention your cell once at the end. What you need is your starting
sheet and your ending sheet. And if you have spaces in
your sheet names like I do, you just need one quotation mark and one exclamation mark at the end. We're going to need to add the colon here, so just the same way you
would refer to a range you have that colon from and to, we need that for the sheet names. Now we need the last sheet, just to make sure I spell it correctly, I'm actually going to click on it and that's included in the formula. But here, one thing I
have to watch out for is that I don't need the
single quotation mark. I just need to put all of this in one starting single quotation mark and one ending single quotation mark. I also just need one exclamation mark. Yes it's a bit tricky to
get the formula right, but once you do that, it's just super easy to maintain. Now remember, if you don't have
the single quotation marks, it means that you don't have
spaces in your sheet names, which is fine. It actually makes it simpler
to write this formula. Now I'm just going to
close bracket, press Enter and I have the total value. Now I only need to do
is to send this down. I could do this for the total as well, but since I have the
totals in all the sheets, I can add them up in this way. But just to show you that
you don't necessarily need to just reference one cell, you can also reference
a range on each sheet. Let's just write the
sum from scratch here. What we need is the starting sheet, and I usually like to click on it to make sure I don't make
any mistakes in the spelling. I'm going to include the range that I want and they're right here. I'm going to add the ending sheet, remove that extra exclamation mark, remove that extra single quotation mark and close bracket, and I
get the same value back. Now here's the great thing
about using 3D formulas. Anything that you drop in between these is going to get included. Now that can be a good thing because anytime you have new products, all you have to do is
add it in the middle, so let's just try that. Let's go to the game one. Let's just copy it and put it right here. I'm just going to call it prod health. Now let's go to all products. My values are updated. Just to make sure we see it let's just increase this
by a really big amount. Let's go to all products. We see it pull through here, so I'm just going to press
Control + Z to go back. Then the only downside to this is if you happen to have other sheets that don't belong in here, and for some reason you decide
to drop it in the middle, they're going to get included. So in this case, I have
other information here. This is the bonuses that I'm
paying out for each month. Let's take a look at my
all product number here total is 203,000, the moment I drag and drop the other info somewhere in the middle
and go to all products, my total here changes. This one didn't change, why? Because it looks like I have
no total on this sheet, right? But there are some that I
actually calculated from scratch, that one got updated. This is something you have to take care of if you use 3D formulas, that you make sure you
don't drop other things that don't belong in there
in the middle of your tabs. But let's get to Bob's trick now. He sent me this trick. He said, "Guess what? "You can use to 3D formulas on
sheets that aren't together!" As long as they have a common name. So in this case, all of
my sheets start with prod. I can make the 3D formula that
adds up all the prod sheets. Here's how you can do this. So let me remove these because
they're not correct anymore. I have this other info
sitting right there. Let's take a look at Bob's trick. What you need to do is use wildcards in the name of the sheet. We can use the asterisk sign. So in my case prod is always
at the beginning of the sheet. I don't need to start off
with the asterisk sign, but if prod was going to
be somewhere in the middle of the sheet name, and I
want to be sure I include all sheets that have the word prod. I can start off with the asterisk sign, but notice I put in the
single quotation mark first. And then I type in the name
that's common to all the sheets I want included in my result. So my case is prod. Then I put the asterisk sign as well, single quotation mark
and the exclamation mark. And then the cell that I want to add, So I just want B3 here, close bracket. Now watch what happens when a press Enter, Excel goes and automatically
brings the sheet names that have the word prod,
but notice what it did here. It put game to health, and then it skipped other info and put utility to productivity. So it completely skipped any sheets that don't have the word prod in there. All I need to do is send this down and I have my sheets included. Now again, I still have
to be careful though when I add new sheets, because if I add anything
in between game and health, it will get included. But using this trick
makes it a lot simpler to write such a formula. You can use wildcards in sheet names. Now, thanks to Bob Umlas
for sending me this trick. So that's how you can create a 3D-reference formula in Excel. So you can easily consolidate
your data from different tabs. Use it to add up costs from
different cost centers, or consolidate your budget data. I hope you enjoyed this video. Do give it a thumbs up if you did and consider subscribing if you'd like to improve
your Excel skills, and I'm going to see
you in the next video. (upbeat music)
