Managing data in Excel can quickly become overwhelming, especially when you’re dealing with multiple columns or rows and need them in one list. If you’ve ever thought, “There has to be an easier way to merge all this into a single list,” you’re right—there is. Microsoft Excel offers two incredibly useful functions to make this process as simple as possible: TOCOL and TOROW.

In this tutorial, we’ll walk you through how to use these powerful functions to combine your Excel data efficiently, either from columns or rows, into one clean list.

What You’ll Need

Before we jump into the steps, here’s what you need on hand:

  • A list of data spread across multiple columns or rows (we’re working with employee names in our example).
  • A basic understanding of Excel formulas.

Now, let’s jump in!

Using TOCOL to Combine Data from Multiple Columns into One List

One of the most common scenarios when working in Excel is having important data spread across columns. Instead of manually copying or sorting, you can simply use the TOCOL function. Here’s how to do it:

Steps to Combine Columns into One List

  1. Select your starting cell: Click on the cell where you want the combined list to begin. In the example, this is G3.
  2. Enter the function: Type =TOCOL( to start the formula.
  3. Select your array: Define the range of columns you want to combine. Click and drag over the employee names in the multiple columns.
  4. Ignore blanks: After selecting the range, type ,1 so that the formula skips any blank cells.
  5. Close the formula and hit Enter: Complete the formula with a closing parenthesis and press Enter. All the employee names are now combined into a single list without any gaps.

Here’s the full formula:

=TOCOL(A1:F10, 1)

You’re done! You just turned multiple Excel columns into one tidy list.

RELATED TOPIC: How to Concatenate or Combine Data in Excel

Using TOROW to Combine Data from Multiple Rows into One Row

But what if your data is stretched out across multiple rows and you want them all lined up in a single row? The TOROW function is just as simple as TOCOL, and within seconds, you’ll have everything merged.

Steps to Combine Rows into One Row

  1. Choose the starting point: In our example, we want the list to start in B10. Click on that cell.
  2. Enter the formula: Type =TOROW( to launch the formula.
  3. Select your array: Click and drag over the rows that contain your data—the employee names in this case.
  4. Remove blank cells: Once you’ve selected the right range, add ,1 to ignore any blanks, just like you did with TOCOL.
  5. Close and press Enter: Close the formula with a parenthesis and press Enter. Just like magic, all the employee names now line up neatly in a single row.

Here’s what the formula looks like:

=TOROW(A1:F10, 1)

That’s it! Now all your data, which was scattered across multiple rows, appears in just one row.

Why These Functions Matter

These two functions can save you a massive amount of time, especially if you’re frequently merging data from several sources or working on projects with large datasets. Manually copying, sorting, or filtering data can lead to errors and wasted time—but using TOCOL and TOROW makes this task seamless.

Both functions also have an added bonus: they’re easy to remember and implement. So whether you’re working in Excel with a few rows or hundreds of columns and need them in one list, you can get the job done quickly.

RELATED TOPIC: How to Create a Pivot Table in Microsoft Excel

Wrapping Up

With TOCOL and TOROW, managing data in Excel just got a whole lot easier. Whether you’re looking to tidy up an employee list, clean up a project report, or organize any other kind of information, these functions allow you to combine your data and move on to other tasks faster.

So next time you have columns full of information scattered all over the place or rows that don’t seem to belong, just remember these simple formulas. With a few clicks and inputs, you’ll have your data cleaned up and ready to go.

If you found this guide helpful, make sure to subscribe for more Excel tips. Feel free to drop any questions or additional tips in the comments below. We’d love to hear how you’re using these tools in your own workflow!

Get Started with Microsoft 365

Be sure to subscribe to my YouTube Channel for more Microsoft Excel and Microsoft 365 Tutorials.

This page may contain affiliate links. These are tools I personally use and love. I may receive a small commission should you make a purchase using one of these links. This helps keep the tutorials and website posts coming and up to date. Thank you for your support! For more information please see my full disclaimer.

RELATED ARTICLESMORE FROM AUTHOR