Whole Column References can get you in TROUBLE with Excel

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

Watch on YouTube & Subscribe to our Channel

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:

Question on Whole Column References - Craig - Quote
Question on Whole Column References - Cary - Quote

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).

Whole Column Reference Example

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.

Whole Column Reference Keyboard Shortcut

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.

SUMPRODUCT test

Using the FILTER function, which is a dynamic array formula, the time difference was almost 20 seconds.

FILTER test

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.

Incorrect Data Caused by Whole Column Reference

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.

Excel Table instead of While COlumn Reference

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.

Dynamic Named Ranges in Excel Automatically Resize

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#)

Spill Range Reference in the Source of a Data Validation List

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.

Conclusion

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!

5 comments

Your email address will not be published. Required fields are marked *

  • Good article Jon,

    I used to use dynamic named range all the time – for summary formulae, charting, conditional formats…

    Then Tables came along and I don’t think that I’ve used them since
    Tables also make your formulae much easier to read, I really can’t understand why some (experienced) Excellers avoid using them

    jim

    • Thanks Jim! I appreciate you sharing your experience with moving to Tables.

      There are pros and cons to Tables, but for me, the pros outweigh the cons most of the time.

      One of the biggest cons, and I’ve talked about this in the past, is structured reference formulas. If you are sharing your file with other users and they are not familiar with structured references, then there will be some confusion and you will likely have to train them.

      However, I usually look at this as an opportunity to help others learn about this amazing feature of Excel.

      In scenarios where you don’t have direct contact with users, the Tables might not be the best option.

      For example, a few years ago I helped with a project for our church that required some lists of data in Excel. I knew this file would be forwarded to several people to input data into the lists. I chose not to use Tables because I knew that not everyone would be familiar with structured reference formulas. We couldn’t provide training to everyone up front, and I didn’t want to slow down the process, cause frustration, or get a ton of calls and emails with complaints.

      Structured references (Table Formulas) are an application-level setting, meaning you can’t turn the feature off in your workbook and have the same behavior for other users.

      Therefore, the use of Tables depends on your audience and users of your file. I believe this is the biggest reason that more people don’t use Tables. In my opinion, structured references should be turned off by default.

      I hope that helps. Thanks again and have a nice weekend!

      • Don’t agree, I believe structured refs make formulae easier to read
        If you don’t think so, then normal refs (A1 style) can still be used

        Also, for non-experienced users, the self-expanding nature of Tables is surely a winner by itself

        as always with Excel though, there is never just one right way, nor just one right opinion

  • For my purposes, adding additional data to a column is the entire point – my spreadsheet is for tracking and analyzing data for all subsequent events over the course of a year. Where I run into trouble is that I can’t use whole-column references because I need header rows, and other ways of referencing the data seem to break if I ever insert a row to capture past data in sequence. Perhaps dynamic named ranges would be the way to go, but they look like they would make my formulae unworkably long and cumbersome. Do you have any suggestions?

    • Use a LET statement with INDIRECT to define your range, and the MAX & ROW functions to find the last used cell in a column.

      =LET(ary,INDIRECT(“A1:A”&MAX(ROW(A:A)*NOT(ISBLANK(A:A)))), )

      While this technically still uses the entire column reference, it only calculates it once to find the last cell that is not blank. All future references within the formula will only reference the actual range, and the result is dynamic, allowing your dataset to grow.

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter