Create a 3d range

create a 3d range

A 3D range is a range that includes the same cell(s) on more than one sheet. This can be helpful when you have multiple sheets that all contain basically the same information but for different groups, people, organizations, months, or other distinguishing factor. You can use a 3D range to perform calculations upon the same fields on each of the sheets.

For example, suppose you have 12 worksheets, one for each month of the year. On each sheet, cell D12 contains the total sales. You could create a SUM function that uses as its range the contents of cell D12 on each of the sheets.

To create a 3D range:

1. Start out your formula or function normally, until you get to the part where you would enter the range.
2. Click the tab of the first sheet in the range.
3. Hold down the Shift key and click the tab of the last sheet in the range. All the tabs will appear selected (white). Release the Shift key.
4. Select the cell(s) you want to refer to on each sheet, and then press Enter. You're returned to the formula or function you were constructing in Step 1.
5. Finish the formula or function. For example, you might need to add a closing parenthesis for a function.

You can also manually write a 3D range in a formula. Simply refer to the sheet names in a range, with the first and last sheet names separated by a colon. For the earlier example with 12 months of sheets and summing the values in D12, the formula would look like this:

Of course, in real life, you'd probably have named the worksheet tabs for each month, so it might look more like this: