How To Sort Columns In Excel Without Mixing Data

Sort Data in Excel & Avoid Problems

How to sort in Excel listing by row or column. Tips show how to avert painful mistakes when sorting your spreadsheets. How to sort multiple columns, rows, or sort in custom order. Videos, and Excel workbook.

Avert Sorting Trouble: Check Your Information

1) Brand a Fill-in of Your Data

Be sure to make a backup copy of your Microsoft Excel file, before yous start sorting the information. Then, you tin can go dorsum to the saved version, if anything goes wrong.

Tip: For a quick and easy backup re-create, get my free Excel Backup tool. This tool makes a backup re-create in the current folder, and does Not affect the active workbook. The backup tool is in xlam format, so it's easy to install on your reckoner - simply like any other Excel file.

2) Check Your Information

Before you sort data in Excel, be sure in that location are no blank rows or columns inside the data range on the worksheet. The steps below show how to do that.

Why is information technology of import to check your data?

If there is a blank row or blank column in the data, part of the data might be sorted, while other data is non sorted.
Yous could terminate up with names and phone numbers that don't match, or orders with the wrong customer address.
Check for formulas that include an sheet proper name, in references to cells on the electric current sheet. For a formula instance, see the VLOOKUP Sorting Trouble below.

How to Check For Blank Rows or Columns

To help prevent sorting issues, follow these steps earlier you sort Excel information:

Select one cell in the column yous want to sort.
Printing Ctrl + A, to select the entire region.
Check the selected surface area, to make certain that all the data is included.
- For example, in the screen shot below, hidden column E is bare
- Subsequently pressing Ctrl+A, columns F, G (subconscious), H and I are non selected.

sort hidden column

3) Gear up Blank Rows and Columns

If some of the information was Not selected, detect the bare rows or columns

You might need to unhide rows or columns, to find the blank ones

Later on you detect a blank row or column:
- If the blank row or column is not needed, delete it
- If the blank row or column IS needed, enter at to the lowest degree one item in the row or column.
  - For case, type an "x" in a cavalcade heading, as a placeholder.

Then, after you gear up whatever blank columns or rows:

Printing Ctrl + A once more, to see if the unabridged region is selected.
- If non, await for other blank rows or columns, and delete or fill them
- If the entire region IS selected, the information can be safely sorted

Quick Sort With Sort Buttons

In Excel, you can speedily sort your data past using the A-Z (ascending order) and Z-A (descending lodge) buttons on the Ribbon's Information tab.

Follow these steps to sort with the Quick Sort command buttons:

Select one cell in the column you lot desire to sort.
On the Excel Ribbon, click the Information tab.
Click Sort A to Z (smallest to largest) or Sort Z to A (largest to smallest)

Check the Results

Immediately after sorting, and before you exercise anything else:

Cheque the sorted data, to see if the data has been sorted correctly.

If anything looks incorrect, or out of order:

Immediately click the Undo push on the toolbar

Sort Buttons on Quick Access Toolbar

If you sort often, you tin can add together the Sort buttons to your Quick Access Toolbar (QAT). That makes information technology even easier to practice a quick sort in Excel. There are "how to" steps on the Quick Access Toolbar page. that prove how to add together buttons.

And then, to use those Sort icons, follow the steps in the Quick Sort with A-Z Buttons section, but employ the QAT buttons instead.

sort buttons on quick access toolbar

Sort Ii or More Columns

If you want to sort 2 or more than columns in an Excel table, use the Sort dialog box, where you tin gear up a multi-level sort.

In this case, the table contains personal information, and information technology will be sorted past iii columns:

First, by Gender
Next, by State
Finally, by Birth Twelvemonth

Follow these steps to safely sort the data fix by the iii columns:

Select all the cells in the list.
This is the safest approach to sorting. In most cases, you can select ane cell and Excel volition correctly detect the rest of the list -- but information technology's not 100% certain. Some of the information may be missed.
On the Excel Ribbon, click the Data tab.
In the Sort & Filter group, click the Sort button.

data tab sort

Click the Add together Level button, to add the first sorting level.
From the Sort by dropdown, select the first column you desire to sort. In this example, Gender will exist the beginning cavalcade sorted.

sort by drop down

Note: If the dropdown is showing Cavalcade letters instead of headings, add together a check marking to My data has headers.

my data has headers

From the Sort On drib down, select the selection that you want. We're sorting on the values in the Gender column, so leave the default setting of Values.

sort on values

Next, from the Guild driblet down, select ane of the options. The listing of Order options will depend on what you selected in the Sort On column. Considering nosotros selected values, the Club options are A to Z, Z to A and Custom List. We'll select A to Z.

sort order options

If yous are sorting on multiple columns, click the Add Level push, to add the next level, and select options from its drop downwardly boxes.
Here we have selected Gender, State and BirthYr as the sort fields, and all are sorted on Values. Because the BirthYr cavalcade contains just numbers, its Society options are slightly different from the text column options.
After y'all have selected all the Sort levels, and their options, click OK.

