Excel Filter
 |
 |
 |
 |
 |
 |
 |
Excel Filter
Additional Resources
Excel Filter
Excel Filter
Excel Filter | Microsoft Excel XP | Page 3 |
 |
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 Advanced Filter
Advanced Excel Filter is much more flexible than AutoFilter, but it takes some up-front work to use it.Advanced Filter provides you with the following capabilities:
| ► | You can specify more complex filtering criteria. |
| ► | You can specify computed filtering criteria. |
| ► | You can extract a copy of the rows that meet the criteria to another location. |
Before you can use the Advanced Filter feature, you must set up a criteria range (a range on a worksheet that holds the information (questions) Excel uses to filter the list.
The criteria range must conform to the following specifications:
| ► | The criteria range consists of at least two rows. |
| ► | The first row contains some or all of the field names from the list. |
| ► | The other rows consist of filtering criteria. |
If you use more than one row below the field names in the criteria range, the criteria in each row are joined with an OR operator.
The entries that you make in a criteria range can be either of the following:
| ► |
Text or value criteria: The filtering involves comparisons to a value or text, using operators such as equal (=), greater than (>), not equal to (<>), and so on. |
| ► | Computed criteria: The filtering involves some sort of computation. |
Basically, the Advanced Filter consists of 3 parts:
| 1. | List Range The original database - Fieldnames + Records. |
| 2. | Criteria Range Question area - Fieldnames + question(s). |
| 3. | Copy To Answers Area - where Excel places the results. |
 |
To perform advanced Excel filter on a list, follow these steps:
| 1. | Make sure your List Range has fieldnames(headings) and records(rows with data). |
| 2. | Copy the fieldnames to a new row. Type your criteria below the desired fieldname(s). |
| 3. | Choose Data ► Filter ► Advanced Filter from the menu bar. |
Advanced Filter dialog box |
|
| 4. | Choose the option: "Copy to another location" to have your Output Range place on a separate part of your sheet. |
| 5. | Click inside the text box next to "List Range" and select your original database - fieldnames and records. |
| 6. | Click inside the text box next to "Criteria Range" and select your criteria area - fieldnames and row with criteria(questions). |
| 7. | Click inside the text box next to "Copy to" and click only the starting cell of where your output range should be. |
Advanced Filter dialog box
after selection |
|
| 8. | Click OK. |
You'll see that your criteria extracts certain records from the original database and places a copy of it at the "Copy to" location.
| Back to Top |
| See also... Using AutoFilter | Using Custom AutoFilter |
| Using Criteria for Text | Using Criteria for Values |
| 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