4 Methods to Rearrange Data in Excel

When dealing with messy data in Excel, reorganizing it properly can save time and reduce errors. Here are some of the most efficient methods to rearrange data:

1. Splitting Alternate Rows Into Two Columns

If you have a list with people’s names and phone numbers in alternating rows, follow these steps:

1. Select Text Data
   • Press Ctrl+G to open the “Go To” dialog.
   • Click Special, choose Constants, uncheck everything except Text, then click OK.
2. Copy and Paste Names
   • Copy the selected text (names), then paste them into a new column.
3. Copy and Paste Numbers
   • Repeat the process, but this time, uncheck everything except Numbers to select phone numbers.
   • Paste these into the adjacent column.
4. Delete Original Data
   • Delete the original column by right-clicking its header.

This method keeps your data in order without using formulas.

2. Turning Vertical Data Horizontal (Transpose)

To flip your data (e.g., change dates from rows to columns), use the transpose function:

1. Copy Data
   Select all the data in your table (including headers) and press Ctrl+C.
2. Paste Transposed Data
   Select the cell where you want the new table, and press Ctrl+Alt+V to open the Paste Special dialog.
   • Choose Values and Number Formats.
   • Check Transpose, then click OK.

This will switch your rows and columns, and you can delete the original table.

3. Sorting Data

You can sort both isolated arrays and columns in a table:

Sorting an Isolated Array

1. Select Data
   Right-click the data and hover over Sort to choose options like “Sort A-Z” or “Sort Z-A” for text, or smallest/largest for numbers.
2. Using the Data Tab
   • With the data selected, go to the Data tab and click A-Z or Z-A for quick sorting.
   • For more options, click Sort in the Data tab and use the dialog box for custom sorting.

Sorting a Column in a Table

1. Add Filter Buttons
   • Select any cell in the table, go to the Data tab, and click Filter to add filter buttons.
2. Sort with Filters
   • Use the filter buttons to sort columns while keeping each row intact.

4. Splitting and Merging Data Using Flash Fill

To split or merge data in Excel, Flash Fill is a powerful tool:

Splitting Data

1. Start with the First Item
   In column B, type the first name (or the part of the data you want to separate).
2. Apply Flash Fill
   • Select the next cell in column B and press Ctrl+E. Excel will automatically continue the pattern.
3. Repeat for Surnames
   For surnames, follow the same process in column C.

Merging Data

1. Start Merging
   Type the first merged value in column C (e.g., “John and Doe”).
2. Apply Flash Fill
   Press Ctrl+E to merge the rest of the data in the same pattern.

You can add extra details (like “and”) during Flash Fill by typing them in the first entry.

5. Sorting Worksheet Tabs Alphabetically

To organize your worksheet tabs:

1. Right-click any Tab
   Right-click on a worksheet tab at the bottom of Excel.
2. Choose “Sort Sheets”
   Select Sort Sheets Alphabetically to organize your tabs in a neat, alphabetical order.

By mastering these simple techniques, you can transform disorganized data into well-structured tables, making analysis and presentation much easier in Excel.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!