When you are presented with a long set of data listing in Excel, it is important to filter the data and get exactly the subset of data you need for reporting or analysis purpose. Some of the good examples of data sets where filter is required would be the following
- Account bank statements
- Invoice payments listing
- Creditors debt listing
Knowing how to use the filter functionalities in Excel important to filter or slice the data sets and get subset of data to meet specific information when required without altering the original set of data. It also cuts down the number of copy and paste which has the potential to alter the original data format and integrity.
Create an Excel Table.
In a typical Excel workbook file, column headings are data headings and each row represent a specific record. That’s why it is important to make sure the column headings reflect the data its contains. Filters are normally applied using column headings or names.
Once the column headings are correct, the next step is to make the data set as a table. Although data in Excel looks like it’s already in a table format because of the rows and columns, it’s not.
- Click in any cell in the data set
- Click Insert tab and under Tables command group, click Table or alternatively press Ctrl + T on the keyboard. This will show the Create table dialog box. Check the My table has headers check box and click OK.
3. Excel will add the Table Tool contextual tab onto the Ribbon. It will automatically add the filter buttons under each column.
4. Under Properties command group, delete the default table name “Table 1” and retype a descriptive table name. Press Enter to accept it.
5. Under Table Style Options command group, check the Total Row check box. This will enable you to get totals on columns that require totals to be displayed.
Now you are ready to apply the required filters on the data.
Apply Filters in Excel
Applying filters on a set data can be done in two ways. Through the column filter buttons already added when the table is created or inserting a Slicer and then use the column names on the slicer to filter it.
Table Column Filters
Click on the filter button in each column heading and then unselect the Select all check box. Then click the specific filters as shown in the example below.
The column filter button can also be used to sort the data either ascending (A-Z) or descending (Z-A) based on the column data.
Applying More than one Filters
If there is a need to apply more than one filter, it can be done by choosing the second column filter and select the required filters. Excel will automatically apply the filter based on filters selected in each column. Just make sure the right filters are selected from each column when applying multiple column filters.
Removing Filters
To remove the filters, click the filter button in each column and click Clear Filter from “filter name” option.
Filter Using Slicer in Excel.
Although, applying filter by column filters does the job, it has its setbacks. Main setback is looking through the filter name listing under each column filter option and trying to check or uncheck. Using a Slicer adds more visual element to the filter. It gives the filter listing as a floating list on top of the data set which can be moved and placed in a desired location. Also, set multiple selection and each selection option can be colored to distinguish the selected from those not selected.
Inserting Slicer
- Click in any cell in the Table data set to activate Table Tools contextual tab on the Ribbon.
- Click on Design tab under Table Tools
- From Tools command group, click on Insert Slicer. This will show the Insert Slicers dialog box. This is basically the list of data or column headings. From the dialog box check the required column names to appear on the slicer list, and click OK.
- The slicers appear on the screen. The slicers can be moved and placed in the desired location, usually on the side of the table. When a slicer is selected, the Slicer Tools contextual tab appears on the ribbon. Under Designs table Slicer styles can be applied to add colors to it.
Select Multiple Filters and Remove Filters in slicers.
At the top of the slicer just above the filter data listing is the Multi-Select (apply more than one filter under the same slicer) and Clear Filter buttons.
DELETE SLICERS
To delete the slicer, simply select the select and then press the Delete key. If you accidentally delete a slicer, you can always them back by pressing Ctrl + Z which is the shortcut for Undo.
Whether you use column filter or Slicers, filtering is a great way to sort through the data and narrow it down to get the exact information that you need. Filters can save your time and effort to get the specific information from the data set in a much faster way.