How can a table be converted to a list in Google Sheets?

By /

To convert a table to a list in Google Sheets, you can first select the table and then go to the Data tab and click on “Create a filter.” This will add filter options to each column in the table. Then, you can click on the arrow next to the column header and select “Sort A-Z” to sort the data in alphabetical order. Finally, you can copy and paste the sorted data into a new sheet, and it will be converted into a list format.

You can use the FLATTEN function in Google Sheets to convert a table of data into a vertical list.

For example, you can use the following formula to convert a table with three rows into a vertical list:

=FLATTEN(B2:E4)

The following example shows how to use this formula in practice.

Example: Convert a Table to List in Google Sheets

Suppose we have the following dataset in Google Sheets that displays the total sales made by some company during each quarter of three consecutive years:

Suppose we would like to convert this table into a vertical list.

We can type the following formula into cell A6 to do so:

=FLATTEN(B2:E4)

The following screenshot shows how to use this formula in practice:

Google Sheets convert table to list

We can see that the formula was able to successfully convert the table of values into a single list that shows the sales values for each quarter and year.

For example:

  • The first value in the list shows the sales for Q1 2021.
  • The second value in the list shows the sales for Q2 2021.
  • The third value in the list shows the sales for Q3 2021.
  • The fourth value in the list shows the sales for Q4 2021.

And so on.

Note: You can find the complete documentation for the FLATTEN function in Google Sheets .

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

x

Related terms:

  1. How to Create Pivot Table from Multiple Sheets in Google Sheets?
  2. How can I fix the error “Only size-1 arrays can be converted to Python scalars”?
  3. Count Specific Characters in Google Sheets How to count specific characters in google sheets?
  4. Google Sheets: Highlight Cell if Value Exists in List
  5. Google Sheets: Create a List Based on Criteria
  6. How to Check if Cell Contains Text from List in Google Sheets
  7. How to Filter by List of Values in Google Sheets?
  8. How to Select a Random Value From a List in Google Sheets
  9. How to Randomize a List in Google Sheets (With Examples)
  10. How do I calculate the median in a pivot table using Google Sheets?