Advanced sorting and filtering in Excel refers to the process of organizing and narrowing down data in a spreadsheet based on specific conditions or criteria. This feature allows users to quickly and easily identify and extract specific data from large datasets.
- The image below carries two separate data set tables (the left one, not sorted, and the right one. Sorted). If we look closely on the orange box displayed, a question is been thrown at us, saying, sort the data from newest to oldest base on date. i.e the data at the left-hand side.
2. select the column headers i.e (orderDate, Region,City, Category, Product,Quality,Unitprice, Totalprice).
3. After selecting the column headers go to your “Home” tab.
4. Then click on “sort and filter” a menu will dropdown.
5. still on the dropdown menu, select “filter” and click on it.
6. Then the column headers will appear, carrying an arrow head sign beside them.
7. if you watch closely, after clicking on “filter” in step 5 the column headers will carry an arrow-like head beside them, so click on the arrow-like head sign beside “orderDate” on the column and a menu will be displayed.
8. Then select “sort newest to oldest” on the dropdown menu.
9. The data will then be sorted from newest to oldest.
10. Or, alternatively, you can press ctrl z on your keyboard which will automatically undo all you have done, taking it back to the original state where it wasn’t sorted or filtered.
11. Then , click on any cell under the “orderDate” column.
12. A menu will dropdown.
13. Then go to “sort” click on it and select “sort newest to oldest”.
14. And we will achieve the same result.
15. Having it in mind that the question we just solved was for “ sheet 1” so we move to “sheet 2”
16. In “sheet 2”, a question is also thrown to us in the orange box, which says, ”sort the data in ascending order of city and descending order of date “.
17. Go to the data at the left-hand side, since that’s the data we are working with. Then click on “orderDate” column and a menu will drop down.
18. Still on the drop down menu click on “sort” and select “sort Z to A” since you are been asked to sort in descending order.
19. Do same for “City” in the column by clicking on it, and a drop down menu will also appear.
20. Then select “sort” on the drop down menu and click on “sort A to Z’ since you are been asked to sort in ascending order.
21. Your result will be displayed.
Note: if your result doesn’t match with the one at the right hand side then you are doing it wrongly.
22. Next, go to “sheet 3”. Here, in the orange box, the question says, “sort the “category” in order of (Bars, Cookies , Crackers, Snacks).
23. Go to the given table at left hands side and click on “city” and right click on it and a menu will drop down.
24. Go to “sort” and select “custom sort”.
25. It will display a message box.
26. Still on the message box click on “orderDate” and a drop down menu will appear, then select “category values” and click on it .
27. Then click on “cell values” and select “cell values” and click on it.
28. Still on the message box, move to where it is written, “Z to A” click on it and select “custom list”.
29. Another message box will appear after clicking on “custom list”.
30. In the message box enter the list of items you want to arrange or sort i.e (Bars, cookies, crackers, snacks).
Note: After entering each item press enter key on your keyboard for separation of the items during sorting.
31. When you are done typing then click “Add” the message box.
32. Then click “Ok” (still on the message box).
33. And then the entered list will be added in the order in which it has been entered.
34. Then click “ok”.
35. And the result will be displayed.
36. Move to “sheet 4”, in the orange box we will see a question saying, “find all the city in the west region”.
37. Go to the cell under the “Region” column and right click on it (make sure it is the cell that is written “west” only).
38. A drop down menu will appear.
39. Still on the drop down menu select “filter” and click on “filter by selected cell value”.
40. And then the result will be displayed.
41. Alternatively, you can do this by selecting the column headers i.e orderDate, Region, City, category, product, quantity, unitprice, Totalprice.
42. Go to your “Home” tab click on “sort and filter”, a menu will drop down then select “filter”.
43. An arrow head sign will appear beside each of the column headers.
44. Click on the arrow head beside “Region” and a menu will drop down.
45. On the dropdown menu click on “select all”.
46. And select “west” (still on the menu dropdown).
47. Then click on “ok”.
48. You will still get the same result.
49. Move to “sheet 5”. Here the question in the orange box says, “Find all the list of cities with region west category (cookies) and region east category (crackers).
50. First of all copy the column header by selecting and pressing ctrl c on your keyboard.
51. And then paste it at the empty cell beside the data at the left hand side.
52. The column header which you copied and pasted beside the data will carry west and east under the “Region” and then cookies and crackers under the “Category”.
53. When you are done with that, go back to the data that you are filtering from and click on any column where you are filling the data from.
54. Go to your “Data” tab and click on “Advanced”.
55. A message box will be displayed. The message box will select the criteria for you which is (sKs1:SRs3).
56. On the message box click on copy to another location.
57. And press “ok” on the message box.
58. And then our result will be displayed.
59. Move to “sheet 6”. Here the question says, “Find all the cities in eastern region with unitprice(<2) and Totalprice(>50).
60. Select the column headers and copy them by pressing ctrl c on your keyboard.
61. Then paste it beside the data at the left by pressing ctrl v. where, under “Region” you will write “East” and under the “unitprice” you will write “<2” and lastly under the “Totalprice” you will write “>50”.
62. Go to the data and click on any column where you are filling the data from.
63. Go to your “Data” tab and click on “Advanced”.
64. A message box will be displayed.
65. On the message box click on “copy to another location”. The message box will automatically select the criteria in which you are filtering from i.e sLs2:sSs3.
66. Then press “ok” on the message box.
67. And then your result will be displayed.
68. Move to “sheet 7”. Here, in the orange box the question says, “Find all cities with Totalprice between 130 and 400.
69. Select the column headers by clicking and dragging from orderDate to Totalprice.
70 Then go to “Home” tab and click on “sort and filter”.
71. A menu will drop down , scroll down, and click on “Filter”.
72. The column headers will appear carrying an arrow head sign beside them.
73. Go to Totalprice column and click on the arrow head sign and a menu will dropdown.
74. On the drop down menu click on “Number filter” and select “Between”.
75. A message box will appear.
76. On the message box, click on “And” on the check box, make sure it turns blue, then enter 130 on the empty space at where it is written “greater than or equal to” box and enter 400 on the empty space at where it is written “less than or equal to” box.
77. Then, click “ok” on the message box.
78. Then, your result will be displayed.
79. Alternatively, you can do this by selecting and copying the column headers by pressing ctrl c to copy on your keyboard.
80. And then paste it beside the empty cells of the given data set. And enter the criteria which is >130 and <400. Enter >130 under the “Totalprice” and <400 under the “TotalPrice” too. As the question implies.
81. Then click on any column in the data that you are filtering.
82. Go to your “Data” tab and click on “Advanced”.
83. A message box will appear.
84. The message box will automatically select the criteria we are working with i.e sKs3:sSs4. Then click on “copy to another location” on the message box. Then click on “ok”.
85. You will still get the same result as the previous step.
86. Move to “sheet 8” which is the last thing we are going to do. Here, the question says “Find the cities with unitprice 1.77 or 1.87.
87. Click on any cell on the given data set.
88. Go to your “Data” tab and click on “Advanced”.
89. A message box will appear.
90. On the message box, click on “copy to another location. And then click “ok”.
91. And then your result will be displayed.
Leave a Reply