word-excel-access-help.com logo
Excel Filter
Word 2007
Excel 2007
Access 2007
Word xp
Excel xp
Access xp

Excel Filter
Additional Resources

Excel Filter

Excel Filter

Excel Filter | Microsoft Excel XP | Page 4

Microsoft Excel XP logo
Learn step-by-step how to use the Excel AutoFilter command. Also learn how to use the Advanced Filter to suit your every needs. Learn to do an Excel filter the easy way.

You can stare at you data for hours on end and still gain nothing for your time.

Spreadsheet data is often not much good unless you can analyze it. If you're familiar with some of the actions that you can perform on a database, managing and analyzing your data in Excel is within your grasp.

And even if you've never even heard of a database, you can still filter lists to your heart's content.

Excel Office Helper What's on this page...
Using AutoFilter
Using Custom AutoFilter
Using Advanced Filter
Using criteria for text

Using criteria for values

Using Criteria For Text

Excel Filter your text using Wildcards such as ?(question mark) which filters any single character, for example, sm?th finds "smith" and "smyth"
Or, use the *(asterisk) which filters any number of characters, for example, *east finds "Northeast" and "Southeast"

You can filter text using the following characters:

Criteria Effect
>K Text that begins with L through to Z
<>C All text, except text that begins with C
January Text that matches January
Sm* Text that begins with Sm
s*s Text that begins with s and ends with s
s?s Three-letter text that begins with s and ends with s

The text comparisons are not case sensitive.  For example, si* matches Simon as well as sick.

Using Criteria For Values

To create a formula that returns results based on filtered criteria, use the Excel database worksheet functions.

For example, you can create a formula that calculates the sum of values in a list that meet certain criteria. Set up a criteria range in you worksheet and then enter a formula such as the following:

=DSUM(ListRange, FieldName, Criteria)

In this case, ListRange refers to the list, FieldName refers to the field name cell of the column being summed, and Criteria refer to the criteria range.

The following table describes the database functions.

Function Description
DAVERAGE Returns the average of selected database entries.
DCOUNT Counts the cells containing numbers from a specified database and criteria.
DCOUNTA Counts nonblank cells from a specified database and criteria.
DGET Extracts from a database a single record that matches the specified criteria.
DMAX Returns the maximum value from selected database entries.
DMIN Returns the minimum value from selected database entries.
DPRODUCT Multiplies the values in a particular field of records that match the criteria in a database.
DSTDEV Estimates the standard deviation based on a sample of selected database entries.
DSTDEVP Calculates the standard deviation based on the entire population of selected database entries.
DSUM Adds the numbers in the field column of records in the database that match the criteria.
DVAR Estimates variance based on a sample from selected database entries.
DVARP Calculates variance based on the entire population of selected database entries.

Computed criteria filters the list based on one or more calculations and does not use a field header from the list (it uses a new field header).

Computed criteria essentially compute a new field for the list so that you must supply new field names in the first row of the criteria range.

Computed criteria are a logical formula (returns True or False) that refers to cells in the first row of data in the list; it does not refer to the header row.
Back to Top
See also... Using AutoFilter | Using Custom AutoFilter
Using Advanced Filter
Return to Excel XP from Excel Filter
Excel XP Topics
- Tips
- Excel Screen Layout
- Navigational Techniques
- Working with Workbooks
- Templates
- Working with Worksheets
- Moving Around
- Move Worksheets
- Copy Worksheets
- Insert & Delete Cells
- Insert & Delete Rows
- Insert & Delete Columns
- Resize Row
- Resize Column
- Editing Data
- Content Color
- Cell Color
- Number Formats
- Fonts
- Alignment
- Text Direction
- Indent Contents
- Merge Cells
- Copy
- Move
- Undo & Redo
- Using Zoom
- Freeze & Unfreeze Titles
- Split Worksheet
- Spreadsheet Data
- AutoFill
- AutoComplete
- Comment
- Find
- Replace
- Spellcheck
- Formulas
- Functions
- Print
- Password
- Sorting
- AutoFilter
- Advanced Filter
- Macros
- Charts
- Charting
- Charting Elements
- Gantt Chart
- PivotTable
- PivotTable Calculations
- PivotTable Layout
- PivotTable Format
- PDF to Excel
- PDF-to-Excel Converter
- Excel to PDF Converter