How to Sort data in excel
Most of the time you need to sort data in excel, it may be sort in numerically, alphabetically, color or icon. In this article, you will learn many ways to sort data in excel, which allows you to view the data from higher to lowest or by color.And so today I am going to show you the easiest ways to you can sort data in excel with few of the best methods.
Learn the different ways to Sort data in excel , allow you to view data from lowest to highest or by color follow these steps:
-Sort data by one click
-Sort by custom, non-alphabetical in order
-Sort by color or icon
-Rearrange columns by clicking on your mouse and using the keyboard.
How to Sort Dialog Box?
Sort dialog box provides you the most versatile way in sort of data, as it allows you to specify how you want the data to be sorted. While using the dialog box, sort by order applied by excel which appears in the list.
Sort by Values
Sort dialog box makes easy to sort by multiple columns, different sort method is applied for each level.
Sort by values are done in the order to appear in the list:
a. Now select a cell in the datasets, the cell is to determine the location and size of the database of the excel.
b. Now select sort on the data tab.
c. Select My Data if the data has a header box if the excel does not recognize it.
d. Now select first column, sort by drop-down
e. Now select values, sort on drop-down
f. Now select the order drop-down, by selecting the order in which the column is to be sorted.Now sort by in alphabetical order by choosing from A-Z in the opposite order.
g. Now select add level ,add another sort by sale.
h. By drop-down, select second column header to sort.
i. Sort on the drop-down, select values.
j. Order drop-down and now selects order, column data sorted.
k. If the field is in the wrong position, move the arrow up or down on the top of the dialog box, move the field into the current location.
l. Now click Ok, Sort data.
Sort data in excel by color or icon
Excel can be sort data by fill color, font icon, icon setting from conditional format-
a. Select cell in the database, excel cell to determine the size and location of the database.
b. Data tab, select sort.
c. Data having the header row, if excel doesn’t recognize it, select My data.
d. Sort by drop-down, select column header to sort by.
e. Sort on the drop-down, now selects cell color, sort by cell’s fill color. Also choose font color sort by value’s color or cell icon, sort by conditional formatting icons.
f. Order drop-down, select the color by columns data sort by, sort icon with choices of icon
g. From second order drop down, select the color sort by top or bottom of data. Also select multiple colors sort by the top of the data, the color still appears in order decision
h. Now click OK, sort by data
How to Do quick sort?
Quick data sort buttons offer one-click access to sort cell values
Quick sort a single column
Several quick methods can be chosen to apply simple sort by data-
a. Select one cell column sort by, select multiple cells, excel sort data in the selected range.
b. Data tab, select A-Z, sort lowest to highest, Za sort highest to lowest
Quick Sort data in excel by Multiple columns
Excel keep previous sort columns,sort new columns, use the quicksort feature, sort multiple columns.Sort by customer, profit, least to greatest-
a. Select the cell in the column, sort last in the dialog box
b. now on the Data tab, click A-Z
c. select the cell in the next column sort by
d. table sort by the customer, sort by profit customer records.
Performing the custom sorts
Sorting data alphabetically and numerically is not limited, by extra setup work now you can randomize the order or sort by custom sequence.
Perform a Random Sort
Since excel do not have the built-in tool for a random sort, using the RAND function in a column,Right of data by sort create your own randomizer.
a. add new column such as random on the right side of the data by a column of the first cell.
b. type =RAND() in the second cell of the new column and press Ctrl+Enter,the value between 0 and 1 will be calculated by the formula.
c. copy formula to the rest rows in column
d. Now select one cell in the new column
e. click the AZ button on the data tab
f. now as the list will be sorted in a random sequence delete the temporary column which is added in step 1
Sort of a custom sequence
Time to time, data will need to be sorted in a custom sequence which is neither applicable or numerical-
a. select a cell in the database
b. now click sort on the data tab
c. select values , sort by drop-down,
d. select the custom list, order drop-down
e. now click ok
f. Data is sort by custom list
Data is sort by rows by default, instead of that choose sort by columns. Note you are not limited to sort them alphabetically.
Sort Columns with the sort Dialog Box
To sort your data from left to right which is hidden in the sort dialog box, top to bottom instead of the default-
a. insert a new blank row which is above the header by right-clicking on row 1 heading and then select insert.
b. 2type the number of corresponding on the new sequence of the columns, with 1 leftmost column followed by 2 and 3.With each number has a number of denoting in its new location.
c. now select a cell in dataset
d. now press cntrl+A to choose your current region, that includes the two header rows.
e. now select sort on the data tab.
f. now click the options button
g. now select sort left to right
h. now to return to the sort dialog box click OK
i. select Row 1,sort by drop-down
j. select values.,Sort on the drop-down
k. Select smallest to largest, order drop-down
l. now click OK
m. Now excel will rearrange the columns
n. Now delete the temporary row which is added in step 1
How to Fix a Sort data in excel Problems?
If the data did not sort properly or looks like it did not-
- Notice if there is no hidden rows or columns exist
- For a single row use the headers in case if you need a multiline header, you can also wrap the text in the cell or use Alt + Enter to force the line breaks in the cell.
- Data of column should be the same type. It may not be in a column of Zipping codes where some numbers such as 57057, the number which starts by zero are actually text. For solving the problem you can convert the entire column into text.
- For sorting a column which contains a formula, excel will recalculate the column. After the recalculation, if the value got changes such as with RAND.
This is how we sort data in excel. Hope this article was helpful to you and now you know every step that is required to sort any data on excel. Please comment below.