How To Make A List Within A Cell In Excel

Harshita Srivastava

How to Apply Excel Sort (Without Messing Up Your Data)

How to Use Excel Sort -magoosh

Sorting data is considered to be ane of the nigh essential components of data analysis equally it helps in organizing or arranging the data in a specified desirable order and aids the procedure of formatting and visualizing information. You might desire to arrange a listing of values alphabetically, compile a list of product values from highest to lowest, or order rows by their respective icons or colors.

Sorting data in Excel helps the user to understand and visualize data more effectively, facilitates investigation of data, aids the process of searching, organizing and formatting information and ultimately, helps in making efficient decisions. You 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 columns. Y'all can besides sort using a custom list you create (such as A, C, and B) or by the format of cells which tin can exist done on the footing of cell color, font color, or cell icon.

The following commodity discusses the various methods and procedures of sorting information in specified order as well every bit diverse criterions that can be applied on the procedure to increase the orderly efficiency of the spreadsheet.

Sorting text in MS Excel

There might exist times when values in cells are textual and need to be sorted accordingly, in order to brand the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of information as well as aid constructive conclusion making.

The following procedure needs to exist performed to sort text in Excel:

Select a cell or range of cells in the column which needs to be sorted.
Click on the Data tab available in Bill of fare Bar, and perform a quick sort past choosing whatever ane of the options under the Sort & Filter group, depending upon whether y'all want to sort in ascending or descending order.

The text volition be sorted in the column of spreadsheet accordingly.

Note: Cheque that all information in the column to be sorted is stored every bit text. If the cavalcade that you lot want to sort contains values that are numerical, y'all need to change them into their respective text, failing to do so, the numbers would be stored equally numbers are sorted before the numbers stored as text.

To format all the selected data in a item cavalcade as text, printing Ctrl+1 simultaneously on the keyboard that will launch the Format Cells dialog, click on the Number tab then, under Category section, click on General, Number, or Text.

Remove any leading spaces: In some cases, when data is imported or copied from any another application, the information values might have some leading spaces, which can produce undesirable sorting results, when the functioning is practical. Thus, all the leading spaces need to be removed before sorting information. This can be done manually or using TRIM function.

Sorting numbers in MS Excel

There might be times when values in cells are numerical and demand to exist sorted accordingly, in order to make the spreadsheet look orderly. The need of sorting may besides arise from the fact that it can facilitate analysis and investigation of data likewise as aid effective conclusion making.

The following procedure needs to be performed to sort numbers in Excel:

Select a prison cell or range of cells in the cavalcade which needs to be sorted.
Click on the Data tab available in Menu Bar, and perform quick sort by choosing whatsoever one of the options nether the Sort & Filter grouping, depending upon whether y'all want to sort in ascending or descending order.

The numbers will be sorted in the column of spreadsheet accordingly.

Note: Check that all the data to be sorted in the specified column contains values that are numerical. If the result is not desired, the column might comprise values which are not numbers and are treated every bit text by the software. In that example, the data needs to exist changed into numbers past pressing Ctrl+1 simultaneously on the keyboard that will launch the Format Cells dialog, clicking on the Number tab and then, nether Category section, clicking on Number. Leading spaces in the data values imported or copied also need to exist removed manually, which can be achieved manually or by using TRIM function.

Sorting Engagement or Fourth dimension in MS Excel

There might exist times when values in cells are dates or times and need to be sorted appropriately, in order to make the spreadsheet wait orderly. The need of sorting may besides arise from the fact that information technology tin facilitate analysis and investigation of information equally well as aid effective decision making.

The post-obit process needs to be performed to sort date or time in Excel:

Select a cell in the column which needs to be sorted.
Click on the Information tab available in Bill of fare Bar, and perform quick sort by choosing any 1 of the options under the Sort & Filter grouping, depending upon whether yous desire to sort in ascending or descending order.

The date or time in the cavalcade of the spreadsheet will be sorted accordingly.

