How to Turn Wide Data into Tall Data in Google Sheets

Hi all, today we will learn how to turn Wide data into Tall Data in Google Sheets. A tall data format has values that repeat in a column. Tall data is mostly useful when you need to create a pivot table. Tall data is a much better format when dealing with pivot tables. To turn wide data into tall data in Google Sheets, we will use a combination of three different functions that include the ARRAYFORMULA, FLATTEN, and SPLIT functions of Google Sheets. It looks quite hard but with the help of a tutorial on how to turn wide data into tall data in Google Sheets, you can easily make it possible.

Benefits of Turning Wide Data into Tall Data?

The first reason to learn how to turn wide data into tall data in Google Sheets is time efficiency, if you have a wide data set that you are turning into tall data and if you write it manually again then it may cost a lot of effort and time too so there is a direct way of using formulas to turn wide data into tall data in Google Sheets. Moreover, the main reason is that data is captured in the column headings, which prevents you from using it in pivot tables for analysis. Therefore, we have brought to you a way of turning wide data into tall data that is described below.

Step-by-step Procedure – How to Turn Wide Data into Tall Data in Google Sheets?

The procedure of turning wide data into tall data can be a bit logical, it is not as complex as it looks but you need to understand the logic of the syntax through which you can easily turn wide data into tall data in Google Sheets. Let’s let you know with the help of the step-by-step guide below.

Step 1

Here we have a sample of wide data in the following example. Let’s see how we can turn it into tall data in Google Sheets.

Step 2

First, select the place where you want to place this tall data and run the “ARRAYFORMULA” in the cell by writing the Arrayformula with an equal sign as written below.

Step 3

After starting the function, provide the data range of the first row that you want to make a column at first, as here I want to make tall first data in C3:H3 as written below.

Step 4

After giving the first range, similarly, we will provide the second data range to make it tall but before giving the second data range place any symbol or sticker or emoji between the ranges with quotation marks and attach the “&” sign along the ranges that will help to split the string later.

Step 5

Once you have written the symbol for separation of string you can write the second data range that you want to place at second in tall data.

Step 6

In the same way, write the third data range that you want to make tall but divide by the same symbol with quotation marks and the “&” sign as written in the following pattern and so on for more ranges.

Step 7

Here we had only three criteria so our syntax has been completed, as we press the Enter key, all data will be sorted into an array of data as specified as can be seen in the following picture.

Step 8

Now, to make it tall data, we will use the FLATTEN function of Google Sheets. Add the flatten function just after the ARRAYFORMULA function with a small bracket as highlighted below.

Note: When you open a small bracket in a syntax don’t forget to close it.

Step 9

After inserting the FLATTEN function of Google Sheets into the syntax and pressing the Enter key to get the result your wide data will be turned into tall data as resultant below.

Step 10

Data has been converted into tall data, but it is in a single column. So here we will use the SPLIT function of Google Sheets to split this data into separate columns.

Same as above we added flatten function into the syntax, we will add the SPLIT function into the syntax after the ARRAYFORMULA function as directed below.

Step 11

As we placed a symbol between the ranges into the syntax previously, write the same symbol into the end of the syntax as I have written below.

It will help the function to identify the point of separation of the string.

Final Formula: =ARRAYFORMULA(SPLIT(FLATTEN(C3:H3&”*”&B4:B6&”*”&C4:H6),”*”))

Step 12

You are almost done now, as you press the Enter key, you should get the following result as we required as you can see below all the wide data has been turned into tall data.

How To Turn Wide Data into Tall Data in Google Sheets – FAQs

Q: How to border the tall data into Google Sheets?

A: When you turn wide data into tall data in Google Sheets, it doesn’t format cells. For example, if you want to border the cells to make a table data then you will have to follow the following steps to border cells or data in Google Sheets.

Step 1

If you have made a tall data and now want to make it all border first select all the data as I have selected in the following example.

Step 2

Once you have selected all the data that you want to border look into the toolbar of the Google Sheets where you will find an icon for the bordering cells as highlighted in the following picture.

Step 3

When you click on this border tool icon, a small drop box will drop down where you will see different kinds of cell border styles, select the border style according to your preference.

Step 4

As you select the border style, you will see that all selected cells have been formatted as a selected border as can be seen in the result below.

Q: How to insert a pivot table in Google Sheets?

A: PivotTables are made for analyzing numerical data in detail and answering unanticipated questions about your data. Especially when you are dealing with huge data pivot tables are the best choice to summarize and organize data. When your spreadsheet begins to grow it may need a bit more power where pivot tables are the right choice. If you are not aware of creating a pivot table in Google Sheets, then below are the steps to create a pivot table in Google Sheets.

Step 1

If you want to insert a pivot table in Google Sheets, you will have to go into the “Insert” tab of the menu bar of Google Sheets as highlighted in the following picture.

Step 2

When you click on the “Insert” tab from the menu bar of Google Sheets, a drop-down will drag down where you will find the “Pivot table” option through which you can easily insert a pivot table in Google Sheets.

Step 3

When you click on the “Pivot table” option from the drop-down menu, a small pop-up will appear in front of you that will ask you to provide the range in which you want to create a pivot table in Google Sheets.

Step 4

After providing the data range, just click on the “Create” button as highlighted in the following picture. You can also select the “New sheet” and “Existing sheet” options to create a pivot table from this pop-up.

Step 5

Clicking on the “Create” button will give you a pivot table in the given range as the result below.

You can also sort the pivot table if you have a big data set.

Conclusion

The above-described formula to unpivot data or to turn wide data into tall data in Google Sheets is very effective and useful through which you can turn even a huge wide data into tall data in Google Sheets in a few seconds. Hope it will help you to fulfill your tasks while analyzing data. Keep learning with OfficeDemy.