Creating 3D References between Workbooks in Excel - AdvantEdge Training & Consulting

Creating 3D References between Workbooks in Excel

 Creating 3D References between Workbooks in Excel
Using 3D References allows us to use Excel more like a database than a spreadsheet. As Excel functionality has increased, so has Excel’s capabilities, including the speed that connections update, and the types of connections that can be made between different software applications. One of the best uses for 3D references is to create formulas that pull data across multiple worksheets or workbooks. Using a 3D reference formulas across workbooks works the same as a regular 3D Reference across worksheets, but there are some subtle differences. Follow these steps to see how a 3D Reference is made between two workbooks:

  1. Ensure that all workbooks being used for the 3D Reference are open, as you will need access to them during the process. We are going to use two workbooks from two of our Excel classes, and create what is called a Simple 3D Cell Reference.
  2. Click inside the cell in which you will build the formula. For our exercise, we will select an empty cell, as it is much easier to see the process.
  3. Anytime we create a formula, we need to type an equal sign, so Excel knows we are creating a formula. 3D References between workbooks in Excel - Equal sign
  4. Next, we click the Excel icon in the Taskbar, and select the workbook we want to reference in our formula.3D References between workbooks in Excel - Workbooks
  5. Once the workbook appears, click the worksheet within that workbook that you wish to reference. This will start to populate the formula with some references that may look kind of strange. 3D References between workbooks in Excel - Formula bar
  6. Click on the cell on the worksheet you wish to reference, which will populate the selection into your formula.3D References between workbooks in Excel - Formula Population
  7. It is important to understand that the formula you are seeing in the formula bar is actually populating in the original workbook where your reference will populate. From here, either press the Enter key on your keyboard, or click the checkmark just to the left of your formula.3D References between workbooks in Excel - Check
  8. This action will return you to your original workbook and populate the referenced information.3D References between workbooks in Excel - Data connected

These types of references can be used in any formula or function as well, but remember that this is equivalent to a connection. Where you keep the file and the reliability of your network dictates if the information is able to reliably update.

Want more information on 3D References, or ways in which you can use Excel like a database? Check out AETC’s private group training or Public Training CenterExcel courses.
If you have at least 5 people we can create a session just for you.  Or, you can contact us for a private session. Contact Us

Questions? Don't see what you need?

We can help!

Business Training Classes
AdvantEdge Training & Consulting, Inc.

Recent Posts

Subscribe to our Newsletter

Copyright AdvantEdge Training & Consulting

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top