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.
4. Next, we click the Excel icon in the Taskbar, and select the workbook we want to reference in our formula.
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.
6. Click on the cell on the worksheet you wish to reference, which will populate the selection into your formula.
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.
8. This action will return you to your original workbook and populate the referenced information.

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.