How to Filter Data in Excel for Mac

Edited 1 week ago by ExtremeHow Editorial Team

Microsoft OfficeExcel for MacData filteringAnalysisAppleMacBookSpreadsheetsOffice productivityInformation managementCustom viewsAdvanced functionsExcel features

Filtering data in Excel is a powerful tool that allows us to see specific rows in our spreadsheets while hiding the rest. When working with large datasets, filters can make it easier to focus on the really important data without the distraction of unnecessary information. In this guide, we'll walk you through the steps involved in filtering data using Excel for Mac. The instructions here are easy to follow and ensure that you can apply the filters effectively.

Understanding the basics of filtering

Before moving on to the filtering process, it is important to understand what filtering does and how it can be used. Filters in Excel allow you to display only rows that meet specific criteria. This is especially helpful when dealing with large tables where scanning through rows manually is inefficient.

Filtering applies to columns within a table or range of data. You can filter by text, numbers, or dates, and use single or multiple criteria to further refine the results.

Applying filters

To apply a filter in Excel for Mac, follow these steps:

1. First, open the Excel file that contains the data you want to filter.
2. Select the data range or place your cursor inside the data if the data is in table format.
3. Go to Data tab in the Excel ribbon at the top of the screen.
4. Find Sort & Filter group and click Filter icon. This will add drop-down arrows to the headers of each column in your dataset.

At this point, your table is ready to be filtered. Each column header will now have a drop-down arrow that you can click to specify filter criteria.

Using the filter drop-down menu

Clicking the Filter drop-down arrow gives you different options depending on the type of data in the column. You can use this menu as follows:

Filtering text

For columns with text data:

• Click the drop-down arrow on the column header you want to filter.
• By default, you'll see a list of unique text entries in your column. You can check or uncheck these boxes to show or hide specific text entries.
• To use more sophisticated criteria, go to Text Filters. You can apply conditions like Contains, Does Not Contain, Begins With, Ends With, etc.

Text filtering example

Suppose you have a list of employee names and you want to filter only those whose names begin with "J". You would select the column with names, go to Text Filters, and choose Begins With, then enter "J".

Filtering numbers

For columns with numeric data:

• Click the drop-down arrow on the column header.
• You can select or deselect numbers from the list, similar to text filtering.
• Use Number Filters for advanced criteria, allowing options like Greater Than, Less Than, Between, etc.

Example of filtering numbers

If you need to see data for more than 1000 sales figures, you would select the specific column, go to Number Filters, click Greater Than, and input 1000.

Filtering dates

For columns containing dates:

• Select the drop-down arrow in the Date column.
• You can choose specific dates directly from the list.
• Alternatively, use Date Filters for options like Before, After, Between, etc. to effectively filter date ranges.

Example of filtering by dates

Let's say you need to filter data for the month of March 2023. You would click the Date column, go to Date Filters, select Between, and enter the date range from March 1, 2023 to March 31, 2023.

Cleaning the filter

Once you've filtered your data, you may want to view the full dataset again in the future. To clear the applied filters, you can:

1. Return to Data tab in the ribbon.
2. In Sort & Filter group, click Clear.
3. This action will remove all filters and your entire dataset will be displayed once again.

Advanced filtering options

Excel for Mac also allows more advanced filtering through custom criteria. For example, you can use logic conditions such as AND or OR by setting multiple criteria in a text, number, or date filter.

Custom autofilters

To use a custom filter:

1. Click the Filter drop-down arrow on the column you want.
2. Select Number Filters, Date Filters, or Text Filters, as applicable.
3. Select Custom Filter (if it's not directly available, it may appear under the menu).
4. A dialog box will appear where you can set specific criteria using AND or OR.

Custom filtering example

Let's say you want to filter rows where the sales in April 2023 are between 1000 and 5000. You can set a custom number filter of greater than 1000 and less than 5000 in the Sales column, and also set a date filter to April 2023 in the Date column.

Filter troubleshooting

Sometimes, filters may not work as expected. Common problems include:

• Grouped rows or columns cause filters to be hidden.
• Non-continuous data range — Make sure your dataset is completely continuous with no empty rows or columns.
• Check if shared workbooks or protected sheets cause the Filter button to appear grayed out.

Make sure your workbook is not in a mode that disables filters, and always verify the integrity and format of your data before applying filters.

Conclusion

Filtering data in Excel for Mac is an indispensable skill when managing large data sets. By applying the proper filters, you can gain valuable information and streamline your data processing tasks. Whether you are dealing with text, numbers, or dates, the filtering feature in Excel for Mac provides versatile solutions to your data analysis needs. Practice filtering on various datasets regularly to become more efficient and discover the potential of this remarkable Excel functionality.

  Share This Article

If you find anything wrong with the article content, you can request an update