There are many built-in Excel tools to help with data management and the sorting and filtering features are among the best. The filter tool gives you the ability to filter a column of data within a table to isolate the key components you need. The sorting tool allows you to sort by date, number, alphabetic order and more. In the following example, we will explore the usage of sorting and filtering and show some advanced sorting techniques. Show For today’s example, we will use the following spreadsheet: As you can see, the order dates, order numbers, prices, etc. are all out of order. Let’s get started on running some sorting and filtering techniques. Sorting Data Let’s say you had the spreadsheet above and wanted to sort by price. This process is fairly simple. You can either highlight the whole column or even click on the first cell in the column to get started. Then you will:
The whole table has now adjusted for the sorted column. Note: when the data in one column is related to the data in the remaining columns of the table, you want to select Expand the selection. This will ensure the data in that row carries over with sorted column data. Filtering Data The filter feature applies a drop down menu to each column heading, allowing you to select specific choices to narrow a table. Using the above example, let’s say you wanted to filter your table by Company and Salesperson. Specifically, you want to find the number of sales Dylan Rogers made to Eastern Company. To do this using the filter you would:
Boom – you now have the exact number of sales Dylan Rogers made to Eastern Company. The Sort & Filter Tool In addition to the right-click menu sorting option and the Filter tool on the Data ribbon, Excel has a Sort & Filter tool that allows for custom sorting. In the following GIF, we can see how the Custom Sorting tool can be used to sort date ranges or price ranges. But notice how this example is either/or. What if you wanted to sort by date and by price? This where the Custom Sort option really comes in handy. After selecting your first sorting conditions, you can add a level to get event more accurate data: As you can see, Excel offers a variety of sorting and filtering tools to help you refine your data and keep it organized. We hope you found today’s tips useful. Now go out there and get your data sorted! Use Learn Excel Now to help with all your Excel questions and training needs. We’re not just experts in Excel, there is content, free resources, and training courses available for Word, Outlook and more. To delete a sorting level applied to a table, click the “Data” tab in the Ribbon. Then click the “Sort” button in the “Sort & Filter” button group. Next, select the sorting level to delete from the “Sort” dialog box. Then click the “Delete Level” button in the “Sort” dialog box to delete the selected sorting level. Then click the “OK” button.In this lesson, you will learn how to sort, group, and filter data in various ways that will enable you to most effectively and efficiently use spreadsheets to locate and analyze information. Sorting, grouping, and filteringDownload the example to work along with the video. A Microsoft Excel spreadsheet can contain a great deal of information. Sometimes you may find that you need to reorder or sort that information, create groups, or filter information to be able to use it most effectively. Sorting lists is a common spreadsheet task that allows you to easily reorder your data. The most common type of sorting is alphabetical ordering, which you can do in ascending or descending order. To sort in alphabetical order:
You can Sort in reverse alphabetical order by choosing Sort Z to A in the list. To sort from smallest to largest:
You can sort in reverse numerical order by choosing From Largest to Smallest in the list. To sort multiple levels:
The spreadsheet has been sorted. All of the categories are organized in alphabetical order, and within each category the unit cost is arranged from smallest to largest. Remember that all of the information and data is still here—it's just in a different order. Grouping cells using the Subtotal commandGrouping is a useful Excel feature that gives you control over how the information is displayed. You must sort before you can group. In this section, we will learn how to create groups using the Subtotal command. To create groups with subtotals:
To collapse or display the group:
To ungroup select cells:
To ungroup the entire worksheet:
Filtering cellsFiltering, or temporarily hiding, data in a spreadsheet is simple. This allows you to focus on specific spreadsheet entries. To filter data:
To clear one filter:
To remove all filters, click the Filter command. Filtering may look a little like grouping, but the difference is that now you can filter on another field if you want to. For example, let’s say you want to see only the vanilla-related flavors. Just click the drop-down arrow next to Item, then select Text Filters. From the menu, choose Contains because you want to find any entry that has the word vanilla in it. A dialog box appears. Type vanilla, then click OK. Now we can see that the data has been filtered again and that only the vanilla-related flavors appear. How can you tell which column a table is sorted by?Sort the table. Select a cell within the data.. Select Home > Sort & Filter. Or, select Data > Sort.. Select an option: Sort A to Z - sorts the selected column in an ascending order. Sort Z to A - sorts the selected column in a descending order.. What happens when a column is filtered?Filtered data displays only the rows that meet criteria that you specify and hides rows that you do not want displayed.
What is the difference between sorting and filtering data in Excel?You sort data to quickly organize your data and to find the data that you want. You filter data to display only the rows that meet criteria that you specify and hide rows that you do not want displayed, for one or more columns of data.
|