Transpose + Concatenate (or Combine) Range of Cells in Excel

Transpose + Concatenate (or Combine) Range of Cells in Excel

Help with Excel

Video blurry? Bump up the quality to HD to make it clear.  To do that, click on the cog in the bottom, right corner of the video. By ‘Quality’ adjust the setting up to 720 HD.

When it gets to the point that you find yourself working in excel on a daily basis, it’ time to start learning some shortcuts to help you become more efficient and better at your day-to-day tasks.  One of the most common formulas I use in excel is the transpose and concatenate formula.  This is a super easy way to combine a range of cell data without having to do any VBAs or advanced formulas.

Combined, these formulas allow you to easily combine a range of cells without having to individually type them in with just the concatenate function alone.  There are various reasons you may want to combine spreadsheet data, but as a digital marketer, I find myself using this formula to combine keywords into one cell before sending it over to a client.

Transpose, Concatenate Formula – No Commas or Spaces

  1. You’ll need a range of cells with your data listed in them. In this example, we’ll say your data is in A2:A75.
  2. Select the cell where you want the data combine, let’s say B2.
  3. In B2, type in =TRANSPOSE(A2:A75). You can quickly select the range of cells by clicking in A2, and then clicking CTRL+SHIFT+↓. Do not click enter.
  4. Before clicking enter, click the F9 button.
  5. At the beginning of the formula, delete TRANSPOSE{ and replace it with CONCATENATE(. Go to the end of the formula, and replace the } with ). Your formula should look like this now: =CONCATENATE(“A2″,”A3″,”A4″,”A5″,”A6″,”A7”, …).
  6. Click enter.

All of your data should now be combined into one cell now. At this time, you can CTRL+C to copy and right-click paste ‘Values Only’ in a new cell so that only the content is in the cell and the formula is gone.  If you want spaces and commas, use the next formula instead.

Transpose, Concatenate Formula – With Commas and Spaces

  1. You’ll need a range of cells with your data listed in them. In this example, we’ll say your data is in A2:A75.
  2. Select the cell where you want the data combine, let’s say B2.
  3. In B2, type in =TRANSPOSE(A2:A75)&”, ” (remember the space after the comma). You can quickly select the range of cells by clicking in A2, and then clicking CTRL+SHIFT+↓. Do not click enter.
  4. Before clicking enter, click the F9 button.
  5. At the beginning of the formula, delete TRANSPOSE{ and replace it with CONCATENATE(. Go to the end of the formula, and replace the } with ). Your formula should look like this now: =CONCATENATE(“A2″,”A3″,”A4″,”A5″,”A6″,”A7”, …).
  6. Click enter.

All of your data should now be combined into one cell now, and have the appropriate commas and spaces. At this time, you can CTRL+C to copy and right-click paste ‘Values Only’ in a new cell so that only the content is in the cell and the formula is gone.

 

 

About Kasy Allen

Kasy brings years of experience in search engine optimization (SEO), content strategy, Internet marketing, and overall web-geekery to the table. She enjoys writing on the web and improving user experience across the Annapurna site, as well as with our clients. When Kasy is out of the office, she can often be found volunteering her time to help non-profit organizations build a better online presence and exploring the great outdoors with her family.

One Comment

  • John Smith says:

    Just a heads up, This works when the listing is in a column (A1:A75) when you use a listing that is from left to right (A1:S1) it generates a semi colon where the comma is supposed to be (; instead of ,) using the same steps, you could put a ” in the beginning of the formula to be able to get out of it, then select the cell and do a search for the the semi-colon ; and replace all with a comma. After that just remove the ” at the beginning of the formula and change transpose to concatenate and replace brackets with parenthesis. Ultimately getting the same result, but from a list that is written from left to write.

Leave a Reply

Ready to discuss your project? Request a Quote