Join Dennis Taylor for an in-depth discussion in this video Creating 3D formulas for worksheets with identical layouts, part of Excel 2013: Managing Multiple Worksheets and Workbooks.
In this workbook called 3D formulas, there is a tab for each state and also a sheet 1, an empty sheet in front of Alabama here to the left. Now let's imagine, what we'd like to be able to do here, is to actually set up a summary sheet that's going to tabulate the data from all the other sheets. And these sheets happen to all have exactly the same layout. As I click on the Alaska tab, you will notice a slight visual difference because column C is going to vary depending upon how wide the sheet name is, or the state name is.
And so as we see this, we do see they have exactly the same layout. Now, the term 3D formula is not an official term in Excel. But it is widely used and the concept here might be, if you could think of each of these sheets here as being printed perhaps on a transparency, if we stacked them on top of one another, this data right here would look identical. This data right here would look identical, all the labels of course would be the same, title up top and so on. But the numbers of course are going to be different.
But what we'd like to be able to do is to pull together all this information. And you could certainly imagine a similar model like this with different regions of the United States or maybe for different months of the year. And in those cases too, you might want to set up a summary sheet. Now whether you put it after all the sheets or in front, makes no difference. And we want to show you two broad approaches to this. I'm going to first of all suggest we want to create a summary sheet. Now, one thought is why don't we just take this information here, copy it, paste it on to sheet 1, change a few words, and we're all set.
Nothing wrong with that, but it's going to be more efficient to simply copy the Alabama sheet. And if we copy the sheet, we're going to get exactly the same column widths and row heights and all the formatting is going to be the same as well. So I'm going to drag the Alabama sheet to the left here, with the Ctrl key held down. So Ctrl key is held down. I'm dragging Alabama to the left of sheet 1. Let go of the mouse and now I've got two Alabamas for the moment. Here's sheet one, we don't need this anymore.
Right click and delete. Nothing in it really. Delete it. And here's Alabama 2, the one we just made a copy of. Let's change its name to summary. Simply double click, type summary. And so that we're sure that we're working on the summary sheet, let's go to cell C3 and type in the word Summary. For the moment, we still have the Alabama numbers here. Now, a quick look at this worksheet, and also I'll use a keystroke shortcut here to remind you, if we want to see where the raw data is compared with the formulas, we can press Ctrl+tilde.
That's above the tab key. And we can see pretty clearly that here is where the raw numbers are. You can also do this by way of the formulas tab here. Show formulas, click it, click it, we see what's happening. So we simply want to get rid of these and replace them with formulas. Now in a certain sense we really don't even need to get rid of them we could simply type over them with new formulas. So it makes no difference whether we're seeing the other formulas or not now. I'll press Ctrl+tilde again to go back to normal. But we've identified these as the raw numbers.
I'll just delete them. And the formulas on the perimeter, of course, for the moment look unusable. And they are. So, here's what we'd like to do. We'd like to start adding up the sheets. Now, let's just suppose for the sake of argument here, we only want to do the first four sheets or so, at least for starters. So I'm going to type equal, click Alabama. Now, before I do this, I want to point out too, that since the layouts are all the same. If we're trying to add up the total for retail chairs, we're always going to cell B6.
So right now, after typing equal, I'll click Alabama. Click, Cell B6. Put in a plus. Go to Alaska. Click cell B6. Put in a plus. And, I think already, you're getting the idea here. This is not going to be very efficient. Here's Arizona, and I'll just do one more, Arkansas and click there and enter. It's certainly okay for a few sheets perhaps, four or five maybe at the most. That's what the formula looks like. Never type those sheet names, by the way, just click on them.
And that certainly would make sense for a small number of sheets. But if we've got 50 sheets this is not the way to go. The answer's okay, it's correct. We can go check it out and if that's all we needed to do we could certainly copy that into the other cells. But we want to cover 50 sheets here. So let's get rid of that completely. Let's start with autosum. That's going to make it a little bit faster. So either from the formulas tab where you see autosum on the left side Or on the home tab where you'll see it on the right side. Let's just start right here.
Click autosum. Now, the next step we're about to do is unusual, but it's not that unusual if you're familiar with working with multiple sheets. In an earlier movie you might have seen that there will be times when you need to select multiple consecutive sheets. And we do that by clicking the first sheet, and then with the shift key, the last sheet. Now, it's a little more awkward this time because we're not seeing all the sheet names that we need. So the first step after putting in auto sum is to click on the Alabama sheet down below.
We next need to see the Wyoming sheet, but a lot of you know, and you might have seen this on a previous movie too, if we hold down the Ctrl key and click this right arrow that's in the lower-left corner, hold down Ctrl and click the right arrow. We now see the last few sheet names. Here's Wyoming, but before we click on it, we want to make sure we've got the shift key held down, we click Wyoming. And if you look in the formula bar, you see that the formula is being built, the only thing it lacks is the actual cell reference, so let's click cell B6.
And our formula is ready. We'll simply press enter. Or if you don't want to move the active cell, press Ctrl+enter. And there's the total. And as I double-click it, there's the formula. And you wouldn't instinctively know this, but this simply is gathering cell B6 from all those sheets, Alabama through Wyoming. All the other 48 states between the two there and we see how this is being gathered properly. Enter, copy that downward then rightward, we can just double click in this case.
And then copy to the right and of course we'll have to make a column adjustment for column G and perhaps the others as well. Just click and drag across the relevant columns like this, double click a boundary, so called best fit kicks in. So we've got formulas in these cells right here. Each of them is gathering data from 50 different sheets. And it's a very compact formula, and relatively easy to create too. And certainly much, much simpler than trying to click on each of those sheet tabs.
So this is an example of what is sometimes called a 3D formula. It's extremely efficient. If you move the Wyoming sheet or the Alabama sheet, you're going to have to rewrite these, and of course you probably wouldn't do that. If we have another state or if you wanted to include D.C. in here, we could insert a sheet, for example, to the left of Delaware. Maybe copy the Delaware sheet, adjust it, put in the D.C. total if we needed to add that. And then all of our totals will be gathering data from 51 sheets. So it's easily expandable that way.
So this is a great tool for tabulating data from multiple sheets, by way of a 3D formula.
- Navigating across worksheets and workbooks
- Displaying multiple worksheets or workbooks simultaneously
- Inserting, deleting, moving, and copying sheets
- Changing sheet names and tab colors
- Showing and hiding worksheets
- Creating linkage formulas among workbooks
- Locating and managing links