{"id":337,"date":"2017-07-17T11:56:02","date_gmt":"2017-07-17T01:56:02","guid":{"rendered":"https:\/\/www.dynamicwebtraining.com.au\/blog\/?p=337"},"modified":"2024-11-15T14:24:26","modified_gmt":"2024-11-15T03:24:26","slug":"understanding-advanced-filters-excel","status":"publish","type":"post","link":"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel","title":{"rendered":"Understanding Advanced Filters in Excel"},"content":{"rendered":"\n

Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria. Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter<\/strong> in Excel enables you to analyse the data in a list by filtering it according to specific criteria.<\/p>\n\n\n\n\n\n\n\n

Using Advanced Filters<\/strong> in Microsoft Excel<\/h2>\n\n\n\n

To work with Advanced Filters<\/strong> in Excel, you need to understand a few of the concepts that are used. A list in Excel is a series of rows of information. Each row is effectively one unit of information. This structure is very similar to a simple database and therefore Excel uses similar terminology to describe the parts of the list. The following illustrates a list and its parts.<\/p>\n\n\n\n

Fields, Field Names and Records in Excel
<\/strong><\/h4>\n\n\n\n

A field<\/strong> is a column<\/strong> in the list of data. In the example below, the column of data for the Last Name<\/strong> is an example of a field.<\/p>\n\n\n\n

The field name<\/strong> is the heading<\/strong> at the top of the column. The field names within one list must be unique. In the example below, the text Annual Fee<\/strong> is an example of a field name.<\/p>\n\n\n\n

A record<\/strong> is a row<\/strong> of data in the list. Each record is one item of data in the list. In the example below, the row of information for Fred Jackson<\/strong> is one record. Note that advanced filters do not work correctly if there are blank rows in the list.<\/p>\n\n\n

\n
\"Understanding
Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div>\n\n\n

Criteria, Criteria Range, AND and OR in Excel
<\/strong><\/h4>\n\n\n\n

Criteria<\/strong> are tests against the data in specific fields, for instance Gold<\/strong>. When Gold<\/strong> is tested against the field Type<\/strong>, the filter would display only the people with Gold<\/strong> memberships. All other records are filtered out (hidden).<\/p>\n\n\n\n

The criteria range<\/strong> is the area where you specify the criteria. The first row contains the field names that mirror those in the list. The second and subsequent rows are used to type the criteria or examples of what you are looking for in the list.<\/p>\n\n\n\n

If you want the conditions between fields joined with an AND<\/strong>, you write the conditions on the same row. If you want them joined with an OR<\/strong>, you write the conditions on separate rows. In the next example below, our criteria specifies greater than 15<\/strong> years membership AND Gold<\/strong> membership.<\/p>\n\n\n

\n
\"Understanding
Click image to enlarge <\/i><\/figcaption><\/figure>\n<\/div>\n\n\n

Handy Tips when using Advanced Filters in Excel <\/strong><\/h4>\n\n\n\n

\u2022 When using the Advanced Filter<\/strong> dialog box, if you can\u2019t remember the range of cells that hold certain values (e.g. Criteria range<\/strong>), you can click on Collapse Dialog<\/strong> which moves the focus to the workbook, enabling you to select the actual cells on the relevant worksheet.<\/p>\n\n\n\n

\u2022 You can use the extract feature of the Advanced Filter<\/strong> to create a list of unique codes that are used in a list. For example, to create a list of Type<\/strong> codes, extract the Type<\/strong> field with no criteria and select the checkbox Unique records<\/strong> only in the Advanced Filter<\/strong> dialog box.<\/p>\n\n\n\n

\u2022 When you perform the copy<\/strong> operation during filtering, Excel names the header row of the copied records as Extract<\/strong>. It names the cells containing the criteria range as Criteria<\/strong>. You can use these names to navigate quickly to the extract or criteria ranges of the worksheet via the Name<\/strong> box.<\/p>\n\n\n\n

The Filter topic is covered in our Advanced Excel course<\/a><\/p>\n\n\n\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

Excel worksheets can be used to keep lists of information such as employee details, subscribers, accounts, sales, or even bird sightings if need be. The Advanced Filter in Excel enables you to analyse the data in a list by filtering it according to specific criteria. Excel worksheets can be used to keep lists of information…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-337","post","type-post","status-publish","format-standard","hentry","category-microsoft-excel"],"yoast_head":"Advanced Filters in Excel for Data Analysis - Blog Archives<\/title>\n<meta name=\"description\" content=\"View and Share Dynamic Web Training Blog Archives. This is a great source of articles and posts on Computer and IT training, tutorials and insights\" \/>\n<meta name=\"robots\" content=\"index, follow\" \/>\n<meta name=\"googlebot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<meta name=\"bingbot\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Advanced Filters in Excel for Data Analysis - Blog Archives\" \/>\n<meta property=\"og:description\" content=\"Learn how to use the Advanced Filter in Excel that enables you to analyse the data in a list by filtering it according to specific criteria.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\" \/>\n<meta property=\"og:site_name\" content=\"Dynamic Web Training Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/DynamicWebTraining\/\" \/>\n<meta property=\"article:published_time\" content=\"2017-07-17T01:56:02+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-11-15T03:24:26+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/07\/understanding-advanced-filters-excel-1.png\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@dynamicwebtrain\" \/>\n<meta name=\"twitter:site\" content=\"@dynamicwebtrain\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\",\"name\":\"Dynamic Web Training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"],\"logo\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2016\/02\/logo.png\",\"width\":361,\"height\":109,\"caption\":\"Dynamic Web Training\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#logo\"}},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/\",\"name\":\"Dynamic Web Training Blog\",\"description\":\"The Ultimate Training Experience.\",\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/?s={search_term_string}\",\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#primaryimage\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-content\/uploads\/2017\/07\/understanding-advanced-filters-excel-1.png\",\"width\":874,\"height\":385},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#webpage\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\",\"name\":\"Advanced Filters in Excel for Data Analysis - Blog Archives\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#primaryimage\"},\"datePublished\":\"2017-07-17T01:56:02+00:00\",\"dateModified\":\"2024-11-15T03:24:26+00:00\",\"description\":\"Learn how to use the Advanced Filter in Excel that enables you to analyse the data in a list by filtering it according to specific criteria.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\",\"name\":\"Training Blog\"}},{\"@type\":\"ListItem\",\"position\":2,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\",\"name\":\"Training\"}},{\"@type\":\"ListItem\",\"position\":3,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/category\/training\/microsoft-excel\",\"name\":\"Microsoft Excel\"}},{\"@type\":\"ListItem\",\"position\":4,\"item\":{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\",\"url\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel\",\"name\":\"Understanding Advanced Filters in Excel\"}}]},{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#webpage\"},\"author\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\"},\"headline\":\"Understanding Advanced Filters in Excel\",\"datePublished\":\"2017-07-17T01:56:02+00:00\",\"dateModified\":\"2024-11-15T03:24:26+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#webpage\"},\"publisher\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/understanding-advanced-filters-excel#primaryimage\"},\"articleSection\":\"Microsoft Excel\",\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#\/schema\/person\/c94653aed4a6decc8e357af0a1082233\",\"name\":\"Dynamic Web Training\",\"image\":{\"@type\":\"ImageObject\",\"@id\":\"https:\/\/www.dynamicwebtraining.com.au\/blog\/#personlogo\",\"inLanguage\":\"en-US\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0a14e92e62ad4eee0843f5cf7da3a00e1df4c9763922d4d20ba3ed2402a6896d?s=96&d=mm&r=g\",\"caption\":\"Dynamic Web Training\"},\"description\":\"Dynamic Web Training is Australia's leading provider of instructor led software training. We offer training courses in Adobe, Web Design, Graphic Design, Photoshop, InDesign, Dreamweaver and many more.\",\"sameAs\":[\"https:\/\/www.facebook.com\/DynamicWebTraining\/\",\"https:\/\/www.linkedin.com\/company\/dynamic-web-training\",\"https:\/\/twitter.com\/dynamicwebtrain\"]}]}<\/script>","_links":{"self":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/337","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/comments?post=337"}],"version-history":[{"count":14,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/337\/revisions"}],"predecessor-version":[{"id":2017,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/posts\/337\/revisions\/2017"}],"wp:attachment":[{"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/media?parent=337"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/categories?post=337"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dynamicwebtraining.com.au\/blog\/wp-json\/wp\/v2\/tags?post=337"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}