Tweet

Advanced filter - Excel

Introduction to advanced filter

Advanced Filter for effectively filter (select) data. For various analyzes, search, selection of data. If you can do using basic filter, is a little more complicated.

Theme is divided into the chapters:

• Theory
  • Advanced filter in Ribbon
  • Dialog: Advanced Filter - Description
  • Operators and Special Characters
• Source data
• Example
  • Filter all data
  • Show...
• Downloads
• Related topics

Example

Advanced filter in Ribbon

Excel 2013/2010

On the Ribbon Data click at Advanced. You will receive a dialog box: Advanced Filter.

Dialog: Advanced Filter

The dialog Advanced Filter

• Actions - where copy
  • Filter the list, in-place - copy to the some Table
  • Copy to another location - I recommend using the result of the copy in a separate table
• List range: - Table of data which are Filtered
• Criteria range: - enter the criteria (the value that we find)
• Copy to: - where you want copy to another location
• Unique records only - if you do not see duplicate records

Note: Inserting the area, including column names.

Operators and Special Characters

For filters can be used operators (marks) and special characters.

Operators

• = equals
• < less
• > more
• <= less equals
• >= more equals
• Not equal

Special Characters

• * - few characters (even zero)
• ? - one character
• ~ - search for special characters in the text as *,?,~

Note: If you need to filter the exact name must be used:

="=Text"

Filters only the word Text

Text

Filters word Text, Texty, Text1, ...

More in the following examples.

Source data

In the practical examples will use the following table.

ID	Name	Date	Season
1	Pavel	1.2.2015	spring
2	John	2.2.2015	summer
3	Jane	3.2.2015	autumn
4	Jone	4.2.2015	winter
5	Ivone	5.2.2015	winter
6	Pivone	6.2.2015	winter
6	Pivone	6.2.2015	winter
8	Jane	8.2.2015	winter

Praktical Example

List of practical examples:

• 01 Filter all data
• 02 Filter one parameter
• 03 Filter more parameters (one column)
• 04 Filter range in parameters (one column)
• 05 Filter range more parameters (one column)
• 06 Filter text (one column)
• 07 Filter only said text (one column)
• 08 Filter text which contains value (one column)
• 09 Filter find special Characters in text (one column)
• 10 Filter value (more column)

01 Filter all data

Filter all data from ID

Example 01

ID

02 Filter one parameter

Filter one parameter from column ID

Example 02

ID 1

03 Filter more parameters (one column)

Filter more parameters from column ID

Example 03

ID 1 3 5

04 Filter range in parameters (one column)

Filter range in parameters from column ID. In Example all values larger than 5.

Example 04

ID >5

05 Filter range more parameters (one column)

Filter more parameters from column ID. In Example all values larger than 2 or values less equal 6.

Example 05

ID ID >2 <=6

06 Filter text (one column)

Filter text in column Name. In Example all values whith "Pivone". Caution filter find also Pivone, Pivonek, ...

Example 06

Name Pivone

07 Filter only said text (one column)

Filter text in column Name. In Example all values whith "Pivone". Find only "Pivone".

Example 07

Name ="=Pivone"

08 Filter text which contains value (one column)

Filter text in column Name. In Example all values whith "ivone". Find Pivone, Ivone, Pivonek .

Example 08

Name *Ivone*

09 Filter find special Characters in text (one column)

Filter text in column Season. Find *

Note: The source table is modified.

Example 09a 09b

Season *~* or Season winter~*

10 Filter value (more column)

Filter text in column Name. In Example all values whith "ivone". Find Pivone, Ivone, Pivonek .

Example 10

ID Season 6 winter

Downloads

File Advanced filter practically example Excel free download.

Related topics

More interesting articles:

• Drop Down Lists - praktical example - Excel
• OFFSET praktical example
• Autofilter VBA Excel - in czech only :(

Článek byl aktualizován: 21.03.2015 19:00

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.