How To Sort Data Numerically Within A Group In Excel

Sort Information in Excel & Avoid Issues

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

Avoid Sorting Trouble: Check Your Data

1) Make a Backup of Your Data

Be sure to make a fill-in copy of your Microsoft Excel file, before you start sorting the data. And so, yous tin go dorsum to the saved version, if anything goes wrong.

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

2) Check Your Data

Earlier you sort data in Excel, be sure there are no bare rows or columns inside the data range on the worksheet. The steps below evidence how to do that.

Why is it important to bank check your data?

If there is a blank row or blank column in the data, part of the information might be sorted, while other data is not sorted.
Yous could cease up with names and phone numbers that don't lucifer, or orders with the wrong customer accost.
Bank check for formulas that include an sheet proper name, in references to cells on the current canvas. For a formula example, meet the VLOOKUP Sorting Trouble beneath.

How to Check For Blank Rows or Columns

To assistance prevent sorting problems, follow these steps before yous sort Excel information:

Select one prison cell in the column you want to sort.
Press Ctrl + A, to select the unabridged region.
Check the selected area, to make certain that all the data is included.
- For case, in the screen shot below, hidden cavalcade Eastward is blank
- After pressing Ctrl+A, columns F, M (hidden), H and I are non selected.

sort hidden column

3) Fix Blank Rows and Columns

If some of the information was NOT selected, find the bare rows or columns

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

After yous notice a blank row or column:
- If the blank row or column is not needed, delete it
- If the bare row or column IS needed, enter at least one item in the row or column.
  - For case, blazon an "x" in a column heading, as a placeholder.

Then, after y'all fix any blank columns or rows:

Press Ctrl + A again, to see if the entire region is selected.
- If non, await for other blank rows or columns, and delete or fill them
- If the unabridged region IS selected, the data tin be safely sorted

Quick Sort With Sort Buttons

In Excel, yous can quickly sort your data past using the A-Z (ascending order) and Z-A (descending guild) buttons on the Ribbon'due south Data tab.

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

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

Cheque the Results

Immediately after sorting, and before you exercise anything else:

Bank check 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 button on the toolbar

Sort Buttons on Quick Admission Toolbar

If you sort frequently, you tin add the Sort buttons to your Quick Access Toolbar (QAT). That makes it fifty-fifty easier to do a quick sort in Excel. There are "how to" steps on the Quick Access Toolbar page. that show how to add together buttons.

Then, to use those Sort icons, follow the steps in the Quick Sort with A-Z Buttons department, but use the QAT buttons instead.

sort buttons on quick access toolbar

Sort Two or More than Columns

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

In this case, the tabular array contains personal data, and it will be sorted past 3 columns:

First, by Gender
Adjacent, past State
Finally, by Birth Year

Follow these steps to safely sort the data set past the 3 columns:

Select all the cells in the list.
This is the safest approach to sorting. In about cases, y'all can select one cell and Excel will correctly detect the residual of the list -- but it's non 100% sure. 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 push, to add the kickoff sorting level.
From the Sort by dropdown, select the first column you want to sort. In this example, Gender will be the first column sorted.

sort by drop down

Annotation: If the dropdown is showing Column letters instead of headings, add a check mark to My data has headers.

my data has headers

From the Sort On driblet down, select the option that yous desire. We're sorting on the values in the Gender column, and so leave the default setting of Values.

sort on values

Side by side, from the Lodge drop down, select one of the options. The list of Order options volition depend on what you selected in the Sort On column. Because we selected values, the Gild options are A to Z, Z to A and Custom List. We'll select A to Z.

sort order options

If you lot are sorting on multiple columns, click the Add Level push, to add together the adjacent level, and select options from its drop downwards boxes.
Here we have selected Gender, State and BirthYr as the sort fields, and all are sorted on Values. Because the BirthYr column contains only numbers, its Lodge options are slightly different from the text column options.
Later you lot have selected all the Sort levels, and their options, click OK.

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

Gender column is sorted start, so all the female names are at the top.
Next, the State cavalcade is sorted, so females from Alabama are at the meridian of the list.
Finally, the BirthYr is sorted, with the primeval birth years at the top of each land.

Sort in a Custom Order

In the Sort dialog box, or on the Excel Ribbon, you can select a sort society, such as A to Z, or Largest to Smallest. In addition to these standard options, you tin can sort in a custom order, such as month order, or weekday gild. In this example, nosotros'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 Society video, or follow the written instructions, below the video.

Sort in a Custom Lodge

In the Sort dialog box, or on the Excel Ribbon, yous can select a sort social club, such as A to Z, or Largest to Smallest. In addition to these standard options, you can sort in a custom social club, such as month club, or weekday social club. In this example, nosotros'll sort a column with weekday names, using the Excel Ribbon comman

To sort in a custom club, follow these steps:

Select 1 cell in the cavalcade yous want to sort.
Press Ctrl + A, to select the entire region.
Check the selected area, to brand 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 Order.

custom sort command

In the Sort dialog box, select the Twenty-four hours column in the Sort Past box.
From the Social club drop down, select Custom Listing.

select custom list

In the Custom dialog box, select a custom listing 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, yous tin can sort the data by row. In this example, we'll sort a table of monthly sales, so the calendar month with the largest sales total is at the left. To do this, nosotros'll use a correct-click popup menu.

You tin encounter the steps in this short Sort past Row video, and read the detailed instructions below.

Sorting a Row

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

To sort past a row, follow these steps:

Select i cell in the row you want to sort.
Press Ctrl + A, to select the unabridged region.
Check the selected expanse, to make sure that all the data is included.
Right-click a cell in the row that you want 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 By box.
From the Order driblet down, select Custom List.
At the superlative 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 By drop down, select the row that you want to sort.
There are no headings bachelor, so select the correct Row number.

select row to sort by

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

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

sorted by total row

Sort Data past Jail cell Icon

Watch this curt video to encounter the steps for adding jail cell icons, and sorting past the selected cell'southward icon. There are written steps below the video.

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

drop down list to sort and filter

If yous add provisional formatting icons to ane of the columns, you can also sort past those icons. In the screen shot below, Traffic light icons are being added to the Quantity cavalcade.

add traffic light icons

Sort by Selected Prison cell Icon

Afterward 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 height of the listing
In the pop-up menu, click Sort
Click Put Selected Jail cell Icon On Top

sort by icon

The list is sorted, to motility 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 Down List

After you lot accept added cell icons, you lot tin also sort past icon from the driblet down list in the heading.

Click the drop down arrow in the heading cell
Click Sort by Color
Click on an icon, to move it to the top of the list

list sorted by icon

VLOOKUP Sorting Trouble

A VLOOKUP formula may render the correct results at outset, simply and then shows incorrect results if the list of items is sorted. This tin occur if the reference to the Lookup value includes a canvas name. For example:

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

NOTE: This trouble can occur with other functions likewise, such as an INDEX/Match lookup formula.

Sentinel this video to meet the steps for fixing the trouble, and download the VLOOKUP Sorting problem sample file to follow forth. The written instructions are on the How to Use Excel VLOOKUP - Examples folio.

Sort Sample Workbook

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