You might have had a situation where you copied and pasted stuff into excel and it dumped it into a rows (stacked one below the other). And you wonder, how can I get this list of values in a single row?

Yeah.. I know it’s trivial but most often these trivial things take more time than actual work. So 2 quick workarounds

 

Here’s a list..

And here is how you can get them in a single row..

  1. Write a Simple IF, concatenated with the current row’s cell address
  2. Drag the formula down
  3. Use Copy and Paste Special > Values for last formula and you have them all in a single row!

 

I use this formula technique all the time but I do have a smart alternative for it i.e. the Justify Command in Excel (a must read). The cool thing is that it works both ways

  • It can convert a list of values in a single row and
  • Can convert a row into a list of values

 

How would you have solved this trivial thing?

Please let me know in the comments.

 

A couple of more cool tricks

  1. 4 Simple tricks in Excel to save time
  2. Auto Sum formula Tricks
  3. 16 Awesome Pivot Table Tricks
  4. Pivot Table Formatting Tricks
  5. 5 Common Excel Problems and Fixes

 

 



Topics that I write about...






Download Smart Ebooks on
Excel and Power BI