Google Sheets Query Hidden Row Handling with Virtual Helper Column

0
101
Google Sheets Query Hidden Row Handling

I am sure that our topic discussing about Google Sheets Query hidden row handling is quite new to you. Yup! Using Google Sheets Query function, you can select only visible rows in Google Sheets. That means when you apply Query function to an already filtered data or data contain hidden rows, our Query formula will only pull the visible rows. It’s not possible in the normal case.

Here with this tutorial I am going to unveil that powerful feature of Query. For this purpose we are going to use a virtual helper column, a new concept. I will tell you what’s this new concept. I know it’s also new to you!

Hidden rows are a nightmare for many spreadsheet users as it invite unwanted results. Lots of powerful functions like SUMIF, QUERY, SUMPRODUCT fell short to hidden or filtered out rows. Only possible way to deal with hidden rows are the SUBTOTAL function and associated function numbers. But Subtotal function has limitation as it doesn’t accept conditions.

Similar: SUMIF Can Exclude Hidden Rows in Google Sheets

With our virtual helper column concept, you can overcome some of the hidden rows related dilemma. So here we go.

Google Sheets Query Hidden Row Handling and Virtual Helper Column

First thing first. Here is the sample data.

Query with Visible Rows - Sample Data

Normally, as you might already aware, it is not possible to select only visible rows using Query. But with our virtual helper column concept, we can do it. So here is the step to create that virtual helper column in Google Sheets.

How to Create a Virtual Helper Column for Query to Select Only Visible Rows

Step 1:

See the above sample data. Add a new column at the end of the data which is called helper column. Of course we will delete this helper column later as we are going to create a virtual helper column.

only extract visible rows using query - create virtual helper column

Refer the above SUBTOTAL formula in cell E2. Apply in your sample data as it is and then copy and paste it up to the cell E7. See the above image.

When you use this method, you can choose any numeric field other than column E. But you should make sure one thing. The values in all the cells in that column should be greater than zero. If you change the column, accordingly our final formula will also change. So stick with this column until you learn this new method.

Step 2:

Go to the menu View > Show formula.

Then remove all the “=” sign prefix to the subtotal and it should look like as below.

generate virtual helper column in Query

Again go to the View menu and click on Show Formula to remove the selection.

Step 3:

Now in any blank cell out side of the above data range, apply the below formula that JOIN all the string in Column E together.

=join(“;”,E2:E7)

It returns the combined texts in Column E. Just copy the formula result, right click on the cell and then apply paste value.

paste value in Google Sheets with Query

Add Curly Brackets at the beginning and end of the string as below.

{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}

Step 4:

Now you can delete the helper Column E. Now we have a long text to use as virtual helper column for Query.

Join Virtual Helper Column with Query

This is the final part of this tutorial related to Google Sheets Query Hidden Row. I’m applying a simple Query formula to select only visible rows from our sample data above. The visible rows can be as a result of either applying filtering or manually hiding rows in our sample data.

First see the normal QUERY formula which will pull all the data from the source including the hidden rows.

=query(A2:D7,”Select *”)

Now see our special QUERY formula with the virtual helper column.

=query({A2:D7,{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}},”select * where Col5>0″)

The blue coloured part is the difference. What is that difference?

I’ve combined our virtual helper column with the Google Sheets normal Query formula. Also we added a where clause condition to return the rows when the value in Col5 >0. We have actually 4 Column in our sample data. Column 5 is our virtual helper column.

Here is a more polished version of the above formula as the above formula would repeat column D or Column 4 twice.

=query({A2:D7,{subtotal(109,D2);subtotal(109,D3);subtotal(109,D4);subtotal(109,D5);subtotal(109,D6);subtotal(109,D7)}},“select Col1,Col2,Col3,Col4 where Col5>0”)

What is the Logic?

Column 5 is our Virtual helper column. When we hide any rows, the value in the virtual helper column corresponding to that row become zero. So it will be automatically excluded from the Query output due to our where clause in Query.

Conclusion

Here is the end of another awesome Google Sheets tutorial. Hope you have enjoyed this tutorial and learned the awesome Google Sheets Query Hidden Row feature and Virtual Helper Column. See you again with more interesting spreadsheet tutorials.

LEAVE A REPLY

Please enter your comment!
Please enter your name here