We sorted numbers in the previous video. You can also sort text and dates, either alphabetically, by format (such as color), or by a custom list you create.
Sort by specifying criteria
Use this technique to choose the column you want to sort, plus other criteria, such as font or cell colors.
-
Select a single cell anywhere in the range that you want to sort.
-
On the DATA tab, in the Sort & Filter group, click Sort. The Sort dialog box appears.
-
In the Sort by list, select the first column on which you want to sort.
-
In the Sort On list, select either Values, Cell Color, Font Color, or Cell Icon.
-
In the Order list, select the order that you want to apply to the sort operation — alphabetically or numerically, ascending or descending (that is, A to Z or Z to A for text, or lower to higher or higher to lower for numbers).
Want more?
We filtered by unchecking and checking values in the Sort and filter data video.
There are many other powerful ways to filter.
If a column contains blank cells, you can filter for cells that are blank and those that aren't.
Click any cell in the range or table. On the HOME tab, click Sort & Filter, and click Filter.
Click the down-arrow for the column. This is also referred to as the AutoFilter. Uncheck (Select All) and check (Blanks).
(Blanks) won't be an option if there aren't any blank cells in the column. Click OK.
And only the row with a blank value in the Code column is displayed.
To display cells that aren't blank, leave all options checked except (Blanks).
You can also use criteria to filter. Click the AutoFilter for the column you want to filter.
Since the Code column contains only numbers, we get the Number Filters option. When I point to it, there are a lot of criteria.
Click one, such as Greater Than. Criteria will be different for different types of data, such as text.
Type a value, such as 50000, click OK, and only rows where the value in the Code column is greater than 50000 are displayed.
You can apply an AutoFilter to multiple different columns.
Click the AutoFilter for a different column, such as Category.
Configure the filter for this column, such as unchecking Drinks.
Click OK. And now multiple filters are applied to the data.
Down here, Excel tells us how many records meet all the criteria, out of how many total records there are – in this case, 2 of 9 rows.
There are specific criteria filters for text. There are also specific criteria filters for dates.
Dates has a huge number of options. I click a criterion, such as After.
In the Custom AutoFilter dialog, enter a date, such as 5/1/2013.
Click OK, and only rows where the Last Order value is after 5/1/2013 are displayed.
You can also AutoFilter by cell and font color. Click an AutoFilter, point to Filter by Color.
This won't be an option if there aren't multiple cells or font color formats in the column. Click an option.
I want to see the products that have been discontinued, so I click the red cell color.
And only rows where the cell in the Discontinued column is formatted with a red fill are displayed.
To remove the filter for a column, click the AutoFilter for the column, and click Clear Filter From.
To turn off AutoFilters completely, click the Sort & Filter button on the HOME tab, and click Filter.
Up next, Advanced filter details.
