Don’t Believe It!
Don’t believe that Excel is just another spreadsheet program! It is much more. By using 3D functionality, you can make Excel act like a database. Excel is much more robust than it was ten years ago, and it holds much more data. Excel, however, will never be a database. Thus, you need to know when to use one or the other.
Excel is best used when a small group of people, or one person, needs to build something that will help them be more efficient, and the data being used does not slow the speed of the computer being used too much. Databases are used when everyone needs to add and use the same data, or if there is so much data, that it would bog down Excel too much.
3D functionality allows worksheets within the same workbook, or even other workbooks, to communicate. Keep in mind that there are roadblocks that can hinder functionality when working this way across workbooks. Careful planning is needed to alleviate any potential issues.
Let us see how 3D referencing is used in functions or formulas.
3D Simple Reference
- Ensure you are in the cell in which you wish to build the formula.
- Type “=” to start your formula. Then click the worksheet tab you want the information to be pulled from. (In our case, we are going to click on the Supply Costs worksheet).
- Even though the Supply Costs worksheet appears active, it is solely for the purpose of selecting the cell from which we want to pull our information. In our case, we are selecting G16. Note the formula in the Formula Bar and how the Supply Costs worksheet is referenced.
- Press Enter, which will take you back to the original worksheet. Note that the information from the other worksheet populated.
- Click the cell you wish to build the formula and type “=” to get the formula started.
- Click a cell with a number you wish to add to a number on a different worksheet, then type “+”
- Click a different worksheet tab, and select the cell you want added into your formula. Note how the cell is referenced in the formula.
- Press the Enter key, and your formula is complete.
Whenever the number from the other worksheet updates, it automatically updates the formula as well.
This is a simple 3D formula, but you can get as complicated as you need to, so that you can make Excel work more and more like a database.