{"id":8105,"date":"2021-10-01T13:41:35","date_gmt":"2021-10-01T13:41:35","guid":{"rendered":"https:\/\/officetuts.net\/excel\/?p=8105"},"modified":"2024-03-28T10:53:57","modified_gmt":"2024-03-28T10:53:57","slug":"filter-multiple-values-in-excel-table","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/","title":{"rendered":"Filter Multiple Values in Excel Table"},"content":{"rendered":"\n
Filter-Multiple-Values-in-Excel-Table<\/a>Download File<\/a><\/div>\n\n\n\n

When dealing with Excel and especially with various Excel tables, we can find ourselves in a situation where we need to filter out special desired data for our needs.<\/p>\n\n\n\n

In the example below, we will show how to filter multiple values in Excel Table.<\/p>\n\n\n\n

Using a simple filter to filter multiple values<\/h2>\n\n\n\n

For the example, we will use the list of NBA players and their various statistical categories: points, rebounds, assists, and turnovers, for several nights, so some players may appear multiple times:<\/p>\n\n\n\n

\"Table\n\nDescription<\/figure>\n\n\n\n

To include a filter in our table, we will click anywhere on our table, select Data tab >> Sort & Filter >> Filter<\/strong>:<\/p>\n\n\n\n

\"Diagram\n\nDescription<\/figure>\n\n\n\n

Once we do this, we can click on any of the dropdown arrows that are in the first row of the table:<\/p>\n\n\n\n

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

For the example, we will click on the filter in the first column, and we will have the following image:<\/p>\n\n\n\n

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

If we want to select only Anthony Davis and LeBron James, we will click on the (Select All) button<\/strong>, which will unselect all the players that are currently selected, and then we will click on two desired players. Our table now looks like this:<\/p>\n\n\n\n

\"Table\n\nDescription<\/figure>\n\n\n\n

There is also another way to filter out multiple values<\/a>. We will first clear the filter from our table by selecting any cell in the table and clicking on Filter<\/strong> again. You can see that it is currently set to active by the change in the icon background:<\/p>\n\n\n\n

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

Now we will click anywhere on the table again and go to the Team column<\/strong> and we will select only Lakers as a team:<\/p>\n\n\n\n

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

If we now want to add the Philadelphia 76ers to our selection, we will search for this value. When we do find it, we need to click on the \u201cAdd current selection to filter\u201d<\/strong> button:<\/p>\n\n\n\n

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

When we click OK<\/strong>, this team will be added to our table:<\/p>\n\n\n\n

\"Table\n\nDescription<\/figure>\n\n\n\n

Advanced filter to filter multiple values<\/h2>\n\n\n\n

There is also a pretty cool feature that Excel has, and that is Advanced Filter<\/strong>. We will remove filters from our table once again.<\/p>\n\n\n\n

We will click on our table, and go to the Data tab >> Sort & Filter<\/strong>. The advanced filter is located right next to our regular filter.<\/p>\n\n\n\n

\"Diagram\n\nDescription<\/figure>\n\n\n\n

When we click on it, the following window will appear:<\/p>\n\n\n\n

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

The advanced filter has several options:<\/p>\n\n\n\n

    \n
  • We can choose to Filter our list in the existing table or to copy the filtered results to a different location in Action<\/strong>.<\/li>\n\n\n\n
  • List range<\/strong> refers to our table, i.e. the list that we want to filter.<\/li>\n\n\n\n
  • Criteria range<\/strong> field is used to define the criteria for our filter.<\/li>\n\n\n\n
  • Copy to<\/strong> is used to designate the location where our filtered result will be copied.<\/li>\n<\/ul>\n\n\n\n

    To use it for filtering multiple values, we first need to define the desired values. We will copy the name of our columns (range A1:G1<\/strong>) and paste it into the range J1:P1<\/strong>.<\/p>\n\n\n\n

    Now we will define our criteria. We will say that we need all players from the Eastern conference<\/strong> that had over 20 points and over 10 rebounds<\/strong>.<\/p>\n\n\n\n

    Our preparation table (that is our criteria range<\/strong>) will look like this:<\/p>\n\n\n\n

    \"Diagram,<\/figure>\n\n\n\n

    Now we will click on our original table and go to Advanced filter<\/strong>. We will choose the option to copy our data to another location.<\/p>\n\n\n\n

    Our list range will be A1:G28<\/strong> (that is our table\u2019s range). Our criteria range will be J1:P2<\/strong>, and we will choose to copy our filtered data set to cell J16<\/strong>:<\/p>\n\n\n\n

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

    When we click OK<\/strong>, a new table will be created starting from the cell J16<\/strong> and it will look like this:<\/p>\n\n\n\n

    \"Text,<\/figure>\n\n\n\n

    Filter Multiple Values Using Formula<\/h2>\n\n\n\n

    There is also a convenient way to select multiple values that we want, and that is with the formula.<\/p>\n\n\n\n

    For this example, we will use the COUNTIF formula<\/strong>. We will create another sheet, call it \u201cValues\u201d<\/strong> and input just two values in it- LA Lakers and Brooklyn Nets<\/strong>:<\/p>\n\n\n\n

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

    In our first sheet, we will add the following formula:<\/p>\n\n\n\n

    =COUNTIF(Values!A:A,[@Team])=1<\/code><\/pre>\n\n\n\n
    What this formula does is that it checks the range in the sheet \u201cValues\u201d<\/strong> in column A<\/strong> (those are LA Lakers and Brooklyn Nets) and then searches for matching criteria in our first sheet, in the second column (Team<\/strong>).<\/p>\n\n\n\n
    Since COUNTIF <\/strong>returns values as 0 or 1<\/strong>, we added the \u201c=1\u201d<\/strong> part to show a TRUE or FALSE<\/strong> value in case our search criteria match our teams.<\/p>\n\n\n\n
    We got the following results:<\/p>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n\n\n\n
    Now we can filter out only TRUE values to get desired values:<\/p>\n\n\n\n
    \"Table\n\nDescription<\/figure>\n","protected":false},"excerpt":{"rendered":"
    When dealing with Excel and especially with various Excel tables, we can find ourselves in a situation where we need to filter out…<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[21],"tags":[170,190],"yoast_head":"\nFilter Multiple Values in Excel Table<\/title>\n<meta name=\"description\" content=\"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.\" \/>\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-multiple-values-in-excel-table\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Filter Multiple Values in Excel Table\" \/>\n<meta property=\"og:description\" content=\"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\" \/>\n<meta property=\"article:published_time\" content=\"2021-10-01T13:41:35+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-28T10:53:57+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png\" \/>\n<meta name=\"author\" content=\"Harun Spahic\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Harun Spahic\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 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-multiple-values-in-excel-table\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\"},\"author\":{\"name\":\"Harun Spahic\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\"},\"headline\":\"Filter Multiple Values in Excel Table\",\"datePublished\":\"2021-10-01T13:41:35+00:00\",\"dateModified\":\"2024-03-28T10:53:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\"},\"wordCount\":755,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png\",\"keywords\":[\"file\",\"pinterest\"],\"articleSection\":[\"examples\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\",\"url\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\",\"name\":\"Filter Multiple Values in Excel Table\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png\",\"datePublished\":\"2021-10-01T13:41:35+00:00\",\"dateModified\":\"2024-03-28T10:53:57+00:00\",\"description\":\"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Filter Multiple Values in Excel Table\"}]},{\"@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.\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2\",\"name\":\"Harun Spahic\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g\",\"caption\":\"Harun Spahic\"},\"sameAs\":[\"http:\/\/human.bsru.ac.th\/\"]}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Filter Multiple Values in Excel Table","description":"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.","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-multiple-values-in-excel-table\/","og_locale":"en_US","og_type":"article","og_title":"Filter Multiple Values in Excel Table","og_description":"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.","og_url":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/","article_published_time":"2021-10-01T13:41:35+00:00","article_modified_time":"2024-03-28T10:53:57+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png"}],"author":"Harun Spahic","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Harun Spahic","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/"},"author":{"name":"Harun Spahic","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2"},"headline":"Filter Multiple Values in Excel Table","datePublished":"2021-10-01T13:41:35+00:00","dateModified":"2024-03-28T10:53:57+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/"},"wordCount":755,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png","keywords":["file","pinterest"],"articleSection":["examples"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/","url":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/","name":"Filter Multiple Values in Excel Table","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png","datePublished":"2021-10-01T13:41:35+00:00","dateModified":"2024-03-28T10:53:57+00:00","description":"Learn how to filter multiple values in Excel Table with this easy tutorial. Filter out specific data for your needs and streamline your Excel work.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2021\/10\/table-description-automatically-generated.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/examples\/filter-multiple-values-in-excel-table\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Filter Multiple Values in Excel Table"}]},{"@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."},{"@type":"Person","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/694491c70f776081acdac4ae41f222a2","name":"Harun Spahic","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/b7b887a2249679182be5550a4421a7a9?s=96&d=mm&r=g","caption":"Harun Spahic"},"sameAs":["http:\/\/human.bsru.ac.th\/"]}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/8105"}],"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\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=8105"}],"version-history":[{"count":7,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/8105\/revisions"}],"predecessor-version":[{"id":17324,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/8105\/revisions\/17324"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=8105"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=8105"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=8105"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}