is an Easy Way to Arrange Data in Ascending or Descending Order

Sorting data is an integral part of data analysis. Y'all might want to accommodate a list of names in alphabetical order, compile a list of product inventory levels from highest to everyman, or order rows past colors or icons. Sorting data helps yous quickly visualize and sympathise your data better, organize and find the data that you want, and ultimately make more effective decisions.

You lot can sort information by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in ane or more than columns. Yous tin too sort past a custom list yous create (such equally Large, Medium, and Small) or by format, including jail cell colour, font color, or icon prepare.

Sort text

1. Select a cell in the column you want to sort.

2. On the Data tab, in the Sort & Filter grouping, do i of the following:

   • To quick sort in ascending society, click (Sort A to Z).

   • To quick sort in descending guild, click (Sort Z to A).

Notes: Potential Bug

• Bank check that all data is stored every bit text    If the column that yous desire to sort contains numbers stored as numbers and numbers stored as text, you need to format them all every bit either numbers or text. If you do not use this format, the numbers stored as numbers are sorted before the numbers stored as text. To format all the selected information every bit text, Press Ctrl+1 to launch the Format Cells dialog, click the Number tab and and then, under Category, click Full general, Number, or Text.

• Remove any leading spaces    In some cases, data imported from some other awarding might have leading spaces inserted before data. Remove the leading spaces before you sort the data. You tin do this manually, or you tin use the TRIM function.

1. Select a prison cell in the column you want to sort.

2. On the Data tab, in the Sort & Filter grouping, do one of the following:

   • To sort from low to high, click (Sort Smallest to Largest).

   • To sort from high to low, click (Sort Largest to Smallest).

Notes:

• Potential Issue

