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.
