|
Google Sheets
| #174
Google Sheets: Tidy Up Messy Data in Seconds
(you can easily filter previous issues 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()
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?
- You compile user feedback after running a successful event but the formatting is a bit messy (thanks to a "special" colleague):
- 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:
- So you end up using a combination of tocol, filter, and flatten formulas to clean up the data like this:
Try it out yourself
- Make a copy of my Google Sheet and play around with these formulas yourself!
=TOCOL(FILTER(FLATTEN(B3:G12), FLATTEN(B3:G12) <> ""))
Was this forwarded to you? Join Workspace Essentials for free 😉
Want to see more (or less) of this? Let me know your feedback here!
When you’re ready, here are all the ways how I can help.