Sorting data groups like data together. For instance, you could sort by zip code to find out how many letters were being mailed within a certain city. Filters narrow the amount of data by displaying only those items that meet the criteria of the filter. Filters are an effective way to view data because they display results instantly without actually changing the data. Filters simply manipulate what data is being displayed and once turned off, all data will be displayed.
The ease of using sorting and filtering data to provide detailed results, makes using these options one of the easiest ways to find information within an Excel spreadsheet.
Using Filters in Excel to Quickly Sort Data
Once basic information is entered into a spreadsheet, it can be sorted or filtered. Here is an example of some sample data depicting customer orders, the dates of the orders, the items ordered, and how many.
From this basic data, we can extrapolate different types of information. We will show how to display who is purchasing specific items, what was ordered on any specific date, and display all orders from a specific customer.
To sort data:
- Right-click on a column heading, hover over sort, then select A-Z or Z-A, or custom sort if desired.
- In this example the data is sorted by customer so it can be easy to see what items any particular customer has recently been ordering.
- To sort this data further, select custom sort to sort by multiple columns at once. Right click and hover over sort, then select "Custom sort".
- In the Sort box, modify the first sort level.
- Click "Add Level" to add a secondary item to sort by.
- In the "Then by" level, chose what to sort by and how to sort it.
- Here is what the data looks like after sorting first by customer then by item purchased.
- Right-click on the name of a column and hover over filter, then select "Filter by Selected Cell's Value".
- Without any data selected, there is no data to display but filter icons have been added to each column.
- Click on the filter icon next to any column, and select the criteria for what data to display and click OK.
- In this example, the filter next to Date was selected and October 9th was selected. Notice the number of the rows in the results are the actual row numbers where the data lives. This shows how certain data is filtered out.
- Click on the filter next to the column for the amount sold.
- Hover over "Number filters" and select "Between".
- In the Custom AutoFilter window, enter the numbers relative to the data to display.
- Here is what the data looks like after being filtered in this example.
Sorting data groups certain information together so they can be assessed. Filtering data provides granular control over what data is displayed and the most specific data can quickly and easily be found with the correct filters applied. Once done, simply remove filters and all of the original data is displayed.
As always, knowing how to get the information you need out of the data you already have, is more efficient than starting over!