Adrey
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.
A Sample of Practice Spreadsheet
You can download the spreadsheet used to describe methods in this article from here.
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.
Read More: How to Insert Multiple Columns in Google Sheets (2 Quick Ways)
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.
Read More: How to Insert Formula in Google Sheets for Entire Column
Similar Readings
- How to Insert Signature in Google Sheets (3 Easy Ways)
- Insert Rows Between Other Rows in Google Sheets (4 Easy Ways)
- How to Insert a Calendar in Google Sheets (2 Effective Ways)
- Insert a Textbox in Google Sheets (An Easy Guide)
- How to Insert Multiple Rows in Google Sheets (4 Ways)
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.
Read More: How to Have More than 26 Columns in Google Sheets
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.
Related Articles
- How to Insert Superscript in Google Sheets (2 Simple Ways)
- Insert Button in Google Sheets (5 Quick Steps)
- How to Add Parentheses in Google Sheets (5 Ideal Scenarios)
- Insert Video in Google Sheets (2 Easy Ways)
- How to Insert Serial Numbers in Google Sheets (7 Easy Ways)
- Insert an Exponent in Google Sheets (3 Easy Ways)
- How to Insert a Legend in Google Sheets (With Easy Steps)