• Bank check that all numbers are stored as numbers    If the results are not what you expected, the column might contain numbers stored as text instead of as numbers. For example, negative numbers imported from some accounting systems, or a number entered with a leading apostrophe (') are stored every bit text. For more information, see Set text-formatted numbers by applying a number format.

1. Select a cell in the cavalcade you want to sort.

2. On the Data tab, in the Sort & Filter grouping, do one of the post-obit:

   • To sort from an earlier to a later date or time, click (Sort Oldest to Newest).

   • To sort from a later to an before date or time, click (Sort Newest to Oldest).

Notes: Potential Issue

• Check that dates and times are stored as dates or times    If the results are not what you expected, the cavalcade might contain dates or times stored as text instead of as dates or times. For Excel to sort dates and times correctly, all dates and times in a cavalcade must be stored equally a date or time serial number. If Excel cannot recognize a value as a appointment or time, the date or time is stored equally text. For more information, come across Convert dates stored as text to dates.

• If you desire to sort by days of the week, format the cells to bear witness the solar day of the calendar week. If you want to sort past the day of the week regardless of the date, convert them to text by using the TEXT function. However, the TEXT function returns a text value, so the sort operation would be based on alphanumeric information. For more information, run into Show dates as days of the calendar week.

You may want to sort by more than 1 column or row when you have data that you want to group by the same value in one column or row, so sort some other cavalcade or row within that grouping of equal values. For instance, if y'all have a Section column and an Employee column, yous tin can outset sort past Section (to group all the employees in the aforementioned department together), and then sort by proper name (to put the names in alphabetical lodge within each section). You tin sort by upward to 64 columns.

Annotation:For best results, the range of cells that you sort should have column headings.

1. Select any cell in the data range.

   

2. On the Data tab, in the Sort & Filter group, click Sort.

   

3. In the Sort dialog box, under Cavalcade, in the Sort by box, select the showtime cavalcade that you lot desire to sort.

   

4. Under Sort On, select the blazon of sort. Do ane of the post-obit:

   • To sort past text, number, or engagement and fourth dimension, select Values.

   • To sort past format, select Prison cell Colour, Font Color, or Prison cell Icon.

5. Under Order, select how you want to sort. Exercise one of the post-obit:

   • For text values, select A to Z or Z to A.

   • For number values, select Smallest to Largest or Largest to Smallest.

   • For appointment or time values, select Oldest to Newest or Newest to Oldest.

   • To sort based on a custom list, select Custom List.

6. To add another column to sort past, click Add together Level, and so repeat steps three through five.

   

7. To copy a cavalcade to sort by, select the entry and so click Copy Level.

8. To delete a column to sort past, select the entry and and then click Delete Level.

   Note:You must go on at least ane entry in the listing.

9. To change the order in which the columns are sorted, select an entry so click the Up or Downwardly arrow next to the Options button to change the society.

   Entries college in the list are sorted earlier entries lower in the list.

If you take manually or conditionally formatted a range of cells or a table column by cell color or font color, you can also sort by these colors. You lot can also sort by an icon set that you created with provisional formatting.

1. Select a jail cell in the column you want to sort.

2. On the Information tab, in the Sort & Filter group, click Sort.

   

3. In the Sort dialog box, under Column, in the Sort by box, select the column that you desire to sort.

   

4. Under Sort On, select Cell Color, Font Color, or Prison cell Icon.

   

5. Nether Order, click the arrow next to the push so, depending on the type of format, select a cell colour, font color, or cell icon.

6. Next, select how you desire to sort. Do ane of the post-obit:

   • To move the cell color, font colour, or icon to the acme or to the left, select On Tiptop for a column sort, and On Left for a row sort.

   • To move the cell color, font color, or icon to the bottom or to the right, select On Lesser for a column sort, and On Right for a row sort.

     Notation:There is no default cell color, font color, or icon sort lodge. You must define the club that you lot want for each sort operation.

7. To specify the next cell color, font colour, or icon to sort by, click Add Level, and then echo steps three through five.

   Brand sure that you select the aforementioned column in the Then by box and that you brand the same selection under Order.

   Keep repeating for each boosted cell color, font color, or icon that yous want included in the sort.

Yous can use a custom list to sort in a user-defined order. For case, a column might contain values that you want to sort past, such as High, Medium, and Low. How tin you sort so that rows containing Loftier appear first, followed by Medium, then Low? If you lot were to sort alphabetically, an "A to Z" sort would put High at the top, only Low would come before Medium. And if you lot sorted "Z to A," Medium would appear first, with Low in the middle. Regardless of the order, yous always want "Medium" in the centre. By creating your own custom listing, you can get effectually this trouble.

1. Optionally, create a custom list:

   1. In a range of cells, enter the values that you want to sort by, in the society that you want them, from pinnacle to lesser equally in this example.

      

   2. Select the range that you just entered. Using the preceding case, select cells A1:A3.

   3. Become to File > Options > Advanced > General > Edit Custom Lists, and so in the Custom Lists dialog box, click Import, and then click OK twice.

      Notes:

      • You can create a custom list based only on a value (text, number, and date or time). You cannot create a custom list based on a format (cell color, font colour, or icon).

      • The maximum length for a custom list is 255 characters, and the first grapheme must not brainstorm with a number.

2. Select a prison cell in the column you desire to sort.

3. On the Data tab, in the Sort & Filter grouping, click Sort.

   

4. In the Sort dialog box, under Cavalcade, in the Sort past or And so by box, select the column that you lot want to sort by a custom list.

5. Under Order, select Custom List.

6. In the Custom Lists dialog box, select the list that you want. Using the custom list that you lot created in the preceding instance, click High, Medium, Low.

7. Click OK.

1. On the Data tab, in the Sort & Filter group, click Sort.

   

2. In the Sort dialog box, click Options.

   

3. In the Sort Options dialog box, select Example sensitive.

   

4. Click OK twice.

It's most common to sort from top to lesser, but you can also sort from left to right.

Note:Tables don't back up left to right sorting. To do then, outset catechumen the table to a range by selecting any cell in the table, and and then clicking Tabular array Tools > Catechumen to range.

1. Select any cell inside the range you want to sort.

2. On the Data tab, in the Sort & Filter grouping, click Sort.

   

3. In the Sort dialog box, click Options.

   

4. In the Sort Options dialog box, nether Orientation, click Sort left to right, and then click OK.

   

5. Under Row, in the Sort past box, select the row that you want to sort. This will more often than not be row 1 if y'all want to sort by your header row.

   

   Tip:If your header row is text, but you desire to order columns by numbers, you can add a new row in a higher place your information range and add together numbers according to the social club y'all desire them.

6. To sort by value, select 1 of the options from the Social club drib-down:

   1. For text values, select A to Z or Z to A.

   2. For number values, select Smallest to Largest or Largest to Smallest.

   3. For date or time values, select Oldest to Newest or Newest to Oldest.

7. To sort past cell colour, font color, or jail cell icon, do this:

   1. Under Sort On, select Cell Color, Font Color, or Cell Icon.

   2. Nether Order select a cell color, font colour, or cell icon, then select On Left or On Correct.

Note:When you lot sort rows that are role of a worksheet outline, Excel sorts the highest-level groups (level 1) so that the detail rows or columns stay together, even if the detail rows or columns are hidden.

To sort by a part of a value in a cavalcade, such as a role number lawmaking (789-WDG-34), last name (Ballad Philips), or first name (Philips, Carol), you first demand to split up the cavalcade into two or more than columns and so that the value you want to sort by is in its ain column. To do this, you can utilise text functions to separate the parts of the cells or you can utilize the Convert Text to Columns Wizard. For examples and more information, come across Split text into different cells and Split text among columns by using functions.

Alarm:Information technology is possible to sort a range within a range, merely information technology is not recommended, considering the issue disassociates the sorted range from its original information. If yous were to sort the following data as shown, the selected employees would exist associated with different departments than they were earlier.

Fortunately, Excel will warn you lot if it senses yous are almost to endeavor this:

If yous did not intend to sort like this, and then printing the Expand the choice option, otherwise select Go on with the electric current selection.

If the results are not what you desire, click Undo .

Note:Y'all cannot sort this style in a table.

If you lot get unexpected results when sorting your data, do the following:

Check to see if the values returned by a formula accept changed    If the data that you have sorted contains ane or more formulas, the return values of those formulas might modify when the worksheet is recalculated. In this case, make certain that you reapply the sort to get up-to-date results.

Unhide rows and columns earlier you sort    Hidden columns are not moved when you sort columns, and hidden rows are not moved when you sort rows. Before you lot sort information, it's a good thought to unhide the hidden columns and rows.

Bank check the locale setting    Sort orders vary by locale setting. Make sure that you have the proper locale setting in Regional Settings or Regional and Linguistic communication Options in Command Panel on your calculator. For data well-nigh changing the locale setting, see the Windows help system.

Enter column headings in merely one row    If y'all need multiple line labels, wrap the text within the cell.

Turn on or off the heading row    It'southward usually best to have a heading row when y'all sort a cavalcade to make it easier to sympathize the meaning of the data. Past default, the value in the heading is not included in the sort performance. Occasionally, yous may need to turn the heading on or off so that the value in the heading is or is not included in the sort operation. Practice ane of the post-obit:

• To exclude the get-go row of information from the sort because information technology is a cavalcade heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort and then select My data has headers.

• To include the first row of data in the sort considering it is not a column heading, on the Home tab, in the Editing group, click Sort & Filter, click Custom Sort, and so clear My information has headers.

If your information is formatted as an Excel table, then you tin quickly sort and filter it with the filter buttons in the header row.

1. If your data isn't already in a table, so format it equally a table. This volition automatically add a filter button at the meridian of each table column.

2. Click the filter push at the elevation of the column you want to sort on, and choice the sort gild you want.

   

3. To undo a sort, use the Disengage button on the Home tab.

   

1. Pick a cell to sort on:

   • If your data has a header row, pick the 1 you want to sort on, such as Population.

     

   • If your data doesn't accept a header row, pick the topmost value you lot want to sort on, such as 634535.

     

2. On the Data tab, pick 1 of the sort methods:

   • Sort Ascending to sort A to Z, smallest to largest, or earliest to latest appointment.

   • Sort Descending to sort Z to A, largest to smallest, or latest to primeval engagement.

Permit's say you accept a tabular array with a Department cavalcade and an Employee column. Yous tin can kickoff sort by Section to group all the employees in the same department together, then sort by name to put the names in alphabetical order within each department.

Select any prison cell within your information range.

1. On theData tab, in the Sort & Filter grouping, click Custom Sort.

   

2. In the Custom Sort dialog box, underColumn, in the Sort by box, select the first cavalcade that y'all want to sort.

   

   Note:The Sort On bill of fare is disabled because information technology's non nevertheless supported. For at present, you can change it in the Excel desktop app.

3. NetherLodge, select how you want to sort:

   • Sort Ascending to sort A to Z, smallest to largest, or earliest to latest date.

   • Sort Descending to sort Z to A, largest to smallest, or latest to primeval appointment.

4. To add another column to sort past, click Add and then echo steps v and six.

   

5. To alter the gild in which the columns are sorted, select an entry and and so click theUp orDown arrow next to theOptions push button.

If y'all have manually or conditionally formatted a range of cells or a table column past cell color or font colour, you tin too sort past these colors. You can besides sort past an icon set up that you created with provisional formatting.

1. Select a jail cell in the cavalcade you desire to sort

2. On theData tab, in theSort & Filtergroup, selectCustom Sort.

3. In theCustom Sortdialog box netherColumns, select the column that you want to sort.

4. Under Sort On, select Cell Colour, Font Color or Conditional Formatting Icon.

5. UnderGuild, select the lodge you lot want (what you see depends on the type of format you have). And then select a jail cell colour, font colour, or cell icon.

6. Next, you lot'll cull how you lot want to sort past moving the jail cell color, font colour, or icon:

   Note:There is no default prison cell color, font colour, or icon sort order. You must define the order that you lot want for each sort performance.

   • To move to the meridian or to the left: SelectOn Peak for a column sort, andOn Left for a row sort.

   • To movement to the bottom or to the right: SelectOn Bottom for a column sort, andOn Right for a row sort.

7. To specify the next jail cell color, font color, or icon to sort past, selectAdd together Level, and and so echo steps 1- 5.

• Make certain that you the cavalcade in theThen by box and the choice under Gild are the same.

• Repeat for each boosted cell color, font color, or icon that you want included in the sort.

1. On theDatatab, in theSort & Filter group, click Custom Sort.

   

2. In the Custom Sort dialog box, clickOptions.

   

3. In theOptions menu, selectInstance sensitive.

   

4. ClickOK.

It's well-nigh common to sort from meridian to bottom, but you can likewise sort from left to right.

Note:Tables don't support left to right sorting. To exercise so, first catechumen the tabular array to a range by selecting whatsoever prison cell in the table, and and so clicking Table Tools > Convert to range.

1. Select any jail cell inside the range you desire to sort.

2. On theDatatab, in theSort & Filter group, selectCustom Sort.

   

3. In the Custom Sort dialog box, clickOptions.

4. UnderOrientation, clickSort left to right

5. NetherRow, in the 'Sort by' drop downwardly, select the row that you desire to sort. This will mostly be row 1 if you lot want to sort by your header row.

6. To sort by value, select ane of the options from the Society drib-down:

   • Sort Ascending to sort A to Z, smallest to largest, or earliest to latest date.

   • Sort Descending to sort Z to A, largest to smallest, or latest to earliest appointment

Need more aid?

You tin always ask an expert in the Excel Tech Community or become back up in the Answers community.

See also

Utilize the SORT and SORTBY functions to automatically sort your information.

holdenginfortiect1971.blogspot.com

Source: https://support.microsoft.com/en-us/office/sort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654

Share this post