How To Create A Virtual Helper Column In Google Sheets - InfoInspired

In this tutorial, I will explain the concept of a virtual helper column in Google Sheets and discuss its pros and cons.

Let’s see how to create a virtual helper column in Google Sheets. But first, it’s important to understand what a helper column is. So, here we go.

What Is a Helper Column in Google Sheets?

We can describe it as follows:

A helper column is an additional column in a dataset used to simplify formulas. In this column, you can perform intermediate calculations or manipulations to support the original formula. However, there are times when a physical helper column is required rather than a virtual one. The first example below illustrates this.

1. With Sorting

In this example, column C is a helper column that contains sequence numbers. It’s not part of the dataset in A1:B.

What is the use of this helper column, then?

Assume you sorted the data in A1:B by the names in column A in ascending order. In the future, if you want to return the data in A1:B to its original order, you can sort it by column C in ascending order.

We can’t turn column C into a virtual helper column since the values have no direct relation to the original data in A1:B. The original data may change, but the values in the helper column will remain the same.

2. With VLOOKUP

In this example, column A is a helper column I’ve used in the VLOOKUP formula to find the “Position” of “Mack Jordan.”

=VLOOKUP("Mack Jordan", A2:D8, 4, 0)

Since our original data in B2:D8 contains the names First Name and Last Name separately in B2:B8 and C2:C8, we can’t find the “Position” of “Mack Jordan” using VLOOKUP as usual. This is because VLOOKUP typically looks for the match in the first column (column B) only.

So, I have combined the first and last names using the following array formula in cell A2 and used A2:D8 as the range instead of B2:D8.

=ArrayFormula(B2:B8&" "&C2:C8)

I’ll use this data to help you understand the concept of the virtual helper column.

I hope you now understand the helper column concept. Now, let’s see how to create a virtual helper column in Google Sheets.

What Is a Virtual Helper Column in Google Sheets?

A virtual helper column in Google Sheets is a concept used to perform intermediate calculations or manipulations without actually adding a physical column to the sheet. Instead, it often involves leveraging Google Sheets’ built-in functions to simulate the effect of having an additional column for calculations.

In short, a virtual helper column is an array formula that replaces a physical helper column.

See the above example 2, where I’ve used column A (A2:A8) as a physical helper column.

If you do not want a physical helper column as in the above example, you can use it as follows:

The original range is B2:D8 and the range with the helper column is A2:D8.

The range with the virtual helper column will be {ArrayFormula(B2:B8&" "&C2:C8), B2:D8}.

So, the VLOOKUP with the virtual helper column will be:

=VLOOKUP("Mack Jordan", {ArrayFormula(B2:B8&" "&C2:C8), B2:D8}, 4, 0)

In the above example, we used curly braces to create a new array that combines the first and last names as the first column and includes the original data in the remaining columns.

The use of a virtual helper column depends on the specific situation. It may not be necessary to combine it with the original data as we did above.

Pros and Cons of Using a Virtual Helper Column

Physical Helper Column:

• Pros: A helper column can make formulas easier to read.
• Cons: You may need to hide the helper columns when printing data.

Virtual Helper Column:

• Pros: It avoids possible data clutter.
• Cons: A virtual helper column might make the formula more complex.

Do we require a helper or virtual helper column in formulas in Google Sheets?

If you are not an expert and are not very familiar with array formulas, using a physical helper column is the best option, as a virtual helper column requires knowledge of array formulas.

Resources

• Dynamic Ranges in Google Sheets Without Helper Cell