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.
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
- 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.
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.
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.
Subsequently, we will see the following table.
- Then, right-click on the 2ndColumn >> select Rename.
- By following the same procedure for other columns, we will get the following table.
- At the end, from Close & Load >> select Close & Load.
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.
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).
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.
- 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.
- 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.
- 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.
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”.
At 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.
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.
