Tamara (my wife) called me into her office yesterday to share this tip she just learned, and it’s pretty cool. Have you ever had multiple columns in Excel that you wanted to combine into a single column without copying and pasting? You can do that with the ToCol (To Column) function. Here’s how that works…
In this example, I have two columns of names, along with a few blank cells mixed in. I’d like to get all of those to show up in a single combined column without the blank cells…
I put my cursor in cell C1 and in the function bar I typed =ToCol(. That provided me the type-ahead format of the function:
To get the array value, I highlighted the content in the two columns. That automatically filled in A1:B5 for the array value:
Next, I added a comma to the function to get the ignore values. By using the value of 1, the function ignores all the blank cells:
I then added the closing parenthesis and pressed Enter:
Column C now has the combined list of all the names found in columns A and B!
One Minute Office Magic 