Join Dennis Taylor for an in-depth discussion in this video Creating 3D formulas to gather data from multiple sheets, part of Excel 2007: Advanced Formulas and Functions.
On your screen, you're seeing sales totals for an Eastern region. I'm going to switch to the next worksheet to the right to display a similar worksheet that's showing sales totals for the South region, and then the next sheet over in Midwest. Each of these worksheets has the exact same layout, and there's a West sheet as well. You'll recognize there are formulas in Row 8, in all cases here, and in Row 9, and in Column G we've got totals as well, and in Column H.
Each worksheet has exactly the same layout. And also created here is a Summary sheet, and it too has formulas in place, but there's no information here. So that's why these look a little bit strange. The division by 0, it's trying to simply perform a calculation that for the moment is inappropriate. But this particular worksheet is an exact copy of the others, with no data here. Now, you could imagine a similar situation dealing with data where you might have 12 separate sheets, data for each month.
You might have different fiscal year data, five, six years potentially. You could have information from the 50 states, any number of different scenarios where it is appropriate to have the same layout worksheet after worksheet after worksheet. And sure enough, on a Summary sheet what we would like to be able to see here is a total, in all of these cells, that shows the information from the four other sheets. A standard way to do this, and not an incorrect way, would simply be to position the active cell on the Summary sheet and write a formula, and you'll notice in this formula there will be practically no typing, except for the initial equals and some occasional pluses.
So we are on the Summary sheet here. I just clicked equals. Now let's jump to the East sheet, click there, and we are about to show you the Disk Drive Retail total there. Click on the East sheet, click this cell, and put in a plus. And now we will go to the South sheet, click on the appropriate cell again. It's B4, put in a plus. The formula is building in the formula bar. We will click on Midwest, click that cell, click plus. And then West, click the cell in question, we are all done. Enter.
I am going to make the column wider so we can see this. And I think you could agree, the information is accurate. It's correct. We could go back and look at the numbers. It is correct. But the formula is a little bit dismaying in the sense that, although valuable, what might we do if it were 12 months, or if it's 50 states. This is a lot of jumping back and forth. You will notice that range names are automatically followed by exclamation points. If you find yourself typing range names, you're probably going down the wrong path. Stop, and go click on them instead.
Who wants to type when you can use this method? And one other bit of note here too. If a range name has a space in it, you will also see single quotes here. That's not the case here, so we wouldn't worry about it. So this could be just fine, if you don't mind clicking back and forth to create this. We could then copy this into the other cells. That's not a huge amount of work. But we do want to show you a faster way to do this, when you're dealing with multiple sheets. Again, imagine that 50 state scenario here. So instead of this, let's approach this in a different way.
On the Home tab, in the Editing group, let's begin with the AutoSum button. Simply click it once, and this is one of those rare times where the Sum function can't see any data around, there is nothing to guess at, so it simply saves us a little bit of typing here. Now, the next two steps are the crucial ones, and not the obvious ones. Let's say we want to add up all the data on the sheets East through West. We have got four sheets here. Remember, there could be many, many sheets. The first thing to do here is to click on the East sheet, and it shows us the East sheet.
And the unusual step here, hold down the Shift key as you click the West sheet, and that essentially picks up all the intervening sheets. In this case, only two, but there could be many. And in the formula bar, you will see how the formula is being constructed. What it lacks is a reference to the specific cell. In this case, it's going to be B4. Click there, and you will notice a strange change here as we complete the entry. Right now you see single quotes. Don't worry about them.
As soon as we hit Enter here, the formula is complete. Same answers we saw before, but this is what it looks like. And imagine that scenario. If these were the 50 states, if they had been alphabetized, that might say Alabama:Wyoming, something like that. If it's 12 months, it's likely to say January:December, and so on. Try that a few times and you realize how fast it is. This only works in situations where you have got identical layouts in your worksheets. So the situation here, we are always looking for cell B4 here.
Now, once that is done, we can double- click or simply drag this down two cells, drag it across, and readjust that column width, and we are all set. This truly is a Summary worksheet. Now, every one of these formulas, let's just pick one at random. We can double-click and see the formula there. They all operate along the same principles. If you move any sheet that happens to be between East and West, if you move them within those confines, nothing changes here. If, for example, if we were to take out the South sheet here, our totals would drop, but it wouldn't disrupt any of these formulas.
If we were to create a new mountain region here and had it somewhere between East and West, the formulas would not need to be recreated. The information would automatically be included. So you have got the same kind of flexibility that you might have with a Sum function that adds up data in a column. When you insert rows between the top and bottom, they are automatically included, and if you delete them, that data gets removed. So a similar kind of layout situation. But this 3D formula, there is no question about it. It's extremely powerful, and the more worksheets you have, the more powerful it is.
- Referencing, copying, updating, and converting formulas
- Using the logical functions and creating compound logic tests
- Searching for and matching data based on specific criteria
- Formatting cell data using functions
- Calculating dates, times, and days of the week
- Analyzing mathematical and financial data