How to Rearrange Data from Multiple Cells to One Row in Excel

Sometimes, you may need to rearrange values from several cells to a single row in Excel. For example, you may have a table with multiple rows of data and you want to convert it into a single row with all the values separated by commas or other delimiters. This can be useful for creating summaries, reports, or charts based on the data.

There are different ways to achieve this task in Excel, such as using the TRANSPOSE function, the TEXTJOIN function, the Power Query feature, or the ampersand sign. In this article, we will explain how to use each of these methods and provide a detailed example with real numbers.

Method 1: Using the TRANSPOSE function

The TRANSPOSE function in Excel can convert a range of cells from rows to columns or vice versa. To use this function, you need to follow these steps:

• Select a blank cell where you want to place the result.
• Enter the formula =TRANSPOSE(array), where array is the range of cells that you want to rearrange.
• Press Ctrl + Shift + Enter to enter the formula as an array formula. This will automatically fill the adjacent cells with the transposed values.

For example, suppose you have the following table in cells A1:C3:

To rearrange the values from this table to a single row in cells E1:O1, you can use the formula =TRANSPOSE(A1:C3) in cell E1 and press Ctrl + Shift + Enter. This will produce the following result:

Note that the TRANSPOSE function will preserve the order of the values from left to right and top to bottom. If you want to change the order, you can use the SORT or SORTBY functions to sort the array before transposing it.

Method 2: Using the TEXTJOIN function

The TEXTJOIN function in Excel can join multiple text strings or values from a range of cells into a single text string, separated by a specified delimiter. To use this function, you need to follow these steps:

• Select a blank cell where you want to place the result.
• Enter the formula =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...), where delimiter is the character or string that you want to use to separate the values, ignore_empty is a logical value that determines whether to ignore blank cells or not, and text1, [text2], ... are the text strings or ranges of cells that you want to join.
• Press Enter to complete the formula.

For example, suppose you have the same table as before in cells A1:C3. To rearrange the values from this table to a single cell in cell E1, separated by commas, you can use the formula =TEXTJOIN(",", TRUE, A1:C3) in cell E1 and press Enter. This will produce the following result:

Note that the TEXTJOIN function will ignore any blank cells in the range if you set the ignore_empty argument to TRUE. If you want to include the blank cells, you can set it to FALSE. Also, you can use any delimiter that you want, such as spaces, dashes, or semicolons.

Method 3: Using the Power Query feature

The Power Query feature in Excel can transform and manipulate data from various sources, such as tables, ranges, files, or web pages. To use this feature, you need to follow these steps:

• Select the range of cells that you want to rearrange.
• Go to the Data tab and click on From Table/Range in the Get & Transform Data group. This will open the Power Query Editor window.
• In the Power Query Editor, go to the Transform tab and click on Transpose in the Any Column group. This will convert the rows to columns or vice versa.
• Optionally, you can apply other transformations, such as sorting, filtering, or renaming the columns.
• Click on Close & Load in the Home tab to return the result to Excel.

For example, suppose you have the same table as before in cells A1:C3. To rearrange the values from this table to a single row in cells E1:O1, you can use the Power Query feature as follows:

• Select the range A1:C3 and go to the Data tab and click on From Table/Range.
• In the Power Query Editor, go to the Transform tab and click on Transpose. This will produce the following result:

• Click on Close & Load to return the result to Excel. This will create a new table in cells E1:O1 with the transposed values.

Note that the Power Query feature will create a connection between the source data and the result, so any changes in the source data will be reflected in the result. If you want to break the connection, you can right-click on the result table and choose Unlink.

Method 4: Using the ampersand sign

The ampersand sign (&) in Excel can concatenate or join two or more text strings or values into a single text string. To use this operator, you need to follow these steps:

• Select a blank cell where you want to place the result.
• Enter the formula =text1 & text2 & ..., where text1, text2, ... are the text strings or values that you want to join.
• Press Enter to complete the formula.

For example, suppose you have the same table as before in cells A1:C3. To rearrange the values from this table to a single cell in cell E1, separated by commas, you can use the formula =A1 & "," & B1 & "," & C1 & "," & A2 & "," & B2 & "," & C2 & "," & A3 & "," & B3 & "," & C3 in cell E1 and press Enter. This will produce the following result:

Note that the ampersand sign will join the values exactly as they are, without ignoring any blank cells or errors. Also, you need to manually enter the delimiter between each value, which can be tedious if you have a large range of cells.