Excel Filter
 |
 |
 |
 |
 |
 |
 |
Excel Filter
Additional Resources
Excel Filter
Excel Filter
Excel Filter | Microsoft Excel XP | Page 4 |
 |
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.
 |
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
- 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