{"id":4598,"date":"2019-08-20T07:18:55","date_gmt":"2019-08-20T07:18:55","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=4598"},"modified":"2024-03-28T10:53:57","modified_gmt":"2024-03-28T10:53:57","slug":"filter-table","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/","title":{"rendered":"Filter a Table in Excel"},"content":{"rendered":"\n
Filter-a-Table-in-Excel<\/a>Download File<\/a><\/div>\n\n\n\n

Filters in Excel are used to reduce the amount of data displayed inside a worksheet. Depending on whether you filter text, numbers, or dates you will get different filtering options. For example, you could filter people by age to check whether they are employees or underage, or you could filter cities with the word \u201cNew\u201d, like \u201cNew York\u201d, or \u201cNew Delhi\u201d.<\/p>\n\n\n\n

In this lesson, I\u2019ll show you how to use filters for\ndifferent types of data, so you can narrow down the result to the information\nyou need.<\/p>\n\n\n\n

Apply filter<\/h2>\n\n\n\n

There are two ways to apply a filter in Excel<\/a>. First click\nany cell inside a table, where you want filtering applied. Next, choose one of\nthese two options:<\/p>\n\n\n\n

    \n
  1. Navigate to HOME\n>> Editing >> Sort & Filter >> Filter<\/strong>.<\/li>\n<\/ol>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
      \n
    1. Use the keyboard shortcut – Ctrl + Shift + K<\/strong><\/li>\n<\/ol>\n\n\n\n

      After you do that, the filtering icons will show up. You can click them to expand filtering options.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Filtering text<\/h2>\n\n\n\n

      The first type of data to filter is text. If you click a filter icon inside a column with text data, text filtering options will appear. Let\u2019s click the filter icon next to City<\/strong>.<\/p>\n\n\n\n

      We will filter only these positions, where there is the word New<\/strong> inside a cell.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      As you can see it returned two positions: New Orleans<\/strong>, and New York<\/strong>. You can unselect the values you don\u2019t want.<\/p>\n\n\n\n

      After you apply a filter to a column, the icon changes its look.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Custom Autofilter<\/h2>\n\n\n\n

      If you click any position inside the Text Filters<\/strong>, it\u2019s going to open the Custom Autofilter<\/strong> window, which is the same for all types of data.<\/p>\n\n\n\n

      Because you clicked Contains<\/strong>,\nthe contains option appears in the first dropdown. You can select the second\noption, and click the Or<\/strong> radio button.<\/p>\n\n\n\n

      In this case, the filtered values are those that contain the word new<\/strong>, los<\/strong>, or both.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      And this is the result we get.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      The Contains<\/strong>\noption works in this case. But if there was a city with the substring new<\/strong> or los<\/strong> inside, it would also be filtered.<\/p>\n\n\n\n

      In this case, you should choose a different option – begins with<\/strong>.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      The other option you have are Equals<\/strong>, Does Not Equal<\/strong>, Begins With<\/strong>, Ends With<\/strong>, Contains<\/strong>, and Does Not Contain<\/strong>.<\/p>\n\n\n\n

      Filtering numbers<\/h2>\n\n\n\n

      Now, click the filter icon next to the Age<\/strong> column. The Text Filters<\/strong> changed to Number Filters<\/strong> with the new set of options.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Here, you can choose Between<\/strong>\nand select only people with a certain age group, or choose Top 10<\/strong> to select 10 oldest people on the list.<\/p>\n\n\n\n

      In the Text Filters<\/strong>,\nno matter which option you chose, the Custom\nAutofilter<\/strong> window would appear. In Number\nFilters<\/strong> there are three options:<\/p>\n\n\n\n

      Equals<\/strong>, Does Not Equal<\/strong>, Greater Than<\/strong>, Greater Than\nOr Equal To<\/strong>, Less Than<\/strong>, Less Than Or Equal To<\/strong>, Between<\/strong>, Custom Filter<\/strong> will display Custom\nAutofilter<\/strong>.<\/p>\n\n\n\n

      To set the number of top or bottom values use Top 10 Autofilter.<\/strong><\/p>\n\n\n\n

      Above and Below\nAverage:<\/strong> They won\u2019t display any window, just filter values right away.<\/p>\n\n\n\n

      Filtering dates<\/h2>\n\n\n\n

      With filtering dates, you have a huge number of Filtering options.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      If you expand the All Dates in the Period<\/strong> option, you are going to get additional filters.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Color Filters<\/h2>\n\n\n\n

      If you fill cells with color or change the font color<\/a>,\nadditional options are going to appear. Take a look at how it works in our\nexample.<\/p>\n\n\n\n

      Inside the table, the most efficient employees are highlighted with green<\/strong> color, and the least efficient with red<\/strong>, the rest is not highlighted with any color.<\/p>\n\n\n\n

      There are three fills: green<\/strong>, red<\/strong>, and no fill,<\/strong> and two font colors: black<\/strong> and white<\/strong>.<\/p>\n\n\n\n

      Now, click a filter button inside a header and navigate to Filter by Color<\/strong>.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Inside Filter by Cell Color<\/strong>, there are three different types of fill: green<\/strong>, red<\/strong>, and No Fill<\/strong> – the same as fills inside the table.<\/p>\n\n\n\n

      In Filter by Font\nColor<\/strong>, there are two types: white<\/strong>,\nand Automatic \u2013 which is black<\/strong> in\nmost cases.<\/p>\n\n\n\n

      Using wildcard characters<\/h2>\n\n\n\n

      Wildcard characters<\/a> are characters that represent one or\nmore special characters<\/a>.<\/p>\n\n\n\n

      The asterisk (*) character represents a variable number of\ncharacters (from zero to infinity), and the question mark (?), which represents\na single character.<\/p>\n\n\n\n

      Here\u2019s how it works. Let\u2019s say that you want to find a name that begins with the letter a<\/strong>.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      But the same effect you would get if you use the option Begins With<\/strong>. That\u2019s true, so let\u2019s check something a bit more complicated.<\/p>\n\n\n\n

      Let\u2019s display all names that begin with the letter a<\/strong>, and end with the letter a<\/strong>. Let\u2019s modify our example and see what you can get.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      The same effect you can achieve with the following Custom AutoFilter<\/strong>.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      And this is the result.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Let\u2019s see how the question mark works as a wildcard\ncharacter.<\/p>\n\n\n\n

      In the next example, we are going to display names that start with a, and end with a, but this time add another restriction \u2013 names should have 6 characters.<\/p>\n\n\n\n

      Here\u2019s what it looks like.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      Now, the filter displays only rows with these two names: Albina<\/strong> and Alisha<\/strong>.<\/p>\n\n\n\n

      You can also use the wildcard characters inside Custom AutoFilter<\/strong>. They are easy to use\nand make our work much easier.<\/p>\n\n\n\n

      Clearing Filters<\/h2>\n\n\n\n

      After you applied a filter, it\u2019s time to clear them. You can clear a single filter, or you can clear it all at once.<\/p>\n\n\n\n

      To clear a single filter, click the filter icon, and navigate to Clear Filter From<\/strong>. In our case, it will be the First Name<\/strong>.<\/p>\n\n\n\n

      \"\"<\/figure>\n\n\n\n

      If you have more than one filter applied, you can remove them one by one, or clear them all at once.<\/p>\n\n\n\n

      Here\u2019s how to do it.<\/p>\n\n\n\n

      Navigate to Home >> Editing >> Sort & Filter >> Clear<\/strong>.<\/p>\n\n\n\n

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

      Filters in Excel are used to reduce the amount of data displayed inside a worksheet. Depending on whether you filter text, numbers, or…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nFilter a Table in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Filter a Table in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\" \/>\n<meta property=\"article:published_time\" content=\"2019-08-20T07:18:55+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T10:53:57+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"8 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Filter a Table in Excel\",\"datePublished\":\"2019-08-20T07:18:55+00:00\",\"dateModified\":\"2024-03-28T10:53:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\"},\"wordCount\":968,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\",\"name\":\"Filter a Table in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png\",\"datePublished\":\"2019-08-20T07:18:55+00:00\",\"dateModified\":\"2024-03-28T10:53:57+00:00\",\"description\":\"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage\",\"url\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png\",\"contentUrl\":\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png\",\"width\":134,\"height\":150},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filter a Table in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Filter a Table in Excel","description":"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/","og_locale":"en_US","og_type":"article","og_title":"Filter a Table in Excel","og_description":"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.","og_url":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/","article_published_time":"2019-08-20T07:18:55+00:00","article_modified_time":"2024-03-28T10:53:57+00:00","og_image":[{"url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"8 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Filter a Table in Excel","datePublished":"2019-08-20T07:18:55+00:00","dateModified":"2024-03-28T10:53:57+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/"},"wordCount":968,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/","url":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/","name":"Filter a Table in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage"},"thumbnailUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png","datePublished":"2019-08-20T07:18:55+00:00","dateModified":"2024-03-28T10:53:57+00:00","description":"Learn how to filter your Excel data! Use filters to reduce the amount of data displayed inside a worksheet and get the info you need.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/filter-table\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#primaryimage","url":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png","contentUrl":"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2019\/08\/filter-button.png","width":134,"height":150},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Filter a Table in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4598"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=4598"}],"version-history":[{"count":11,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4598\/revisions"}],"predecessor-version":[{"id":17323,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/4598\/revisions\/17323"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=4598"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=4598"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=4598"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}