Google Sheets: Tidy Up Messy Data in Seconds

Jeff -

(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?

  1. You compile user feedback after running a successful event but the formatting is a bit messy (thanks to a "special" colleague):
  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:
  1. 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.