Want to make your Excel work easier and faster? Imagine if you could pull data from multiple worksheets and combine it into one effortlessly. That’s what a 3D Reference in Excel lets you do.

In this tutorial, we’ll walk through how to use a 3D Reference to calculate data across several Excel worksheets in a workbook. We’ll be combining sales data from four regions: USA, Europe, Asia, and Australia into one worksheet. Not only will you know how to set up the formulas, but you’ll also learn how to copy them without messing up your formatting.

Keep reading to learn step-by-step how easy this can be!

What is a 3D Reference in Excel?

In simple terms, a 3D Reference lets you reference the same cell or range of cells across multiple sheets. With this, you can pull data from different worksheet tabs and calculate them together in one place. It’s perfect for situations where you’re working with the same type of data in separate sheets, like sales reports for different regions, departments, or time periods.

Why use 3D References in Excel?

  • Saves time: No need to go through and manually add data from different sheets.
  • Consistency: Keeps everything clearly organized by referencing the same cell across multiple worksheets. You’ll minimize mistakes.
  • Automation: Once set up, it’s easy to scale the formula across additional data sets.

Setting Up Your 3D Reference in Excel: Step-by-Step Guide

Here’s how you can set up a 3D Reference in your workbook. In this example, we’ll sum annual product sales data from four regions: USA, Europe, Asia, and Australia.

Step 1: Open the Formula

  1. In your target worksheet (in this case, it’s the “Total” sheet), click on the cell where you want to display the combined data (let’s start with cell B6).
  2. Begin your formula by entering =SUM(.

Next – Step 2: Click the First Tab (Starting Sheet)

  1. Click on the first tab of the worksheet you’re pulling data from, in this example, the “USA” tab.

RELATED TOPIC: Mastering Excel: Absolute and Relative Cell References Made Easy

Step 3: Select the Range Across Sheets

  1. Hold down the Shift key, and while holding it, click the last tab of the range (here, it’s the “Australia” tab).
    • This step will highlight the worksheets in between, i.e., the selected range will include USA, Europe, Asia, and Australia.

Next – Step 4: Choose the Cell

  1. Still holding the shift key, click the cell in these worksheets that holds your data. In this case, it’s cell B6.
  2. Let go of the Shift key and press Enter.

Step 5: Check the Result

Voila! Excel adds up all the values in cell B6 across the four regions and shows the result in your “Total” sheet’s B6 cell.

Finally – Step 6: Auto Fill the Formula Across Other Cells

Now, you most likely have more cells to calculate, like quarters 2, 3, and 4. Instead of manually repeating this process for each cell, drag the formula across.

  1. Select the bottom-right corner of the cell (you’ll see the small square appear).
  2. Click and drag it across and down to fill the formula for additional cells.

There’s a catch: Once you do this, Excel automatically copies the format from the original cell along with the formula. That can mess up your worksheet’s formatting.

RELATED TOPIC: How to Use the IF Function in Excel

Step 7: Avoid Breaking Your Formatting

To avoid disturbing the formatting:

  1. After dragging the formula into other cells, a little AutoFill Options box pops up.
  2. Click this box and select Fill Without Formatting.

That’s it! Now your data is filled in correctly without destroying your formatting.

FAQs When Using 3D References in Excel

Can I Use Functions Other Than SUM?

Absolutely! While SUM is commonly used, other functions like AVERAGE, COUNT, or even MAX can all be used with 3D references.

Do I Need to Have Continuous Worksheets?

No, you don’t. Even if your sheets aren’t adjacent (for example, skipping a few in between), you can create a reference to specific sheets by manually typing in the names inside the formula.

How Do I Update the a 3D Reference If I Add a New Worksheet in Excel?

If you add another tab between the first and last sheets in your reference, Excel will automatically pull data from the new sheet as long as it’s within the selected range. If you add the new sheet outside, you’ll need to update the formula.

RELATED TOPIC: How to Use the VLOOKUP Function in Excel

Conclusion

Using the 3D Reference feature in Excel is one of those tricks that can drastically cut down the time spent updating workbooks. Whether you’re calculating regional sales or keeping track of department expenses, understanding how to combine data from multiple sheets makes a major difference in efficiency.

If you found this guide helpful, make sure to subscribe to my channel for more tips and tutorials. And of course, feel free to reach out with any Excel questions you might have!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR