> ## Content Index
> Fetch the complete content index at: https://www.jeffsu.org/llms.txt
> Use this file to discover other available public pages before exploring further.

# Google Sheets: Tidy Up Messy Data in Seconds
- URL: https://www.jeffsu.org/newsletter-174/
- Published: 2024-08-15T13:00:57.000Z
- Updated: 2024-08-15T13:00:56.000Z
- Description: #174
- Author: Jeff
- Tags: #newsletter, Google Sheets

*(you can easily* [*filter previous issues*](https://www.jeffsu.org/filter-previous-editions-by-app/) *by application!)*

---

Hey friends - I recently came across two formulas in Google Sheets I don’t know how I survived so long without using:

- \=TOROW()
- \=TOCOL()

![](https://storage.ghost.io/c/db/85/db858a9d-1c4f-4993-8042-faa87b9bb3c1/content/images/2024/08/CleanShot-2024-08-15-at-09.31.00.gif)

## What is it?

Put simply, both functions are able to transform an array of cells (i.e. you can select as many cells as you want) into a **single row** (=torow) or a **single column** (=tocol).

## When should I use this?

1. You compile user feedback after running a successful event but the formatting is a bit messy (thanks to a "special" colleague):

![](https://storage.ghost.io/c/db/85/db858a9d-1c4f-4993-8042-faa87b9bb3c1/content/images/2024/08/CleanShot-2024-08-15-at-09.34.43@2x.png)

1. You try using the **\=tocol** function to arrange all the feedback in a single column but you come across a new problem—All the empty cells are inserted as well:

![](https://storage.ghost.io/c/db/85/db858a9d-1c4f-4993-8042-faa87b9bb3c1/content/images/2024/08/CleanShot-2024-08-15-at-09.41.38@2x-1.png)

1. So you end up using a combination of **tocol, filter, and flatten** formulas to clean up the data like this:

![](https://storage.ghost.io/c/db/85/db858a9d-1c4f-4993-8042-faa87b9bb3c1/content/images/2024/08/CleanShot-2024-08-15-at-09.42.53.gif)

## Try it out yourself

- [Make a copy](https://docs.google.com/spreadsheets/d/1T75vKVPhRuhIPBjVd0eX1L3RaLwbPxac9hSC7zJipFU/copy?ref=jeffsu.org) of my Google Sheet and play around with these formulas yourself!

```html
=TOCOL(FILTER(FLATTEN(B3:G12), FLATTEN(B3:G12) <> ""))

```

---

**Was this forwarded to you?** [Join Workspace Essentials for free](https://www.jeffsu.org/newsletter/?utm%5Fsource=newsletter&utm%5Fmedium=email&utm%5Fcampaign=footer) 😉

**Want to see more (or less) of this?** Let me know [your feedback here](https://forms.gle/X5K1qRhojVbqsUm48?ref=jeffsu.org)!

**When you’re ready,** [here are all the ways how I can help](https://academy.jeffsu.org/?utm%5Fsource=newsletter&utm%5Fmedium=email&utm%5Fcampaign=footer).