Supporting the entrepreneurial spirit

Big Ideas for SMBs

BLOG

Excel Filters for Beginners, Part 2

Excel Filters for Beginners, Part 2

By Olivia Novak

If you are starting out on Microsoft® Excel® or want a quick review, this article builds on the basic filter and limit concepts explained in Microsoft Excel for Beginners. The same concepts also apply to the reporting features in many ECi software packages.

Filters: Diving Deeper

To review, filtering in Excel enables you to create a data subset from which you can drill down and find the precise information you need.

To filter, follow these steps:

  1. Click in any cell in your spreadsheet.
  2. Using the toolbar, navigate to the Home tab.
  3. In the Editing group on the far right, click Sort and Filter, then Filter.

Note: Filters will have all cells selected by default, indicated by checkmarks on the left of the boxes within the filter.

When filters have been applied, you can set inclusive and exclusive limits, as well as limits at extremes. The shortcut to Filter is holding down the Ctrl key, then the Shift key, while typing L on your keyboard. You can also format cells based on limits to help certain values stand out. In this tutorial, we will explain these terms and how you can implement them in Excel.

Inclusive Filter Limits

Inclusive filters enable you to filter out data that does not meet your criteria, or conversely, to include only data that does meet your criteria. The steps for setting inclusive filter limits are simple:

  1. Click on the down arrow of a column.
  2. Hover over Number filters.
  3. Set the desired values, such as Between 200 AND 500, so that only values between 200 and 500 are included in the visible column.

Notes: The filter only applies to a column if there are multiple columns. You can type in numbers without any formatting, such as dollar signs.

If you have a large set of data, you may want to use the Search box in the filter or the Number Filters > Equals option instead. The Equals option is especially helpful if you are looking for a smaller number or text value that may be contained in other cells if you are using the Search box in the filter. For example, if we use the Search box on column D and type 55, then both the cell D7 and D12 are included.

Exclusive Filter Limits

Exclusive filters enable you to limit the column to data that does not meet a certain criterion. For example, you can uncheck the box for zero so that cells with a zero value are not shown.

Undesired Cell Included with Search

To exclude rows from being included in your filter, follow these steps:

  1. Right-click the row.
  2. Click Hide to hide it, as in the Subtotal row 12 below.

Notes: The row will still be there if you need to go back to unhide it, but it will not be visible onscreen while hidden. Columns and sheets can also be hidden in the same way. In the example below you may not want to include the Subtotal row in a filter if you want to work only with individual cells.

Subtotal Row Hidden

Now we can search for numeric values, such as cells containing a 55, with the Subtotal row excluded from our search results on the column.

Date Filters

Excel automatically provides Date filters for cells formatted as Dates. For example, you can follow these steps:

  1. Click on the down arrow of a column.
  2. Hover over the Date filter,
  3. Move right over All Dates in the Period,
  4. Select a desired calendar month or quarter.

Note: You can also click on any of the filters listed to automatically to go to a specific date range, such as This Month.

Options that are followed by three periods(…) will direct to a new prompt to offer you additional Custom Options. On the right side of the Custom Options is a Calendar icon that you can click on to navigate through to help prefill the Custom field.

If you would like to see all of your data again, follow these steps:

  1. Click the dropdown arrow.
  2. Click Clear Filter from individual columns.

Or

  1. In the Home tab within the Editing group.
  2. Click Sort and Filter.
  3. Then click Clear to clear filters from all columns.

Knowing the types of limits you can apply to filters enables you to quickly determine the best method to narrow down your data for further analysis.

Olivia Novak

About the author

Olivia Novak is a Customer Support Engineer for ECi DDMS with broad experience in various applications. She holds a Master’s of Science in Information Technology and Management. In her spare time she reads and crafts items such as latch hook rugs.

facebook-icon facebook-icon linkedin-icon linkedin-icon twitter-icon twitter-icon blog-icon blog-icon youtube-icon youtube-icon instagram-icon instagram-icon Bookmark this page Google +