How to Create and Use 3-D (3-D) Tables in Excel 2010: Spreadsheet

How to Create and Use 3-D (3-D) Tables in Excel 2010: Spreadsheet covers the essential facts behind the topic, including what changed, why it mattered, and how it affected users.

Spreadsheet Overview

In this article, a tab can serve as a third dimension. Think of your sheet as a cube containing numbers instead of just a 2-dimensional array.

To be more specific to illustrate this, think of a building that has three floors, each with a 4 × 4 array of tenants. And each room has a person in which the representative is their name. With a spreadsheet, all floors will be represented 2-dimensional by a floor map, the first floor will look like this:

The second floor is like this:

. And the third floor is like this:

Each floor is a separate tab in a worksheet. To make it clearer, you can change the names of tabs from Sheet1, Sheet2 and Sheet3 to First, Second and Third, like this:

Now to use the 3-dimensional sheet you created, you need to think that each person lives in 3-dimensional space, with an address. For example, a person named Rena lives on the third floor, in cell B2, so her address will be ThirdB2, or in spreadsheet Third! B2 (exclamation marks the address between the tabs).

So, to use as a sheet you can add something useful to the sheet, like the question "Who lives in room X on the Yth floor?" So the apartment manager can find it easily in this case, just type in the address and floor name. In the table, it should look like this:

Cells with representative circles, where a user of the sheet will type the first room number, such as d4, then the floor, and then the sheet will give the answer, like this:

Jill lives in room d4 on the First (first) floor, which you can see by looking at the First floor tab above. Of course, to make the sheet give you the answer as seen above, you must use some Excel functions, in which case you will first need to build the address by concatenating the floor name with address box by doing this:

Here, because this floor name is of cell K10, and the cell address (room) is I10, we can make the addresses by putting them together by using " & " and adding a sign than "! " between them and it will look like this:

In cell K16, we chose randomly to keep the result of the pairing. Next, in the answer box, (where Jill is shown above), type = INDIRECT (K16) to make the content of cell K16 be interpreted as a cell address rather than plain text, The result is the answer we will seek, Jill.