Creating 3D References between Workbooks in Excel

 Creating 3D References between Workbooks in Excel

Using 3D References between worksheets in an Excel workbook has been around for many years. It 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. However, it is not well known that a 3D connection can be made in different workbooks, which actually adds a new level to the  using Excel more like a database.

It works the same as a regular 3D Reference, 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 MCT’s Excel training courses.