How to Rearrange Data in Excel (5 Suitable Examples)

To do analysis or keep a record of the data you may need to Rearrange Data in Excel. Here I’ll show you how to Rearrange Data in Excel.

For your better understanding, I’m going to use an example of a departmental store dataset that contains three columns. They are Customer ID, Customer Name, and Sales.

 How to Rearrange Data in Excel


Download Practice Workbook

You can download the practice workbook from here:


5 Examples to Rearrange Data in Excel

1. Use of Transpose Command to Rearrange Data

We can use Transpose Command to Rearrange Data. Suppose we have the following sample dataset.

Steps:

  • In the beginning, we have to select the data that we want to rearrange.
  • Here, I selected the range B4:D7.
  • Then, go to the Home tab >> select Copy

 How to Rearrange Data in Excel

  • Then, select a cell where you want to keep the rearranged data. Here, I select the B9 cell.
  • From Paste group >> choose Transpose.

Finally, we can see the Rearranged Data.

 How to Rearrange Data in Excel


2. Applying Power Query to Rearrange Data

We can apply Power Query to Rearrange Data. Let us have the following dataset: Customer ID in column B and Customer Name, Sales, Profit in column C.

Steps:

  • Firstly, select the C4 cell.
  • Secondly, go to the Data tab >> choose From Table/Range.

A dialog box of Create Table will appear.

  • Next, select the data for your table. Here, I selected the range B4:C8.
  • Make sure that “My table has headers” is marked.
  • Then, press OK.

 How to Rearrange Data in Excel

At this time, we will see the Power Query column.

  • Now, right-click on the Customer Name, Sales, Profit column.
  • Then, go to Split Column >> choose By Delimiter.

  • After that, we will select Comma as Delimiter.
  • Then, from “Split at” select Each occurrence of the delimiter
  • Finally, press OK.

 How to Rearrange Data in ExcelSubsequently, we will see the following table.

  • Then,  right-click on the 2ndColumn  >> select Rename.

 How to Rearrange Data in Excel

  • By following the same procedure for other columns, we will get the following table.

  • At the end, from Close & Load >> select Close & Load.

 How to Rearrange Data in Excel

Finally, we will see the Rearranged Data Table.


3. Using Keyboard Shortcut to Rearrange a Single Column

We can use Keyboard Shortcut to Rearrange a Single Column. For this, we are going to use the following dataset.

 How to Rearrange Data in Excel

Steps:

  • At first, select the B4 cell (Customer ID).
  • Then,  press CTRL + SHIFT + L to Filter the dataset

  • Next, click on the filter icon >> select Sort Smallest to Largest >> choose (Select All).

 How to Rearrange Data in Excel

After that, we will see the Rearranged Data.


4. Rearranging Data in Table

We can Rearrange the Data in table format. Suppose we have the following dataset which is not arranged. Now, we will fix the dataset in a table.


Steps:

  • At first, select a different cell (F5) where you want to Rearrange the Data.
  • Then use the corresponding formula in the F5 cell.

=B6

  • Then, press ENTER to bring the value of the B6 cell to the F5 cell.

 How to Rearrange Data in Excel

  • Similarly, we have to do it up to two rows of our table to link with the given dataset.

  • Then, select those two rows.
  • After that, press CTRL+H to bring the Find and Replace dialog box.
  • Insert “=” in the “Find what” box and then insert “#=” in the “Replace with” box.
  • Finally, click on Replace All.

 How to Rearrange Data in Excel

  • Subsequently, we will see the following changes. Then, we have to drag the Fill Handle icon to AutoFill the corresponding data in the rest of the cells.

At this time, we will see the following table.

 How to Rearrange Data in Excel

  • After that, press CTRL+H to bring Find and Replace dialog box again.
  • Next, type “#=” in the “Find what” box and then type “=” in the “Replace with” box.
  • Finally, click on Replace All.

As a result, we will see the Rearranged Data in the table form.


5. Employing Sort Command to Rearrange Data

We can employ the Sort Command to Rearrange Data. Furthermore, this is the easiest method. Now, we will use the following dataset.

 How to Rearrange Data in Excel

Steps:

  • Firstly, we have to select the Customer ID column.
  • Secondly, go to the Data tab >> choose Sort.

  • Subsequently, we have to select “Customer ID” in the “Sort by” box >> “Smallest to Largest” in the “Order” box.
  • Finally, click on “OK”.

 How to Rearrange Data in ExcelAt this time, we will see the following Rearranged Data.


Things to Remember

  • In the Rearranging data table method, we have to select the cells first then we will get the Find and Replace box.
  • When you want to rearrange any data by column then the Sort command or Keyboard shortcut methods will be more convenient for you.
  • If you want to change the alignment of data then the Transpose command will be preferable.
  • In case, when you have mixed data in a single column and you want to separate those into different columns, then applying Power Query will be preferable.

Conclusion

We hope you found this article helpful. Here, we have explained 5 different ways of how to Rearrange Data in Excel. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Musiha

Musiha

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo