Filtering Techniques for Excel Data

Author: David H. Ringstrom

Users who are unaware of the Filter feature in Excel spend a great deal of time sorting data and staving off information overload. In this course, Excel expert David Ringstrom, CPA, shares a number of techniques you can use to quickly sift through large amounts of data in Excel. He’ll contrast the Table and Filter features, show how to filter data in pivot tables, and share several shortcuts. You’ll see how to remember filter criteria by way of the Custom Views feature and learn about the FILTER function that’s being rolled out in the Office 365 version of Excel.

David demonstrates every technique at least twice: first, on a PowerPoint slide with numbered steps, and second, in the subscription-based Office 365 version of Excel. David draws your attention to any differences in the older versions of Excel (2019, 2016, 2013, and earlier) during the course as well as in his detailed handouts. David also provides an Excel workbook that includes most of the examples he uses during the course.

Microsoft 365 is a subscription-based product that provides new feature updates as often as monthly. Conversely, the perpetual licensed versions of Excel have feature sets that don't change. Perpetual licensed versions have year numbers, such as Excel 2021, Excel 2019, and so on.

Publication Date: March 2023

Designed For
Users who work with large data sets in Excel and want to learn ways to streamline their filtering tasks.

Topics Covered

• Adding interactivity to pivot tables by using the Slicer feature for filtering in Excel 2010 and later
• Applying different filter settings with just a couple of mouse clicks using the Custom Views feature
• Avoiding the need to retype data or cumbersome formulas by way of Excel's Text to Columns command
• Creating a pivot table to transform lists of data into on-screen reports
• Displaying subsets of data dynamically by way of the new FILTER worksheet function
• Filtering based upon two or more conditions with the FILTER function in Excel 2021 and Microsoft 365
• Filtering lists based on cell color
• Filtering pivot tables to show fewer columns and/or rows of data
• Gaining control of long lists of data by filtering instead of sorting
• Identifying the feature conflict that arises when you utilize tables within Excel workbooks
• Learning how the Table feature empowers you to improve the integrity of Excel spreadsheets
• Learning how to control multiple pivot tables and charts instantly with the Slicer feature in Excel 2010 and later

Learning Objectives

• Identify the types of data that actions such as row deletions or formatting affect while filtering a worksheet
• Recognize valid filtering options in Excel
• Identify the keyboard shortcut that enables you to delete one or more rows or columns that you've selected

Level
Intermediate

Instructional Method
Self-Study

NASBA Field of Study
Computer Software & Applications (2 hours)

Program Prerequisites
Experience working with lists of data in Microsoft Excel.

Advance Preparation
None