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

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.