This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010
Excel has a little known but very powerful feature that can help users to build much simpler and easier to read formulas and, if used correctly, can also help to make models much easier to modify without breaking references or referencing the wrong cells. The key to this is Implicit Intersection.
First we will look as some simple ways to use it. Here is a screenshot of the sample book which can be downloaded here.
We have a simple sheet with three columns of numbers (C,D,E: column headings coloured in blue). The first column is a list of quantities (Qty), the second is a list of prices (Price). We want to know the cost for each product.
In Column E you can see the traditional way of setting up this calculation.
Now look at column F. We have repeated the setup to the right to make it easy to compare. This time we have referenced both entire columns.
But the result is the same. This is because Excel will automatically return the value in the referenced column by intersecting the column and the current row.
With the sample workbook open, move the mouse down the cells in Column E. You will see every cell has a different formula. The formulas are consistent but if there was an error, would you easily spot it? Now do the same exercise on Column F. The formulas are identical. You can immediately notice when one is wrong.
Does this impact performance? No. I have run tests using thousands of rows and the calculation speed is identical. So the initial benefit here is really in making formulas much easier to read and making it easier to spot errors.
Now, if you are to add range names to this, you will see even more, how useful this can be. Named Ranges are covered in detail here. This time, we have the same concept but we have named both the Qty and Price columns (The headings of the Named Ranges are coloured in orange). Now compare the two formulas and decide which is easier to use. Look at Column G.
= Price x Qty
There are many easy ways to trace what range a Named Range refers to. Again this will be covered in another post.
Implicit Intersection works in the same way with rows as it does with columns. Excel will intersect the current column. This is shown in Row 12 in the sample workbook. This row simply gets the data from Row 10 but it can be used in more complex formulas.
Using Implicit Intersection does the following:
- Makes formulas easier to read.
- Makes errors easier to spot.
- Reduces the chance of making errors. It is quite easy to click on the wrong cell when editing a formula. It is far easier to click on a column heading.
- Makes formula more flexible to modify in the future.
Implicit Intersection will also work on ranges that are not entire columns or rows. However, in most cases it is easier and more reliable to work with full columns and rows.
Implicit Intersection will not work in an array formula as array formulas are designed to work on each element in the given range. It will however work when referencing the result of an array formula. Array formulas will be covered in detail at a later date but in the meantime if you need further information, google Excel Array Formula.
This is part 1 of a 2 part post. Continued in Excel Intersection 101: Explicit Intersection
Implicit Intersection Sample Workbook