How to Insert Blank Column Using QUERY in Google Sheets

Often we need to insert one or more blank columns between columns in order to input data that we forgot to insert previously. So it is necessary to know methods to solve this issue. In this article, we have illustrated the ways to insert blank column using the QUERY function in Google Sheets. The final output may look as follows.

Table of Contents

A Sample of Practice Spreadsheet

You can download the spreadsheet used to describe methods in this article from here.

Inserting Blank Column Using QUERY Function

3 Easy Ways to Insert Blank Column Using QUERY Function in Google Sheets

We will be using the following dataset as an example to describe methods in this article. The dataset represents some students’ grades in Mathematics.

1. Using Only QUERY Function

The QUERY Function in Google Sheets is a built-in function that gathers data based on criteria and, if needed, modifies the formatting, adds additional calculations, rearranges the columns and much more. You can add single or multiple blank columns using this function easily.

1.1 Inserting a Single Blank Column

Suppose, we want to add a single blank column within the data of Column B and Column C and then make a new dataset gathering them all.

Steps:

In the following dataset, select Cell E4 then apply the following formula below and press Enter–

=QUERY(B4:C16, "SELECT B, ' ', C LABEL ' ' ''")

Have a look, the QUERY function created a new dataset where there is a new blank column between the “Students’ Name” column and “Maths” column including the name of those headers as well.

1.2 Inserting Multiple Blank Columns

Assume, this time you want to put 2 blank columns instead of one. QUERY function is an expert for that as well.

Steps:

Activate Cell E4, insert the following formula and press Enter–

=QUERY(B4:C16, "SELECT B, ' ', ' ', C LABEL ' ' '', ' ' ''")

Watch out, there are 2 blank columns between the two columns containing data.

2. Joining QUERY, ARRAYFORMULA and IFERROR Functions

In Google Sheets, you can write only one formula and then the ARRAYFORMULA function will help you to apply it to each cell in the predefined data range. When an error is present, the IFERROR function in Google Sheets produces a blank cell or the specified value instead of returning the first parameter, as is the case when there are no errors in the data range. Combination of QUERY, ARRAYFORMULA and IFERROR helps to create single or multiple blank columns. Suppose, in the following dataset, we want to create a new dataset, where there will be two blank columns between the “Students’ Name” column and “Maths” column.

Steps:

Choose Cell E4, apply the formula below and then press Enter–

=QUERY(ARRAYFORMULA(IFERROR({B4:B16,{" "," "}/ROW(B4:B16),C4:C16})))

Formula Breakdown

IFERROR({B4:B16,{” “,” “}/ROW(B4:B16),C4:C16})

The IFERROR function will check for errors, if not found then will return the original value.

ARRAYFORMULA(IFERROR({B4:B16,{” “,” “}/ROW(B4:B16),C4:C16}))

Later, the ARRAYFORMULA function will return the output as an array.

QUERY(ARRAYFORMULA(IFERROR({B4:B16,{” “,” “}/ROW(B4:B16),C4:C16})))

Finally, the QUERY function will insert two blank columns into the output.

Similar Readings

3. Combining INDEX, SUBSTITUTE and QUERY Functions

Based on the row, column, or range that you specify, the INDEX function collects data from particular cells or cell ranges and then returns the data to the intersection of the defined range. The SUBSTITUTE function can replace one text with another even with delimiters like space, comma or full stops also. To add one or more blank columns within a range of data, we can use INDEX, SUBSTITUTE and QUERY functions together. Presume, in the below spreadsheet, we want to put a blank column between the two columns that contains data.

Steps:

Select Cell E4, type down or copy and paste the following formula below and after that press Enter–

=INDEX(SUBSTITUTE(QUERY({B4:C16},"Select Col1, '#', Col2"),"#",""))

Take a look, there is a new column now, but what is exceptional here, there is something like “”() at the header of the new column because of this specific formula. As it is just the header, so it may not come up with any issues.

Formula Breakdown

QUERY({B4:C16},”Select Col1, ‘#’, Col2″)

First, the QUERY here will return the whole Cell range B4:C16 with a new column in which all the cells will contain “#” as an output.

SUBSTITUTE(QUERY({B4:C16},”Select Col1, ‘#’, Col2″),”#”,””)

Next, the SUBSTITUTE function will substitute all the “#” with blank values.

INDEX(SUBSTITUTE(QUERY({B4:C16},”Select Col1, ‘#’, Col2″),”#”,””))

Finally, the whole function here will return output with a new column between the previous two columns that are Columns B and C.

Alternative to Insert Blank Column Using QUERY Function in Google Sheets

We can add blank columns in Google Sheets except using QUERY formula as well. Assume, in the following dataset, we want to insert a blank column just between Column B and C.

Steps:

First, select Column B then right-click on the mouse and select Insert 1 column right.

Or you can select Column C then right-click on the mouse and select Insert 1 column left.

Following this, a black column will be added between Column B and C

Conclusion

The article contains 3 easy methods to insert blank column using the QUERY function in Google Sheets. Hope this will help you with your task. Our site officewheel.com surely will help you to become more efficient and expert in using Google Sheets.

How to Insert Blank Column Using QUERY in Google Sheets

A Sample of Practice Spreadsheet

3 Easy Ways to Insert Blank Column Using QUERY Function in Google Sheets

1. Using Only QUERY Function

1.1 Inserting a Single Blank Column

1.2 Inserting Multiple Blank Columns

2. Joining QUERY, ARRAYFORMULA and IFERROR Functions

3. Combining INDEX, SUBSTITUTE and QUERY Functions

Alternative to Insert Blank Column Using QUERY Function in Google Sheets

Conclusion

Related Articles

Adrey

Leave a reply Cancel reply