Annotation: Check that all the data to be sorted in the specified column contains values that are either date or fourth dimension. If the issue is not desired, the column might contain values which are non stored as date and fourth dimension but are treated as numbers or text by the software. In that example, the data needs to be changed into the engagement or time serial values. Leading spaces in the information values imported or copied also need to be removed manually, which can be achieved manually or by using TRIM role.

If you want to sort the values in the cavalcade by days of the calendar week, format the cells in a fashion to display the solar day of the week and catechumen them to text by using the TEXT office and perform the sorting operation accordingly.

Sorting Multiple Rows or Columns

There might be times when the data in MS Excel needs to be sorted by performing the operation on more than 1 row or cavalcade, in guild to brand the spreadsheet look orderly. The need of sorting may also arise from the fact that it can facilitate analysis and investigation of information likewise as produce efficiency in the decision-making procedure. This example of sorting may especially arise when the sorting in ane column (or row) may have equal values, wherein values tin be textual, numerical or engagement or time, such that further sorting can be applied on another column (or row), leading to an ordered organisation in the spreadsheet.

For example, in the spreadsheet Society Piece of work, if the sorting is initially applied onto the alphabetic nature of the Departments' employees are selected for, farther sorting may exist applied onto the column of Name, to make the arrangement ordered. In order to achieve best results of sorting in a spreadsheet, the range of cells on which sorting is applied should take cavalcade headings to brand the arrangement highly ordered which tin can lead to a proper analysis of data and constructive decision making.

The following procedure needs to be performed to sort multiple rows or columns in Excel:

Select any cell within the data range wherein sorting needs to be applied.
Click on the Data Tab on Menu Bar, and farther click on Sort under Sort & Filter group.
Sort dialog box opens up. Select the commencement column of priority which needs to be sorted under Column, in the Sort dialog box.
Nether Sort On List, select the type of sort that needs to be applied. Anyone of the following options can be selected:
- Select Values to sort past value of text, number or date and time.
- Select Prison cell Color, Font Color or Jail cell Icon to sort by the format applied on the column cells.
Nether Club list, select the mode of sorting. Anyone of the following options can be selected:
- For sorting text values, select A to Z or Z to A appropriately.
- For sorting numerical values, select Smallest to Largest or Largest to Smallest accordingly.
- For sorting date or fourth dimension values, select Oldest to Newest or Newest to Oldest accordingly.
- For sorting based on a custom list, select Custom List.
Optional: Click on Add level button in the Sort dialog box to add another cavalcade to sort by and repeat the steps iii to five to sort finer.
Optional: Select the entry and click on Copy Level push in the Sort dialog box to copy a column/field to sort by.
Optional: Select the entry and click on Delete Level push in the Sort dialog box to delete a cavalcade/field to sort by.
Optional: To change the order in which the columns or fields in the spreadsheet need to be sorted, select an entry of the Column Proper noun and and then click on the Up or Downwards arrow button, next to the Options button to change the social club of sorting, provided entries higher in the list would exist sorted before entries which announced lower in the list.
Click on OK.

Sorted spreadsheet will be obtained.

Note: One entry for sorting the spreadsheet is essential in the dialog box.

Sorting by Prison cell Colour, Font Color, or Icon in Excel

If there is a range of cells or columns which accept been manually or conditionally formatted such that they have cells containing different prison cell colors, font colors or icons, they can exist ordered into a specified arrangement even on the ground of their formats such that it tin can it tin can facilitate analysis and investigation of data as well as produce efficiency in decision making process.

The following procedure needs to be performed to sort cells by cell color, font color or icon in Excel:

