Join Dennis Taylor for an in-depth discussion in this video Creating 3D formulas for worksheets with identical layouts, part of Excel 2010: Managing Multiple Worksheets and Workbooks.
When a series of worksheets have identical layouts, there are huge advantages to using what we call 3D formulas when creating summary sheets. In this workbook you will notice at the bottom the sheet tabs, Alabama Alaska Arizona, and as it turns out there is a tab for all 50 states and they all have exactly the same layout. Nowobviously different totals. What we want to do here is to create a summary sheet with the exact same layout as all of these and then on that summary sheet tabulate all these totals.
Now there are two broad approaches to starting this off and I think you will see which way is going to work better. One is to say, why don't we take one of the states data like Alabama here and simply copy this onto a Sheet1 here and then readjust it and change the wording and so on. We can certainly do that. What would not happen now is not all of the formatting that you wished would travel with it. A better way is simply to copy this sheet. So I am about to drag the Alabama sheet leftward, put it in the front of Sheet1, and as I do this I will be holding the Ctrl key.
This is the fastest way to copy a sheet. I am dragging Alabama leftward, I've got Ctrl held down, I deposit it just to the left of Sheet1, let go over the mouse first. I don't want to keep this name Alabama. I am going to double- click it and call it Summary. And by the way, I could have put this at the very end of all the state names and I can certainly move this there if that's where I think it belongs. Next thing to do is to change Cell C3 so that this says Summary. Although we don't really have to eliminate the data here, we could type over it.
I am going to get rid of this data here. These are the raw numbers in the worksheet from Alabama. We want to get rid of those. The totals in the formulas along the edges are still there. Looks a little strange right now since we have no data. But you see what's happening here. In this Sheet1 here we either want to delete or maybe put in front here. We might use that a bit later. So, we are about to set up a summary sheet here. Now, if we were only using three sheets, suppose it's the first three, we could build a formula in the following way.
In Cell B6, which in all sheets, by the way, always means Retail for SSDs, Solid State Devices. So, we could write a formula on the Summary sheet this way, if we had only few states. Now I think you can see what's going to be problematic about this if we have more than just a few sheets. I am going to type equal, click on Alabama, and click this cell, put in a plus go to Alaska, click here, put in a plus. You really would have to keep your wits about you too if you are trying to do this fifty times.
I am going to Arizona and I am going to click there. That's enough for the demo. I'll just simply press Enter here and there is a formula that is correct. It's accurate. It only covers three states. I think it's immediately obvious why we wouldn't want to be doing this for 50 states. We need a better way. That way is 3D formula. At the core of what we are about to do here is the concept or the idea that there are times when dealing with multi-sheet workbooks, where you want to select either a few sheets or a whole cluster of them.
And the key idea is, for example, if we wanted to select all of these sheets, we might click on the Alabama sheet and then with a Shift key held down, go find that Wyoming sheet and click on it. Well that's what we are about to do in this formula. And this is amazingly fast when you think of what we are about to do. We are about to tabulate data from 50 separate sheets. I am simply going to start here by clicking the AutoSum button, found either on the Formulas Tab on the left side or on the Home Tab here. Here is AutoSum.
Click it once, saves us a little bit of typing. Now the key step, to select all the sheets from Alabama to Wyoming, so I'll click Alabama at the bottom of the screen and then use the navigation arrows off to the left. I'll use the right most one so that we can see our last few sheet names. There is Wyoming. I am about to click on it. I will hold down the Shift key and click Wyoming. The only thing we need in this formula now is the reference to Cell B6. So I'll click on it, Enter, we are done, and this formula adds up data from 50 separate sheets. Cell B6 in every case.
I'll simply drag this down to here and then over to there and this truly is a summary sheet. Next thing to do would be to drag across these cells. Double click to adjust the column widths. Every one of the formulas in this part of the worksheet here is a so-called 3D formula and it's not that long when you look at it. The colon here, no doubt you know what that means. You've seen it with several references. All of the sheets between Alabama and Wyoming are included. We only see the first and the last here. Now, you will run into problems of course if you somehow decide to move these states around and put them in regional order.
Then you'd have to redesign the entire formula all over again, if that's what you need. But this is fast, it is easy, and again the unusual step in there. But not that strange really is the idea that we select all these sheets by clicking on the first sheet and then Shift+Clicking on the last and this does all of the work. There is no question that this is a huge timesaver and a powerful tool. It's 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