How to select two noncontiguous columns in an Excel table

Excel tables offer several benefits for data storage and manipulation, one of the most significant being the ability to use structured references to programmatically select different elements of a dataset.

Consider, for instance, this product launch dataset. You can obtain your own copy by downloading the exercise file provided below:

When the columns you wish to analyze or report on in Excel are adjacent, utilizing Excel tables simplifies the process. All you need to do is use the colon (:) operator to define the start and end of the column selection range:

We have the opportunity to apply various manipulations to this selection, including utilizing the FILTER() function for refinement:

When it comes to selecting nonconsecutive columns from an Excel table, the process becomes a bit more complex. Despite the utility of structured references, they fall short in this particular scenario.

However, we can devise our own solution by leveraging a few Excel functions. This approach allows for a customized method to achieve the desired outcome, circumventing the limitations of structured references.

Choosing columns by position with CHOOSECOLS()

The initial step in implementing this workaround involves utilizing the CHOOSECOLS() function. This function is designed to accept an array along with a specified set of column numbers. Once these inputs are provided, Excel will proceed to return the outcomes corresponding to those specified columns.

Choosing columns by index number with MATCH()

While manually selecting columns by index number is technically feasible, enhancing user-friendliness involves specifying the desired columns in a list. This approach allows the utilization of the MATCH() function to ascertain the index number of each specified column, thereby facilitating the retrieval of corresponding data:

We can now even combine this with FILTER() to get selected rows and columns:

Unfortunately, it’s disappointing that we cannot utilize structured references for noncontiguous columns, which would have offered a more flexible solution. However, the combination of functions provided does serve as an adequate alternative for most cases.

Do you have any questions about handling nonconsecutive columns in an Excel table? Feel free to share them in the comments below.