Hello everyone, today we will learn how to turn Wide data into Tall Data in Google Sheets. Values in a tall data format repeat in a column. Tall data is primarily helpful when a pivot table needs to be made. Tall data is a format that works considerably better with pivot tables. We will combine three methods – the ARRAYFORMULA, FLATTEN, and SPLIT functions of Google Sheets – to transform wide data into tall data in the program. Although it appears difficult, you can easily accomplish it by following this tutorial on how to turn Wide data into Tall Data in Google Sheets
Advantages of Turning Wide Data into Tall Data?
The primary motivation behind learning how to turn wide data into tall data in Google Sheets is time efficiency. There is a direct method of using formulas to convert wide data into tall data in Google Sheets if you have a wide data set that you are turning into tall data. If you write it manually again, it could take a lot of time and effort. The primary problem is that the data cannot be used in pivot tables for analysis because it is captured in the column headings. As a result, we have presented to you the method that is explained below for converting wide data into tall data.
You may also like>>> Google Sheets Formulas Tips & Techniques You Have To Be Aware
How to Turn Wide Data into Tall Data in Google Sheets – Step-by-step Procedure
It is not as difficult as it seems to convert wide data into tall data in Google Sheets; nonetheless, you must comprehend the syntax’s reasoning to implement this simple process. Let us clarify through the detailed guide provided below.
Step 1>
In the example below, we have a sample of wide data. Let’s see if we can use Google Sheets to transform it into tall data.
Step 2>
The first step is to choose the location for this tall data and execute the “ARRAYFORMULA” in the cell by entering the Arrayformula as indicated below, with an equal sign.
Step 3>
Once the function has been started, enter the data range of the first row for which you wish to create a column initially. In this case, I want to create a tall first data in C3:H3, as indicated below.
Step 4>
To make it taller, we will provide the second data range in the same way to the first. However, before providing the second data range, put any symbol, sticker, or emoji between the ranges with quote marks, and affix the “&” sign along the ranges to help in subsequent string splitting.
Step 5>
You can write the second data range that you want to place at second in tall data once you have typed the symbol for string separation.
Step 6>
Using quote marks and the “&” sign, write the third data range you wish to make tall in the same way as seen in the pattern below. Continue this process for further ranges.
Step 7>
Since there were just three criteria, in this case, our syntax was finished. As soon as we hit the Enter key, all of the data was sorted into the array that was defined, as shown in the figure below.
Step 8>
We will now use Google Sheets’ FLATTEN function to make the data taller. As indicated below, add the flatten function with a tiny bracket right after the ARRAYFORMULA function.
Remember to close the little bracket in syntax when you open it.
Step 9>
Your wide data will become tall data due to putting Google Sheets’ FLATTEN function into the syntax and hitting the Enter key, as shown below.
Step 10>
Although it is in a single column, the data has been transformed into tall data. Here, we will split this data into separate columns using Google Sheets’ SPLIT function.
The SPLIT function will be introduced to the syntax after the ARRAYFORMULA function, as instructed below. The flatten function was added to the syntax earlier.
Step 11>
Write the same symbol at the end of the syntax that I have written below, just as we did when we first inserted it between the ranges.
It will assist the function in determining the string’s point of separation.
Complete Formula: =ARRAYFORMULA(SPLIT(FLATTEN(C3:H3&”*”&B4:B6&”*”&C4:H6),”*”))
Step 12>
Now that you’re almost done, hit the Enter key to produce what you want. As you can see below, all of the wide data has been converted to tall data.
You may also like>>> Complex Numbers In Google Sheets – Complete Guide
Turn Wide Data into Tall Data in Google Sheets – FAQs
How to border the tall data into Google Sheets?
In Google Sheets, the formatting of cells is not retained when wide data is converted to tall data. For example, you must take the following actions to border cells or data in Google Sheets if you wish to create a table with the data.
Step 1>
If you have created a tall dataset and would like to make it a border, select all the data as I have done in the example below.
Step 2>
After you select all of the data you wish to border, navigate to the Google Sheets toolbar. There, you will see an icon for the bordering cells, as seen in the image below.
Step 3>
Upon selecting this border tool icon, a little drop box will appear displaying various cell border styles. Feel free to choose the style that best suits your taste.
Step 4>
All of the selected cells will be formatted as a specified border when you choose the border style, as the result below shows.
How to insert a pivot table in Google Sheets?
PivotTables are designed to provide unexpected answers regarding your data and to analyze numerical data in great detail. Pivot tables are the most effective way to summarize and arrange data, particularly when working with large amounts of data. When your spreadsheet starts to expand, pivot tables could be the best option if it needs a little more strength. The steps to create a pivot table in Google Sheets are listed below if you are unfamiliar with the process.
Step 1>
You must navigate to the “Insert” tab of Google Sheets’ menu bar, as indicated in the following image, in order to insert a pivot table.
Step 2>
A drop-down menu will appear when you select the “Insert” tab from Google Sheets’ menu bar. This will reveal the “Pivot table” option, which makes it simple to add a pivot table to Google Sheets.
Step 3>
A little pop-up window asking for the range in which you wish to build a pivot table in Google Sheets will show up when you select the “Pivot table” option from the drop-down menu.
Step 4>
Simply click the “Create” button as seen in the below image after entering the data range. The “New sheet” and “Existing sheet” options can also be used to construct a pivot table from this pop-up.
Step 5>
You will receive a pivot table in the specified range like the result below when you click the “Create” button.
If your data collection is large, you can additionally sort the pivot table.
Conclusion
You can quickly transform even a sizable amount of wide data into tall data in Google Sheets by using the above-described method to unpivot data or convert wide data into tall data. I hope it will assist you in completing your data analysis responsibilities. Keep learning with Office Chaser.