This post applies to the following versions of Excel: Excel 2003, Excel 2007 & Excel 2010
This is part 2 of a 2 part post. Continued From Excel Intersection 101: Implicit Intersection
As opposed to using Implicit Intersection, you can also use the intersection operator to force an Intersection between rows and columns. This intersection is called Explicit Intersection. The Intersection operator is a “Space”.
First we will look as some simple ways to use it. Here is a screenshot of the sample book which can be downloaded here.
This time we have a simple table showing costs for four different products over three years. Below this table under the section titled “Showing Formulas” there is an output table that transposes the totals. This is an example of a nice summary table. It could be located on any sheet in the workbook.
In this summary table, we simply need to get the Total Costs for each of the three years. On the first row of the summary table, we use the traditional way of referencing. On the second row, we create a reference to the Total using Explicit Intersection. On the third row, we use Explicit Intersection with Named Ranges to get the following formula:
=Year2011 Totals (which simply returns the value in cell E10)
In this example we are forcing Excel to return the value at the intersection of a row and column but it does not need to be in the same row or column as the formula. In fact, it can be on a different sheet entirely. Or even in a different workbook!
The next example takes this concept a stage further. What if we want to refer to a range of cells at the intersection of several rows and a column. This is illustrated in Row 19. Here we use various methods to sum up the values to get the Total Costs. In C20 we use the traditional method. In D20 we use Explicit Intersection to return the range that we need to sum, and in E20, we use Explicit Intersection combined with Named Ranges to do the same. Note the differences in these three approaches.
As with Implicit Intersection, there seems to be no performance impact of this approach,. However, using Explicit Intersection offers the same benefits over the traditional approach as Implicit Intersection does but with the addition of these:
- Fewer restrictions and even more flexible.
- Not limited to being in the same row or column.
- Not limited to individual cells. Entire ranges can be referenced.
Explicit 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 entire columns and rows.
If you refer to just a simple range using the traditional approach i.e. with direct cell references, it is very easy to insert rows or columns that will mess up this range. Or just forget that the range is there. Remember, with Best Practices layout, you should always use a column for a particular purpose and rows also should be sectioned for common purposes. This way, if the rows are grouped and labelled and the column is labelled then it is very hard to mess up this formula. I often come across ranges that users have squeezed in somewhere and forgotten about and then when they modify the spreadsheet, this range gets shifted and formulas derived from it get orphaned.
As Explicit Intersection enables entire ranges to be referenced, it can be used with all the functions that look for multi cell ranges. It allows users to reference areas of the spreadsheet using entire column and entire row references which creates far more robust formulas. It also makes it much easier to work with Named Ranges as just few Named Ranges can be intersected in different ways to create multiple ranges; each one extremely robust and easy to maintain.
Explicit Intersection will not work when used in the following situations:
- In Array Formulas – Sometimes it’s fine. It depends on the usage.
- In Data Validation Formulas.
- In Conditional Formatting Formulas.
Having said that, Intersections can be used in all these places as there is a cunning work around. If you are like me and don’t like to compromise your standards and are allergic to direct cell references, then there is always a solution. I will add a link in here when I have written a post on it.
Personally, I use the Intersection of rows and columns for almost all my references and define Named Ranges for everything. This way my models are extremely robust and are virtually impossible to break. When making key changes, it is very easy simply to redefine a Named Range or do a global replace on a Name. Usually, the time taken to incorporate additional functionality takes only a few minutes before it’s ready to use and the rest of the model is “guaranteed” still to work as expected.
Intersections can be a great way to reference tables (I do not mean Excel Tables which can be references with Structured References) on other sheets and not worry about what rows and columns are inserted. Actually, its quite similar to using Excel Tables Structured References which are also a good way to work with data in Excel.
I hope this will be enough to get more people to use Intersections as a means of creating references in Excel. I will add a subsequent post on advanced uses of Intersections with more sophisticated examples. However, I have first to submit posts on other areas of Excel that will be used in the more advanced examples.
Explicit Intersection Sample Workbook