We can separate the data cells that have the same color by sorting the data by color. There are numerous ways to sort by color, including by font color, cell color, and more. When sorting data by color, we may additionally include more levels. Analysis is relatively simple and time-efficient when sorted by color.
How to sort by single color/ Sort by Cell Color?
Given a dataset of Students attendances. In the given data set the rows are colored with green whose attendances are greater than 85%. Try putting all the cells at the top whose attendances are greater than 85%.
Steps for sorting data by single color:
Step-1: Select the cells you want to apply the sort to. (Cells A2 to B11 in the example).
Step 2: Select the Data Tab and then select the Sort option. Choose Sort from the menu. This will bring up the Sort dialog box.
Step 3: Make sure “My Data has headers” is chosen in the Sort dialog box. You can leave this option unchecked if your data doesn’t include headers.
Step 4: There are multiple choices in the pop-up.
Add Level: Depending on the priority, we can add various levels using this option.
Delete Level: It aids in erasing the extra levels.
Copy Level: It enables us to duplicate various levels in order to reorganize the priority.
Step 5: Select the basis on which you wish to sort the data under Sort by column.
Step 6: Select “Cell Color” under “Sort On” to arrange your cells according to the color of their backgrounds.
Step 7: Select the color you wish to retain at the top or bottom of the list by clicking the “Order” drop-down menu.
Step 8: Choose where you want to put the cells with the chosen color by clicking the drop-down menu next to this one. You have two choices: “On Top” and “On Bottom.”
Step-9: Click OK. Result outlined below:
How to Sort by Multiple Color in Excel?
Given a dataset of Students attendances. In the given data set the rows are colored with green whose attendances are greater than 80% and red whose attendance are below 80%. Try putting all the cells at the top whose attendances are greater than 80%.
Steps for sorting data by Multiple color:
Step-1: Select the cells you want to apply the sort to. (Cells A2 to B11 in the example).
Step 2: Select the Data Tab and then select the Sort option. Choose Sort from the menu. This will bring up the Sort dialog box.
Step-3: Select “Attendance” from the “Sort By” drop-down menu. This is the column that we intend to use to sort the data.
Step-4: Go to the ‘Sort On’ drop-down menu and select Cell Color.
Step-5: Pick the first color you want to use to sort the data from the ‘Order’ drop-down. It will display each and every color found in the dataset. Choose green.
Step-6: Choose your first fill color and retain On Top for the Order value.
Step-7: In the panel’s upper left corner, click the Add Level button. This will result in the addition of another sort order rule.
Step 8: Select the basis on which you wish to sort the data under Sort by column.
Step-9: Go to the ‘Sort On’ drop-down menu and select Cell Color.
Step-10: Select the second color you wish to use to sort the data in the ‘Order’ drop-down. It will display each and every color found in the dataset. Choose Red.
Step-11: Choose On-Bottom from the second drop-down menu under Order.
Step-12: Click OK. Result outlined below:
How to sort data by font color?
Steps for sorting by font color:
Step-1: Select the cells you want to apply the sort to. (Cells A1 to B11 in the example).
Step 2: Select the Data Tab and then select the Sort option. Choose Sort from the menu. This will bring up the Sort dialog box.
Step-3: Select “Attendance” from the “Sort By” drop-down menu.
Step-4: Go to the ‘Sort On’ drop-down menu and select Font Color.
Step-5: Pick the red color to sort the data from the ‘Order’ drop-down.
Step-6: Choose ‘On Top’ for the Order value.
Step-7: Click OK. Result outlined below:
How to bring back original order of the data?
- You can easily restore the data to its initial state by using the Undo button (or the shortcut Ctrl + Z). However, you can only accomplish this if you do it immediately after applying filters. After much editing and closing and reopening of workbooks, this would not be a trustworthy way to employ.
- The most dependable technique is to temporarily add a new column with sequential numbering and then, to return to the initial condition, filter using the temporary ordinated column.
Steps for bringing back the original order of the data:
Step-1: Prepare a data table with sequential numbering outlined below:
Step-2: After different Sorting, if you want to revert back, Select the Data Tab and then select the Filter option.
Step-3: Select the dropdown arrow appeared in Index cell.
Filter result will be appeared, outlined below:
You may be interested:
