Bottom Line: Learn what whole column references are, as well as the pros and cons of using them in formulas.
Skill Level: Beginner
Watch the Tutorial
Download the Excel File
Here's the workbook I use in the video.
Are Whole Column References a Good Option?
I received a couple of great questions on my tutorial for avoiding XLOOKUP errors about using Whole Column References. Other names for this are Full Column References and Entire Column References. Here are the questions:
While there are some advantages to using whole column references, my advice is to avoid them. I'll tell you why. But first, let's talk about what a whole column reference is and how it works.
What Is a Whole Column Reference?
Just like it sounds, a whole column reference is a reference within a formula that indicates an entire column of data from the top of the sheet to the bottom. It's notated by typing the column letter, a colon, and then the column letter again (D:D, for example).
In my XLOOKUP formula below, you can see that both the lookup and return arrays are denoted with whole column references (G:G and I:I).
Advantages of Using a Whole Column Reference
1. Easy to Read
As you can see, whole column references are super easy to read. (Compare the formula for the whole column reference with the formula using range references in the image above.) That's one of the pros of using a whole column reference.
2. Automatically Includes Additions
Another advantage is that anything added to the column above or below your existing data will be included in the formula you write. As we'll see in a moment, this can be both good and bad, depending on how careful you are with data placement.
3. Avoid Range Length Errors
A third advantage is that you'll never receive a range length error. This happens when a lookup function gets confused if there is one range that's longer than another.
4. Easy to Write
And finally, whole column references are easy to insert into a formula. In addition to being simple to type, you can also insert whole column references into a formula by simply clicking on the column letter in the header or by using the keyboard shortcut Ctrl + Space when a cell from that column is selected.
Two Reasons Not to Use Whole Column References
Despite the advantages I just mentioned, I would recommend avoiding whole column references for two main reasons.
1. Slowed Performance
Charles Williams, one of my fellow Microsoft MVPs, wrote an excellent article outlining the results of testing he's done using whole column references. You can read it here.
To summarize the article: Excel can really slow down as a result of whole column references, depending on what function is used.
I conducted some tests of my own and found this to be true.
Using the SUMPRODUCT function on a list of 1,000 formulas, the difference between using a whole column reference as opposed to referencing a range of cells was more than 15 seconds. That time would obviously increase for spreadsheets with more data/formulas.
Using the FILTER function, which is a dynamic array formula, the time difference was almost 20 seconds.
These tests are relatively simple, and it's not hard to imagine how much longer the performance time would take with more complex formulas and larger amounts of data.
2. Incorrect Results
When you use whole column references, it's imperative that you never add any additional data to the columns that you've referenced unless you want to include that data in your formula calculations.
If you've used Excel for any amount of time, however, you know how common it can be to add additional calculations or to stack data tables in a worksheet. Below is an example where I've quickly calculated some revenue amounts by region and placed them below my total in Column G. The problem is that I've referenced the whole column on another worksheet, so those additional totals are erroneously being added into my calculation. Therefore, my formula is returning incorrect results.
You may start out with good intentions to never add any entries below your data. Over time, however, you might forget. Or someone who doesn't know about those intentions might modify the worksheet, accidentally corrupting your results.
Alternative Solutions to Whole Column References
There are three alternatives to using whole column references:
1. Excel Tables
Excel Tables are like a container within a worksheet for your data. You can reference the entire column within the table without having to worry about data above or below the table accidentally getting included.
In addition, new data that gets added or deleted into the table will automatically factor into any formulas that reference the table column.
2. Dynamic Named Ranges
This is another popular technique for creating range references that automatically expand when new data is added to a range.
In the video above I show one method of creating a dynamic named range with the OFFSET and COUNTA functions.
These are a little more complicated to set up. Like Whole Column References, Dynamic Name Ranges can sometimes get you into trouble and can be especially tricky if you have a lot of blank cells in your columns.
You'll also want to make sure that users of your files understand how they work if they will be modifying formulas.
3. Spill Ranges
We can also reference spill ranges that automatically resize. Put the # symbol after the cell in the top-left corner of a spill range to reference the entire spill range. (A3#)
Check out my post on creating drop-down lists that automatically expand to learn more about this technique.
The spill range reference can be used in formulas as well.
My preferred method, when possible, is Excel Tables. When you use structured Excel Table references, your formulas will only reference the column data from within the table.
If you are interested in learning more about Excel Tables, check out this post: Excel Tables Tutorial.
I hope today's post is helpful for you. And I hope it keeps you from making some of the same mistakes I've made from using whole column references. Leave a comment below with questions or feedback!