Excel For Mac 2008 How To Filter On A Column

by

Just as with any other spreadsheet application, Apple Numbers offers users different ways to manage and sort your data so it becomes easier to handle, especially when there’s a lot of it.

For this, Numbers sports filtering and sorting features that have taken a simpler, more straightforward approach in the latest versions of the app, mainly because of Apple’s idea of unifying their productivity apps across all devices and platforms.

Let’s take a closer look at these essential features of Numbers.

In Excel, you can sort numbers, text, weekdays, months, or items from custom lists that you create. You can also sort by font color, cell color, or icon sets.

So, speaking of design flaws, Excel in Microsoft Office 2016 for the Mac tries to present 'intelligent' filtering for a column based on the data in the column. I'm talking about the filter menu in the filter 'Choose One' dropdown, entries such as Equals, Does Not Equal, Begins With, Ends With, etc. For example, if you’re looking at a table of possible monthly mortgage payments for a loan with various interest rates, you can filter the table to show only the loans you can afford. Note: You can also quickly filter a table. Select a column or cell, click the arrow on the column’s border, then choose an option under Quick Filter.

Note: While you are at it, you might also want to check out our entries on the basics of Numbers and a few tips on how to make it more productive for you.

Sorting Data

Sorting large chunks of data can come very handy to get your Numbers spreadsheet in order and get the big picture of all your information in it. Sorting allows you to change the column by which your table is organized.

To do it on Numbers, first click on the Sort & Filter button at the top right of the window to bring in the relevant panel.

From there, you can click the Add a Column… button to select one column to be sorted. Once you do, you will be able to choose if you want the data to show up as ascending or descending.

That’s not all though. If you want to sort your data by more than just one criteria (for example, by a name and a number) you can do so by just adding another column to the mix.

Additionally, you can choose to sort the entire table or just the selected column, although choosing the latter might disarrange your table.

Filtering Data

When it comes to managing data on spreadsheets, Filtering is perhaps the most important feature you can make use of, since as its name states, it allows you to focus on specific sets of your data depending on the criteria you select.

The filtering option in Numbers is located in the same place as the Sorting option. Just click on the Filter tab to start using it.

To do so, first click on the Add a Filter… button and select the column you want to filter.

Once you do, you have to then choose a rule to filter that column’s data. For this, Numbers presents a pretty intuitive Rules Panel with five main tabs each representing a category of data to filter.

As seen below, each of these categories sports a series of rules. Once you select one, you have to specify the range of the filter before applying it. In this example, I decided to filter the Budget column (with the letter ‘F’) for it to show only amounts larger than 200.

Similar rules can be applied to filter text, dates and other sorts of data. You can also apply multiple filters to different columns of your table, allowing you to really narrow down the information you need to check.

Conclusion

As you can see, sorting and filtering your data on Numbers is tremendously useful. And if you’ve used these functions before on Excel, Numbers makes it even easier to do so with its new approach.


The above article may contain affiliate links which help support Guiding Tech. However, it does not affect our editorial integrity. The content remains unbiased and authentic.Also See#numbers #productivity

Did You Know

Notion was founded by Ivan Zhao.

More in Mac

A Guide on Fixing Apple Music on Mac Not Syncing With iPhone

Excel AutoFilter is a simple Excel feature for filtering lists. As a consultant or analyst looking through data for answers you are going to need how to filter.

The Excel advanced filter has far more powerful filtering and extracting capability you will want to use with To Do Lists, Contact Lists, Project Task Lists, Inventory Lists, and more.

These tips begin with the simple AutoFilter in Excel and go on to show you the secrets of the Excel advanced filter. With the advanced filter in Excel you can filter and extract data using complex AND and OR criteria impossible with AutoFilter.

Are you an Excel power-user? Jump to the advanced filter tips starting with Tip 11.

Use an Excel advanced filter to create AND and OR criteria unavailable with the Excel AutoFilter. The Excel advanced filter can also extract filtered data into columnar reports.

Click here to download the Excel AutoFilter and advanced filter example file.

Do you know someone who works with Contact Lists, Inventory Lists, To Do or Project Task Lists? Forward this article to them and earn Excel karma points.

“In the beginner’s mind there are many possibilities, but in the experts there are few.”Shunryu Suzuki (1905-1971)

Click here to learn how to create an Excel list or Excel Table.

Click here to learn how to create an Excel checklist.

Click here to learn how to create an Excel Gantt chart from a task list with scheduled dates.

1. Enabling Excel AutoFilter in Lists or Tables

AutoFilter arrows display to the right of header labels.

The Excel AutoFilter displays down arrows to the right of each header label in a list or Excel Table. Clicking one or more of the filter arrows displays a dialog box you can use to filter your list or Table.

Excel Tables immediately display AutoFilter arrows when they are created. You can turn the AutoFilter arrows off in an Excel Table when you need to.

To apply an Excel AutoFilter to your list,

1. If the list is surrounded by blank cells, select a cell in the list. If the list has filled cells touching the list border, then select the list headers and all data cells.

2. On the Data tab, in the Sort & Filter group, click Filter. The AutoFilter arrows will display.

AutoFilter arrows that have a dot next to them indicate a filter criterion has been applied.

Click here to learn more about the basics of Excel lists and Excel Tables.

2. Using the Excel AutoFilter to Filter Lists or Tables

To apply one or more AutoFilters to an Excel list or Excel Table column,

1. Click the AutoFilter arrow on the header label you want to filter.

2. The AutoFilter menu gives you options to sort or filter by the contents of that column.

3. AutoFilter menu options change depending upon the column’s contents. In this figure, the Region column contains text data, so only Text Filters are available in a side menu. The types of filters available are Number, Text, Date, and Color. The conditional criteria, such as Equals, Contains, etc., change to match the type of filter.

4. You can apply AutoFilters to multiple columns. The AutoFilters in all columns must be TRUE for data to display. (If you need to work around this limit, use the Excel advanced filter described starting at tip 11.)

Filters that contain criteria show a dot next to the filter arrow in the header label.

To remove a filter from a column, click the AutoFilter arrow, then select Clear Filter.

3. Using Excel AutoFilter to Find Exact Data

To search for an exact match to data, use the checkboxes listed at the bottom of the AutoFilter menu. This list of checkboxes shows data unique to the column. Select checkboxes of the data you want to be displayed and clear checkboxes of data you want filtered.

4. Using Excel AutoFilter to Show the Top n Items or Percent

Top 10, Bottom 20, Top 25%, are all the type of lists items that often go in reports. Although Excel calls this the Top 10 AutoFilter, it does much more.

Click here to download the Excel AutoFilter and advanced filter example file.

Excel

To display the top n or bottom n items or percent,

1. Click the AutoFilter arrow in the header of a numeric column.

2. Select Number Filters, Top 10.

3. In the Top 10 AutoFilter dialog box select whether you want to display the Top or Bottom of any number of Items or Percent.

4. Enter the number or percent to display. Select whether you want Items or Percent, then click Ok.

This only works on columns with numeric data. If you don’t see this AutoFilter, check to make sure the column contains only numbers.

5. Using AutoFilter Excel with AND and OR Conditions

Excel AutoFilter can filter with either the AND or the OR condition. Selecting many of the AutoFilter menu items will display the Custom AutoFilter dialog box with the appropriate conditions preselected to match your menu selection.

If you want to immediately display the Custom AutoFilter dialog box, select Custom Filter after selecting the AutoFilter arrow and the Filter menu item.

Custom AutoFilter enables you to choose,

  • a single condition by completing only one criterion
  • an AND criteria where both criteria must be TRUE
  • an OR criteria where either one criteria or the other must be TRUE

6. Using AutoFilter Excel to Filter for Text Anywhere in a Cell

When you are analyzing open text responses to surveys, filtering inventory lists with scrambled names, or searching contact lists for near matches, you can use the Contains criteria in Excel AutoFilter to help.

Click here to download the Excel AutoFilter and advanced filter example file.

To use the Excel AutoFilter to find and filter text within longer strings of text,

1. Click the AutoFilter arrow in the header of a text column.

2. Select Text Filters, then Contains. This displays the Custom AutoFilter dialog.

3. If you have one criterion, click Ok to filter the list.

4. If you want to include “cut” but want to exclude special cases that contain other text you must enter another criteria on the second line. For example, to find “cut” but exclude instances that also contain “Lite”, click And, and select Does Not Contain, and type “Lite.”

Excel AutoFilter can use two AND or OR conditions.

5. Choose Ok.

7. Using AutoFilter Excel for Numeric Ranges

Number or date ranges frequently used in Excel AutoFilter to limit reports.

To use Excel AutoFilter for a numeric or date range,

1. Click the Excel AutoFilter arrow in a number column. Make sure the column contains only numbers. If the column contains text, other than the header, the filter will not work.

2. For a numeric column, click Number Filters, Between to display a Custom AutoFilter dialog preconfigured for numeric ranges.

Selecting Between from the AutoFilter menu open the Excel AutoFilter dialog preconfigured for a range.

3. Select from the left drop-downs how you want the range to start and end.

4. In the right drop-downs either type the numeric limits or select them from the drop-downs.

8. Using AutoFilter Excel for Dates and Date Ranges

Date ranges are one of the most frequent uses for filtering lists.

1. Click the Excel AutoFilter arrow in a date column. Make sure the column contains only dates. If the column contains text, other than the header, the filter will not work.

2. Click Date Filters.

3. You can select predefined date ranges from the sub-menu, for example, Last Week, This Quarter, Last Month, etc. or select Custom Filter.

4. If you need a custom range, select Custom Filter to display the Custom AutoFilter dialog box. Select start and end dates for the range by clicking the calendar icons at the right and selecting start and end dates.

5. Click Ok.

9. Using Wildcards with an Excel AutoFilter

Excel AutoFilter works with wildcards. You can use wildcards as substitutes for an individual character or string of characters in text filters.

Filter

Use an * (asterisk) as a wildcard for multiple text characters. For example, *cut, will filter for Lite Cut and Heavy Cut.

Use a ? (question mark) as a wildcard for any single text character. For example, st??r, will filter for steer and stair.

10. Using Slicers as Filter in Excel

When you need a quick, easy-to-use filter, but one that filters using only large categories, use a Slicer. Slicers are boxes that display on your worksheet. They contain buttons that display a list of unique contents for the column you want as a filter. Click one of the buttons and the list or Excel Table is filtered.

To select multiple Slicers, hold the Ctrl key as you click a slicer.

Click here to learn how to add a Slicer.

11. Creating the Excel Advanced Filter

The Excel AutoFilter is easy to display and use, but it is cannot create some of the more complex filters. With the Excel Advanced Filter you can create very complex filters as well as extract filtered data into a new list.

Click here to download the Excel AutoFilter and advanced filter example file.

The advanced filter in Excel uses three ranges, the list or Excel Table, the Criteria range containing logical formulas that define the filter, and an optional Extract range. This figure shows the list or Table on the left, a Criteria range in green, and the optional Extract range on the right.

Extracted data does not have to be in the same column order as the original list or Excel Table so it is an excellent method of creating lists for reports.

To set up an advanced filter in Excel,

1. Create a normal list or Excel Table.

2. Create a Criteria range that includes header labels at the top that are exactly the same as the list or Table.

  • Create the Criteria range so that it is on different rows than the list or Table so the Criteria range is not hidden when the list is filtered.
  • Include as many blank rows below the header row as you need for the filter criteria.
  • Include one blank row for each unique AND condition. For example, Units>200 AND Units<=250 requires one row with two cells with the Units header label.
  • Include a blank row for each OR condition. For example, Region=West OR Region=East requires two rows with one Region header label.

To apply the advanced filter in Excel,

1. Select a cell in the list or Excel Table.

2. On the Data tab, in the Sort & Filter group, click Advanced to display the Advanced Filter dialog box.

The figure shows the Advanced Filter dialog box after the filter has been applied. An Extract range has not been entered.

3. Select Filter the list, in-place.

4. Select the List or Excel Table range. If you have assigned the list or Excel Table a range name, enter the range name.

Excel For Mac 2008 How To Filter On A Column Diagram

5. Select the Criteria range including the header labels and all rows containing criteria.

6. Click Ok to filter the data in place.

The cell references or range names, for the list or Table and Criteria are remembered and are automatically entered the next time you display the Advanced Filter dialog box. If you are using multiple lists, Criteria, or Extract ranges be sure to change the ranges to match the worksheet you are working on.

12. Removing an Advanced Filter

To remove a filter, clear the cells under the header labels in the Criteria range and rerun the Advanced Filter.

13. Using the Excel Advanced Filter with AND Conditions

Use the AND criteria when ALL criteria in the AND condition must be TRUE. The AND condition is most often used with date or number ranges.

An advanced Excel filter that uses AND conditions may have one row, but will use multiple header labels for the AND criteria, for example,

Units>=200 AND Units<250

The Units header label is used once for each Unit criteria. The header label must be spelled exactly, so it is best to copy it.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

Excel filter formulas in the Criteria range must be entered in quotes. The formulas in this example are,

=”>=200”

=”<250”

The Excel filter formula in quotes must evaluate to TRUE for a row to be selected by the filter.

14. Using the Excel Advanced Filter with OR Conditions

Use the OR criteria where one criterion OR the other must be TRUE. OR criteria are most often used when filtering for one text item OR another.

An advanced Excel filter that uses OR conditions will have more than one row, with each row being one of the items being filtered for, for example,

Region=North OR Region=South

The Region header label is used once in the Criteria range for an OR. The header label must be spelled exactly as it is in the List or Table, so it is best to copy it.

An Excel advanced filter uses two rows for an OR criteria.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

Excel filter formulas in the Criteria range must be entered in quotes. But, exact text used as criteria can be entered directly, for example,

