Here is yet another Google Spread Sheet formula exclusively for Info Inspired readers. Here you can master how to properly stack data in Google Sheets. It’s simple and dynamic.

You can tweak the formula that you are going to get in this tutorial to simply fit any number of rows or columns in stacking in Google Spread Sheets.

Here is what I meant by saying proper stacking of data in Google Sheets.

It’s not combining multiple columns of data into one column. It’s all about stacking group of data into the proper group in the stack.

See this screenshot.

Here Column A to Column H contains some type of data. Actually, you can call it an unstacked data as it’s well organised under different columns.

The data under each column have some kind of relationship with its titles.

When we come back to my stacked data in column A and B that starts from row # 8, you can see one important thing. It preserves its grouping. So what is the benefit of such stacking of data in Google Sheets?

By doing so, I mean preserving the grouping, if you wish you can again use it to gather relevant information by applying filter, query, lookup or any other relevant functions.

So you can understand one thing. Stacking is not something like combining or transposing different columns into one single column. It’s arranging multiple columns into two columns and that also with preserving the data structure.

That is why I said properly stack data in Googles sheets instead of simply saying stacking of data. Here is the steps to create a formula that stacks data in Google Sheets.

How to Properly Stack Data in Google Sheets

I forgot to say one important thing. The formula that I am going to introduce you to stack data in Google Sheets will work well with text columns.

If any of the column has number, time or date value, this formula will stack such data too but will return the values unformatted.

I’ve addressed this issue in the troubleshooting section at the end of this tutorial. Having said that I’m beginning the formula part.

You can learn here how to develop the formula step by step. If I directly provide you with the main formula to stack data in Google Sheets, you may not be able to understand the functioning. Also by learning the steps, you can improve your Google Sheets usage skills.

Step 1:

=ArrayFormula(A1:H1&","&A2:H5)

This formula is to just prefix the header row contents and an additional comma to each and every column values as below.

I’ve used the Ampersand, which is equal to the use of the Concatenate function, to joint the text and Comma.

As you may know, the Array Formula is mandatory as we are dealing with an array, not a single cell.

Can I use infinitive ranges like A2: H instead of A2: H5?

Yes, you can use but with one limitation. Below I am going to use another function called TEXTJOIN that has some issue with infinitive ranges.

In my test that based on the above data, I could find it works well up to 600 rows. If there are more than 600 rows the formula would return #VALUE! error saying;

Text result of TEXTJOIN is longer than the limit of 50000 characters.

So I recommend you to use the range up to the rows with content in your sheet. This’s a limitation.

If you have more than 600 rows or if you find the above error, you can consider stacking of data part by part and combine them using the Curly Brackets later.

Step 2:

=ArrayFormula(TRANSPOSE((A1:H1&","&A2:H5)))

Here I’ve just transposed, changing the orientation of the above data. So I think no need to go into much details about this formula. So let me take you to the next step of stacking of data.

Step 3:

=ArrayFormula(textjoin("-",TRUE,TRANSPOSE((A1:H1&","&A2:H5))))

Here I’ve used the above said TEXTJOIN formula. The purpose of this formula is to join the above columns of data and placing “-” mark in between.

This mark will serve as a point to split the joined text later. Yes! our above data is now a text looking like a sentence. Just see that.

Please keep in mind about the 50,000 character limitation with the Textjoin formula.

Step 4:

=ArrayFormula(TRANSPOSE(SPLIT(textjoin("-",TRUE,TRANSPOSE((A1:H1&","&A2:H5))),"-")))

Now the use of SPLIT function and Transpose make the above joined text as below. That means we are one step closer to our target on learning how to Stack Data in Google Sheets.

Please note that I’ve only taken the screenshot of the first few lines. The reason is the number of screenshots with this post already. I don’t want to compromise on the page load speed due to this.

I think this is enough to understand the output of the above formula.

Now what we want to do is to just split this data based on the comma as the delimiter. See how to do that in the next step.

Step 5:

=ArrayFormula(SPLIT(TRANSPOSE(SPLIT(textjoin("-",TRUE,TRANSPOSE((A1:H1&","&A2:H5))),"-")),","))

This formula is my answer to how to properly stack data in Google Sheets. But to avoid any future conflict like blank cell contents, we can use a Query to put an extra filter.

In certain cases, you may want to use the formula above without Query. I’ll explain that in the trouble shooting section at the end. For the time being, I’m not calling the above Step 5 formula as our final formula to stack data in Google Sheets.

Final Formula to Stack Data in Google Sheets:

=QUERY(ArrayFormula(SPLIT(TRANSPOSE(SPLIT(textjoin("-",TRUE,TRANSPOSE((A1:H1&","&A2:H5))),"-")),",")),"Select * where Col2<>''")

In our stacked data there are two columns. This Query can filter out any blank cells in Column 2 as Column 1 is the header row stacked to the column. So there is no point of blank cells in column 1.

You have learned in this step by step tutorial how to stack data in multiple columns in to two columns.

At the beginning of this tutorial, I’ve told you, numbers, dates and time values can cause formatting issues in stacking. Let me look in to that in the troubleshooting section below.

Troubleshooting of Errors in Stacking of Data

When you have mixed content type as below, remove the Query formula and use the formula that I’ve provided you under Step 5 above.

Example:

Numbers and date can cause problems in your stacking of data. Here I’ve used my formula under step 5 that without the Query because my data in this example is a mixed type.

The formula retains the numbers and date in the stacked data too. But you have to manually format the date (I’ve marked in the screenshot) from the format Menu > Number > Date as it would be in the date value.

Now I know obviously you will ask me what is the use of Query then?

As you can see on the screenshot, Cell C4 has no value. That causes an extra line in the stacked data which is not required.

The purpose of Query is to filter out such rows. But it can be troublesome, if you are stacking mixed type of data.

So it’s up to you whether to use Query or not. I suggest you to use Query, if you are stacking pure text.

I’ve more option to tame the Query. But I don’t want to drag you in to more complexity. That’s all about how to properly stack data in Google Sheets.

Note: Access my Google Sheets Here that contain the formula for stacking and unstacking of data in Google Doc Sheets.