How to Rearrange Data in Excel (5 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


1. Using Transpose Command to Rearrange Data in Excel

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 for Rearranging 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.

Read More: How to Rearrange Columns in Excel


4. Rearranging Data in Excel 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 the 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.

Read More: How to Automatically Rearrange Columns in Excel


5. Applying 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.

Read More: How to Rearrange Columns in Excel


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.

Download Practice Workbook

You can download the practice workbook from here:


Conclusion

We hope you found this article helpful. Here, we have explained 5 different ways of how to Rearrange Data in Excel. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Rearranging in ExcelData Analysis with Excel Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo