What does filtering data in a spreadsheet do?
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. The filter in excel helps display relevant data by eliminating the irrelevant entries temporarily from the view. The data is filtered as per the given criteria. The purpose of filtering is to focus on the crucial areas of a dataset. For example, the city-wise sales data of an organization can be filtered by the location. Hence, the user can view the sales of selected cities at a given time. A filter is necessarily required when working with a huge database. Being a widely used tool, the filter converts a comprehensive view into an easy-to-understand one. To apply filters, the dataset must contain a header row which specifies the name of every column. Table of contentsYou are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked How to Filter in Excel?You can download this Filter Column Excel Template here – It is good to work with filters because they fit our needs the way we want to. In order to filter data, select the entries to be visible and deselect the rest of the items. The three methods to add filters in excel are listed as follows:
Let us consider a dataset to go through the three methods of adding filters. The following table shows the invoices issued to the buyers of different cities. We want to filter the data using different methods. Method 1: With Filter Option Under the Home tabIn the Home tab, there is a “filter” option under the “sort and filter” drop-down of the “editing” section, as shown in the following image. Step 1: Select the data and click “filter” under the “sort and filter” drop-down. Step 2: The filters are added to the selected data range. The drop-down arrows, shown within the red boxes in the following image, are filters. Step 3: Click the drop-down arrow of the column “city” to view the different names of the cities. Step 4: To see the invoice values of “Delhi” only, select “Delhi” and uncheck all the remaining boxes. Step 5: The data for the city “Delhi” is filtered and displayed in the following image. Method 2: With Filter Option Under the Data tabIn the Data tab, there is a “filter” option under the “sort and filter” section, as shown in the following image. Method 3: With the Shortcut keyThe keyboard shortcutsKeyboard ShortcutsAn Excel shortcut is a technique of performing a manual task in a quicker way.read more are a good way to speed up the daily tasks. Select the data and add the filter using either of the following shortcuts:
Note: The preceding shortcuts for adding filtersShortcuts For Adding FiltersUsing sorting and filtering, we can see the data category wise. With filtering data quickly you can easily navigate through menus or clicking through a mouse in less time.read more are toggle keys. Repetitive pressing helps to turn on and turn off the filters. How to Add Filters in Excel?We can filter numbers using advanced techniques. Let us consider some examples to understand the working of filters in Excel. Example #1–“Number Filters” OptionWorking on the data under the preceding heading (methods of filtering in Excel), we want to apply the following filters: a. To filter column B (invoice value) for numbers greater than 10000 b. To filter column B for numbers greater than 10000 but less than 20000 Let us go through the two cases one by one. a. Filter numbers greater than 10000 Step 1: Open the filter in column B (invoice value) by clicking on the filter symbol. Step 2: In “number filters,” choose the “greater than” option, as shown in the following image. Step 3: The “custom autofilter” box appears. Step 4: Enter the number 10000 in the box to the right of “is greater than.” Step 5: The output displays the invoice values greater than 10000. The symbol within the red box is the filter icon. It indicates that the filter has been applied to column B. b. Filter numbers greater than 10000 but less than 20000 Step 1: In “number filters,” choose the “greater than” option. Step 2: In the “custom autofilter” box, select “is less than” in the second box to the left-hand side. This is shown in the following image. Step 3: Enter the number 10000 in the box to the right of “is greater than.” Enter the number 20000 in the box to the right of “is less than.” Step 4: The output displays the invoice values greater than 10000 but less than 20000. Example #2–“Search Box” OptionWorking on the data under the preceding heading (methods of filtering in Excel), we have replaced the first column (city) with product IDs. We want to filter the details of product ID “prd 1.” The steps are listed as follows: Step 1: Add filters to the columns “product ID” and “invoice value.” Step 2: In the search boxSearch BoxA search box in Excel finds the needed data by typing into it, then filters the data and displays only that much info. When working with large datasheets, this simple tool may save a lot of time.read more, enter the value that is to be filtered. So, enter “prd 1.” Step 3: The output displays only the filtered value from the list, as shown in the following image. Hence, we can see the invoice value of the product ID “prd 1.” Option while you Drop Down the Filter Function
For example:
The Techniques of Filtering in ExcelThe following techniques must be followed while filtering data:
Frequently Asked Questions1. What are filters and how to add them in Excel? Filtering is a technique which displays the required information and removes the unwanted data from the view. It helps the user focus on the relevant data at a given time. 2. How to apply filters to one or more columns? The steps to apply filters to one or more columns are listed as follows: 3. How to use filters in Excel? The filters can be applied to numbers, text values, and dates. These cases are discussed as follows: Recommended ArticlesThis has been a guide to Filter in Excel. Here we discuss how to use/add filters in excel along with step by step examples and a downloadable template. You may learn more about Excel from the following articles –
|