In this chapter, we're going to learn how to do sorting and filtering.
Use this workbook for the chapter.
Note: This is the same workbook used in the Navigation Shortcuts chapter.
Let's start with sorting. In our spreadsheet, we have a list with a header at the top. It always helps to have a header of labels at the top for sorting. To sort, do the following.
Let's suppose we want to do a little bit different sort. Once you have selected custom sort, you can select multiple levels of sorting. The box that opens starts with a single row, telling you that it will sort by the first column based on the order Z to A. (See Figure 12.2)
To add a level, choose to sort next based on any of the other columns that we have.
In this example, we’ll do the following.
One other thing to note here is that when you do a custom sort, there is a check box here at the top of the box that appears that says My Data Has Headers. (See Figure 12.3)
When this is selected, it means that there is a header row at the top, and it will automatically deselect that row. It also allows it to know what options should exist in the drop-down lists. If you deselect this, now it will include the top row in the sorting and it only does it by column. So, generally, it makes sense to have a header row, and then to checkmark that your data has a header row in it.
The second thing you will learn about is data filters. In order to turn on a filter, go onto the Home tab again. Off to the upper right-hand corner under Sort and Filter, select Filter. (See Figure 12.4)
When you do this, it will recognize all of the data on the sheet and put a filter in place at the top. A filter is a series of drop-down lists that allows us to manage a table using those drop-down arrows. (See Figures 12.5 and 12.6)
Instead of sorting, if you want, you can also filter out certain entries. By default, everything is selected. If you open up the drop-down arrow again, for example, in the Class column, when you uncheck the Select All box, nothing will be selected. Then, you can scroll down and select the items that you want to be sorted. (See Figure 12.7)
When you select OK, notice that it filters out everything in the list, except for the items that you had check marked. When something is filtered, a filter icon will replace the arrow icon that had previously been displayed. Only the items selected are shown in the list. Now, it's important to note that they're not gone. They're just hidden. You'll notice with the row that the numbers jump because the information that wasn’t selected is hidden.
In addition to the filter in the Class column, you could also add a filter to the Lead Time. This time, instead of checking things off the list, you can choose Number Filters, which provides many more different automatic filters. For example, you can select a Less Than filter, and show only items Less Than 10. (See Figure 12.8)
Why is this useful? Suppose you wanted to look at only the A items. With a filter, rather than having to scroll through thousands of items, you now can scroll through a much smaller and more specific list. Taking it a step further, if you added one more number filter to the Price column, you can select everything that’s less than ten dollars. Now, you only have 32 items that you can see very quickly and go through line by line if needed.
To remove all these filters, you can individually go through and choose clear filter. Or, you can also go back to the Sort and Filter button in the top right, and choose Clear. This resets everything to the way it was prior to the filter being applied.
In addition, you can use the Control+Shift+L shortcut to add or clear filters, but to make sure all your data is included, it is recommended that you select all of the data before entering the shortcut.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/sorting_and_filterin.