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 3

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 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.
Excel Filter: Advanced Filter example 1

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.
  Excel Filter: Advanced Filter dialog box 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.
  Excel Filter: Advanced Filter dialog box after selection 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
- 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