North

South

15. Using the Excel Advanced Filter with AND and OR Conditions

An Excel advanced filter can create very complex criteria for filtering or extracting data. The AND and OR criteria can be independent or dependent upon each other.

Click here to download the Excel AutoFilter and advanced filter example file.

Advanced filters in Excel often use combinations of AND and OR criteria that involve many rows. For simplicity, this example only uses two rows in the Criteria range, but the advantage of an advanced filter in Excel is that it can have many rows and criteria.

An advanced Excel filter using OR criteria will have more than one row.

In this figure, the filter has already been applied and then the Advanced Filter dialog box redisplayed.

In this figure, the Region header label is used with the text East and West under it to create an AND criteria. The Units header label in the Criteria range is repeated twice to create an AND criteria for a numeric range.

The example shows the list after filtering with the Advanced Filter dialog box redisplayed. The two Criteria rows translate in English to,

Rows with dates on or after April 1, 2017 AND containing East regionsandRows containing West regions AND Units greater than or equal to 200 and less than 250.

The Excel filter formulas for this are,

Cell

I7

K7

K8

L8

M8

16. Extracting Data to Create Custom Reports from Excel Lists or Tables

Extracting a filtered list is very useful for creating a second list for reporting on data meeting your criteria. It can be used with To Do lists, Contact lists or Tables, inventory lists, or lists of any type. For example, you may need a list of all employees who need recertification, a list of all vehicles having more than 100,000 miles, all sales reps who have sold more than quota in the Western region, and more.

If you can filter the list, then you can extract it to create a new list. Here’s how,

1. Create your list or Excel Table.

2. Create your Criteria for an advanced filter.

3. Test the criteria using advanced find to make sure it displays only the rows you want to be extracted.

4. Enter header labels in the order needed for the report. You can use only header labels you need in any order you want. You can create the report on another worksheet.

Warning! Create your extract area and enter your report headers in an area that will not have rows hidden if the list or Table is filtered.

5. Select a cell in the list or Excel Table, then on the Data tab, in the Sort & Filter group, click Advanced Filter.

6. Select Copy to Another Location.

Excel For Mac 2008 How To Filter On A Column For A

7. Enter the list or Table range and the Criteria range.

8. Enter the header labels range in the Copy To area.

9. Click Ok.

17. Creating Reports with Columns in Custom Order

You can put the header labels in any order in the Extract range. This gives you the ability to arrange your report data as you need.

Also, you can leave out header labels for data you do not need in the report. If you do leave out header labels in an extract it may create duplicate rows. To prevent duplicates, select the Unique records only checkbox in the Advanced Filter dialog box.

18. Filtering Duplicates in an Excel List or Excel Table

If you have a list from which you want to remove duplicates, click inside the list, then on the Data tab, in the Data Tools group, click Remove Duplicates. This permanently removes duplicate rows.

If you want to use Excel to create a second list with duplicates removed, then use the Advanced Filter dialog box to create an extract, as described in tip 16, and select the Unique records only check box.

19. Troubleshooting an Excel AutoFilter or Excel Advanced Filter

Here are a few troubleshooting tips to help when your Excel filter is not working.

  • Text in numeric or date columns, except for the header label, limits Excel’s filter to text filters. If you attempt to filter a column you think has numeric or date data, but the filter choices are for text filtering, then the text is somewhere in the data.
  • Do not confuse the Boolean logic AND and OR conditions for the human language “and” and “or.” They are not the same. The logical AND condition means that both criteria must be TRUE for the filter to work. The logical OR condition means that either one criteria or the other can be TRUE for the filter to work.
  • Numbers and dates use an AND condition for a range, for example, Units>200 AND Units<=250.
  • Text data uses an OR condition when you are filtering for multiple items in the same column, for example, Region=West OR Region=East. A Region cannot be both West AND East at the same time.
  • Text filtered with Contains may use an AND condition, for example, Product contains “brown” AND Product contains “jersey”.
  • If you are using an advanced filter and the Criteria range disappears when you filter, then some or all of its rows are being hidden by filtered rows in the list or Table. Move the Criteria range so it is not on the same rows as the list.
  • Advanced filters in Excel create range names Criteria and Extract. When opening the Advanced Filter dialog box to filter a new list or use new criteria, make sure the range in the dialog box matches the range on the worksheet you want.

Click here to download the Excel AutoFilter and advanced filter example file.

Excel For Mac 2008 How To Filter On A Column In Excel

Related Links

Click here to learn how to create an Excel list or Excel Table.

Click here to learn how to create an Excel checklist.

Click here to learn how to create an Excel Gantt chart from a task list with scheduled dates.