Select any cell in the column where sorting needs to be applied.
Click on the Information Tab on Carte Bar, and further click on Sort under Sort & Filter grouping.
Sort dialog box opens upward. Select the column which needs to exist sorted under Column list.
Under Sort On list, select Cell Color, Font Color or Cell Icon, based upon the criterion of sorting.
Under Gild list, click on the arrow of the drib-downward list and so, depending on the blazon of cells formats, select a cell colour, font color, or prison cell icon, based upon which the formatting needs to be washed.
Select the club of sorting inside the mode by performing the steps every bit desired:
- To movement the desired cell color, font color, or cell icon to the top or to the left, equally per the desired society requirement, select On Pinnacle option for a column sort, and On Left pick for a row sort.
- To move the cell color, font colour, or cell icon to the lesser or to the right, as per the desired order requirement, select On Bottom option for a column sort, and On Correct pick for a row sort.
Note: There is no default cell colour, font color, or icon sort order predefined by Excel, as there tin be a large number of prison cell color, font color or cell icon divers by the user. Thus, the user must explicitly ascertain the club of sorting in instance of sorting by formatting of cells.
Optional: Select the entry and click on Copy Level button in the Sort dialog box to copy a column/field to sort by.
Optional: Select the entry and click on Delete Level button in the Sort dialog box to delete a column/field to sort past.
Optional: To change the order in which the columns or fields in the spreadsheet need to be sorted, select an entry of the Column Name and then click on the Up or Downwardly arrow button, adjacent to the Options button to change the order of sorting, provided entries higher in the list would exist sorted before entries which announced lower in the listing.
Click on OK.

Sorted Spreadsheet will exist obtained.

Sorting using Custom List in Excel

A custom listing is a dummy listing defined by the user to sort the values of a list in an arrangement or order that is user-defined. This may also exist considered equally a special style of conditional formatting, wherein values contained in a custom list are textual, based on which further sorting is performed.

For example, consider a listing defined by A, B and C group of employees, wherein the user-defined club wants to sort list A, C and B in the social club of employees. This problem is compounded past the issue of not existence able to sort employees on the ground of their alphabetical nature, as this guild is neither ascending alphabet nor descending alphabet. Creating a user defined list, also known equally a custom list, is a way to solve the problem and accomplish the desired sorted spreadsheet.

The following steps need to be performed in order to create the custom listing and perform sorting:

Creation of Custom List:
1. Enter the values that you want to sort the values by, in the order you want them, from height to bottom, in unlike cells in the range of cells.
2. Select the range of cells of the order in which you lot want to sort.
3. Click on File or Office Button on the top left corner of Excel Awarding plan, click on Excel Options at the lesser.
4. Excel Options dialog box opens up.
5. Click on Popular Tab, and click on Edit Custom Lists button under Top Options for Working with Excel.
6. In the Custom Lists dialog box, click on Import list from cells to import the values of the custom list by selecting the range of cells that decide the order and finally, click on OK twice, to create the requisite Custom List.
Note: A custom list tin be created past the user but based upon values such as text, number and engagement or time, and cannot exist created based on a format of jail cell colour, font color or jail cell icon. Also, the maximum length for a custom list that can be created by the user is 255 characters and the offset graphic symbol of the custom list cannot begin with a number or a special character.
2. Sorting using Custom List:
1. Select the cell or the range of cells in the cavalcade which needs to be sorted.
2. Click on the Data Tab on the Menu bar, and click on Sort under Sort & Filter section.
3. The Sort dialog box opens up. Select the column that y'all want to sort past a custom listing under the Column list.
4. Under the Order list, click on Custom List.
5. In the Custom Lists dialog box, select the custom list that you lot want to use for sorting. In the above example, the created list of value A, C, B needs to exist used.
6. Click on OK.

Sorted Spreadsheet using Custom Listing would be obtained.

Advantages of Excel Sort

Produces ordered arrangement of spreadsheet.
Facilitates efficient decision making procedure.
Results in effective analysis and investigation of data.
Aids the process of searching, organizing and formatting data.

In this mail, you learned about how to apply Excel sort functionality. Crawly! Make your work easier now by using the Excel sort you just learned! 🙂

Harshita is enthusiastic nearly mentoring JEE aspirants and helping make the Magoosh blog an interesting and effective study source. She loves challenges and programming. She also loves music and she (thinks she) is pretty good at dancing.

View all posts