The data will be sorted in the order that you specified. In the screen shot below:

Gender column is sorted first, so all the female person names are at the elevation.
Next, the State cavalcade is sorted, so females from Alabama are at the superlative of the list.
Finally, the BirthYr is sorted, with the earliest birth years at the top of each state.

Sort in a Custom Order

In the Sort dialog box, or on the Excel Ribbon, y'all tin select a sort social club, such every bit A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom lodge, such as month order, or weekday order. In this instance, we'll sort a column with weekday names, using the Excel Ribbon control.

Lookout man the steps for doing a custom sort in the Sort Custom Guild video, or follow the written instructions, below the video.

Sort in a Custom Lodge

In the Sort dialog box, or on the Excel Ribbon, you lot can select a sort order, such as A to Z, or Largest to Smallest. In improver to these standard options, you can sort in a custom gild, such as calendar month order, or weekday order. In this example, we'll sort a column with weekday names, using the Excel Ribbon comman

To sort in a custom order, follow these steps:

Select one cell in the column you lot want to sort.
Press Ctrl + A, to select the entire region.
Bank check the selected area, to make sure that all the data is included.
On the Excel Ribbon, click the Home tab
In the Editing group, click the arrow on Sort & Filter.
Click Custom Social club.

custom sort command

In the Sort dialog box, select the Day column in the Sort By box.
From the Club drop downwards, select Custom Listing.

select custom list

In the Custom dialog box, select a custom list and and then click OK, twice, to close the dialog boxes.

The Day column is sorted in weekday order, instead of alphabetical order, and so Sunday appears at the top of the list.

sorted by weekday

Sorting a Row

Instead of sorting your data by columns, you lot can sort the data by row. In this example, we'll sort a table of monthly sales, and so the month with the largest sales total is at the left. To do this, we'll employ a correct-click popup menu.

You can see the steps in this short Sort by Row video, and read the detailed instructions beneath.

Sorting a Row

Instead of sorting your information by columns, yous tin can sort the data by row. In this example, nosotros'll sort a tabular array of monthly sales, so the month with the largest sales full is at the left. To practice this, we'll employ a right-click popup menu.

To sort past a row, follow these steps:

Select ane jail cell in the row you want to sort.
Press Ctrl + A, to select the entire region.
Bank check the selected expanse, to make certain that all the data is included.
Right-click a cell in the row that you desire to sort
In the popup menu, click Sort, so click Custom Sort.

custom sort popup menu

In the Sort dialog box, select the Day column in the Sort Past box.
From the Order drop downwardly, select Custom List.
At the meridian of the Sort dialog box, click the Options push.

sort options button

In the Sort Options dialog box, under Orientation, select Sort Left to Right.

sort left to right

Click OK, to close the Options dialog box.
From the Sort Past drib down, select the row that y'all want to sort.
In that location are no headings available, and then select the correct Row number.

select row to sort by

Select the Sort On, and the Lodge options, so click OK.

The information is sorted by the values in the selected row.

sorted by total row

Sort Data past Jail cell Icon

Watch this curt video to see the steps for adding jail cell icons, and sorting by the selected prison cell'due south icon. In that location are written steps below the video.

When you create a named Excel table, or employ an AutoFilter to a list, each heading cell gets a driblet down arrow. Click that arrow, and you'll see a variety of sorting and filtering options for the data.

drop down list to sort and filter

If you lot add conditional formatting icons to i of the columns, yous can also sort by those icons. In the screen shot beneath, Traffic low-cal icons are existence added to the Quantity column.

add traffic light icons

Sort by Selected Cell Icon

After adding icons, the quickest way to sort by a specific icon is:

Right-click on a cell that contains the icon you want at the peak of the listing
In the pop-up menu, click Sort
Click Put Selected Cell Icon On Meridian

sort by icon

The list is sorted, to movement all items with the selected icon to the top of the list.

Other items are not sorted, and the items that were moved to the top of the list are left in their original order, within that group.

list sorted by icon

Sort With Heading Drop Downwards Listing

After you have added cell icons, you can also sort by icon from the driblet down list in the heading.

Click the driblet downward arrow in the heading cell
Click Sort by Color
Click on an icon, to move information technology to the top of the list

list sorted by icon

VLOOKUP Sorting Problem

A VLOOKUP formula may render the correct results at first, but and so shows incorrect results if the list of items is sorted. This can occur if the reference to the Lookup value includes a canvas name. For instance:

=VLOOKUP('Order Form'!B5, Products!$B$two:$C$6,2,FALSE)

NOTE: This trouble tin can occur with other functions likewise, such every bit an INDEX/MATCH lookup formula.

Watch this video to see the steps for fixing the problem, and download the VLOOKUP Sorting problem sample file to follow along. The written instructions are on the How to Use Excel VLOOKUP - Examples page.

Sort Sample Workbook

To try the sorting techniques, get the Sort sample workbook. The file is in xlsx file format, and is zipped. Information technology does not contain any macros.