Hi, this is Wayne again with a topic “Excel 3D Formulas”.
In this article, we will look at how to create what are called 3d formulas in excel, and the reason these are called 3d formulas is because they help you pull information that is not on the x-axis or the y-axis, but is actually on the z-axis going deeper Into a workbook, so hopefully that makes sense and as you can see here, i have a workbook made up of several spreadsheets and each of these spreadsheets tracks game sales for a hypothetical board, game and card game store. You can see that it goes back to 2018 and each of these sheets tracks the sales for each month of that particular year, and each year’s sales figures are totaled here in green. Now, it’s important to notice this that each of these spreadsheets is set up exactly the same way. The data is different, but the layout and where the information is stored is in the same place. That’S a huge advantage when you’re trying to do 3d formulas in excel. So let’s say i manage the books and the numbers for this small business, and maybe my supervisor or my boss asks for all time totals what is the total amount of revenue brought in by this company since it began in 2018 and also what are the total Number of items sold all time by this company, so there are a couple of different ways. We could do this. Let’S look at a way that just links the data from these other spreadsheets to this cover sheet. Okay, so the first method would be to click on the cell, where you want to display, in this case the total sales revenue and then you would type equals and you could just type the function sum left parenthesis and then, with the parenthesis open with just the Left parenthesis typed in you can click on the next spreadsheet down here by clicking on the tab, and you can see the formula is still active here at the top in the formula bar and then i could click on the grand total sales for the year 2021. In this case, you’ll notice it changed the formula and then here in the formula bar i’m just going to click and then type a plus sign and then i’ll go to 2020 and select the cell with the grand total sales and then go back up here to The formula bar tap a plus sign – i could go to 2019 and do the same thing and then type a plus sign and 2018 click on the grand total sales for 2018. And then i can simply go up here to the formula bar put in a right parenthesis and tap enter on the keyboard and that formula returns the grand total all-time sales revenue, so that i can see that.
So that’s one way to do this. Just link the data from each of these other spreadsheets to the front cover sheet, but that was kind of a pain. Let’S look at how to do it a much easier way and, let’s see if we get the same results this time, i’m going to use a 3d formula so i’ll delete the number that was there tap enter okay once again i’ll click on the cell, where i Want to display the all-time total sales revenue and again i’ll type equals and i’ll type sum left parenthesis and again i’ll click on 2021 and i’ll click on the cell, where the total sales for 2021 are listed. So that’s b16. Now the trick to make this a 3d formula, is you just hold the shift key on the keyboard and go down here and because i want to add up all four of the grand total sales for each of these years, holding the shift key.
I just click on 2018 and look. It highlighted all four of those tabs all four of those spreadsheets, basically and now i’ll tap, enter on the keyboard and look it produced the same result but much easier, much quicker. Now, keep in mind.
The only reason that worked is because all of these spreadsheets follow the same format the same layout. The information in b16 in each case deals with grand total sales for that particular year. Let’S try it again, but this time with just the total items sold all time. I’Ve clicked on the cell, where i want to display the data i type equals sum left parenthesis now. In my example, i want to add up things, so i’m using sum a lot, but keep in mind you can do the same kind of 3d formula with max min large small average just about any function just about any formula. You can turn into a 3d formula. Okay, so what do i want to sum? Well i’ll start with 2021 total items sold in a year and, of course, that shouldn’t have a dollar sign.
That’S a mistake on my part: it should just be total units sold i’ll click there on the cell and then going down to the tabs for each of the spreadsheets. I hold the shift key and notice. If i were to stop at 2019, it only highlights 2021. 2020. 2019, but i want to go all the way to 2018, so i’ll click on that as well.
Now i can release the shift key and tap enter on the keyboard. Total items sold 10. 545.. Now, like i said those aren’t really dollars.
So i’m going to change this just to general and i’ll add in a comma there, and i can get rid of the decimals using this button here and i will fix this in the practice sheet so that you won’t have to make these changes yourself. So hopefully you can see the advantages of using 3d formulas to pull in data from more than one sheet and bring it together onto one sheet thanks for watching. I hope you found this tutorial to be helpful. If you did please like follow and subscribe and when you do click the bell so you’ll be notified.
When i post another video, if you’d like to support my channel, you can do that through my patreon account and you’ll see a link to that in the description below speaking of patreon. I wanted to give a quick shout out to my five dollar patreon supporters. Thank you so much for all you do to support my channel. .