{"id":518409,"date":"2022-11-29T21:00:57","date_gmt":"2022-11-29T18:00:57","guid":{"rendered":"https:\/\/en.buradabiliyorum.com\/how-to-use-an-advanced-filter-in-microsoft-excel\/"},"modified":"2022-11-29T21:00:57","modified_gmt":"2022-11-29T18:00:57","slug":"how-to-use-an-advanced-filter-in-microsoft-excel","status":"publish","type":"post","link":"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/","title":{"rendered":"#How to Use an Advanced Filter in Microsoft Excel"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_85 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a42cc779b636\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #dd3333;color:#dd3333\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #dd3333;color:#dd3333\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a42cc779b636\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-1'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#%E2%80%9CHow_to_Use_an_Advanced_Filter_in_Microsoft_Excel%E2%80%9D\" >&#8220;How to Use an Advanced Filter in Microsoft Excel&#8221;<\/a><ul class='ez-toc-list-level-2' ><li class='ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#How_to_Set_Up_the_Criteria_Range\" >How to Set Up the Criteria Range<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#Entering_Criteria_for_an_Advanced_Filter_in_Excel\" >Entering Criteria for an Advanced Filter in Excel<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#How_to_Use_a_Single_Criterion_Single_Column_Excel_Filter\" >How to Use a Single Criterion, Single Column Excel Filter<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#How_to_Use_a_Multiple_Criteria_Single_Column_Excel_Filter\" >How to Use a Multiple Criteria, Single Column Excel Filter<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#How_to_Use_a_Multiple_Criteria_Multiple_Column_Excel_Filter\" >How to Use a Multiple Criteria, Multiple Column Excel Filter<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#All_Conditions_True\" >All Conditions True<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#Any_Conditions_True\" >Any Conditions True<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/buradabiliyorum.com\/en\/how-to-use-an-advanced-filter-in-microsoft-excel\/#Any_and_All_Conditions_True\" >Any and All Conditions True<\/a><\/li><\/ul><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h1><span class=\"ez-toc-section\" id=\"%E2%80%9CHow_to_Use_an_Advanced_Filter_in_Microsoft_Excel%E2%80%9D\"><\/span>&#8220;How to Use an Advanced Filter in Microsoft Excel&#8221;<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<div>\n<img loading=\"lazy\" decoding=\"async\" class=\"type:primaryImage alignnone size-full wp-image-757715\" data-pagespeed-no-defer=\"\" src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?width=1198&amp;trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Microsoft Excel logo on a green background\" width=\"1200\" height=\"675\"\/><\/p>\n<p>To create an advanced filter in Excel, start by setting up your criteria range. Then, select your data set and open the Advanced filter on the Data tab. Complete the fields, click OK, and see your data a new way.<\/p>\n<p>While Microsoft Excel offers a built-in feature for filtering data, you may have a large number of items in your sheet or need a more complex filter. Here, we\u2019ll explain how to create an advanced filter in Excel.<\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Set_Up_the_Criteria_Range\"><\/span>How to Set Up the Criteria Range<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Before you can use the advanced filter in Excel, you\u2019ll need to create a cell range for the conditions you\u2019ll use.<\/p>\n<p>Add at least a few rows above your data to get started; you can always <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/761450\/how-to-insert-multiple-rows-in-microsoft-excel\/\">insert more rows if needed. Keep in mind, you\u2019ll need one row for the labels and one empty row between the criteria and the data.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Insert Multiple Rows in Microsoft Excel<\/em><\/strong><\/p>\n<p>In the top row, enter your columns labels. These should match those for your data as they\u2019ll be used for the filter criteria.<\/p>\n<p>We\u2019ll be using an example throughout this tutorial, so below is the data we\u2019re using.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848059\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/StartingData-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Data for a filter in Excel\" width=\"480\" height=\"250\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>We then insert five rows above our data. We have one row for labels, three for criteria, and one blank row. We then copy our column headers into the first row. So, now our sheet looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848058\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/CriteriaRangeAdded-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Criteria range added to a data set\" width=\"480\" height=\"370\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Once you have the range for your filter conditions set up, you\u2019re ready to create the advanced filter.<\/p>\n<blockquote class=\"admonishment_tip\"><p><strong>Tip:<\/strong> You can name your criteria range to automatically pop it into the filter if you like.<\/p><\/blockquote>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"Entering_Criteria_for_an_Advanced_Filter_in_Excel\"><\/span>Entering Criteria for an Advanced Filter in Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To enter criteria for your advanced Excel filter into a cell, you\u2019ll use the format <code>=\"=variable\"<\/code>.<\/p>\n<p>The first equal sign begins the string and the quotations marks contain the criteria. You can use the normal <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/800546\/google-sheets-comparison-operators\/\">comparison operators for your conditions. Here are a few examples.<\/p>\n<ul>\n<li>Equal to Smith: <code>=\"=Smith\"<\/code><\/li>\n<li>Not equal to Smith: <code>=\"&lt;&gt;Smith\"<\/code><\/li>\n<li>Less than 100: <code>=\"&lt;100\"<\/code><\/li>\n<li>Greater than or equal to 100: <code>=\"&gt;=100\"<\/code><\/li>\n<\/ul>\n<p>When you type the criteria this way into the cell, Excel converts it to the format it needs for the filter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848060\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/CriteriaExample-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Criteria format example in Excel\" width=\"515\" height=\"150\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Use_a_Single_Criterion_Single_Column_Excel_Filter\"><\/span>How to Use a Single Criterion, Single Column Excel Filter<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The best way to get started is with a simple example using one condition and one column. Here, we\u2019ll filter our data based on the Location ID 2B.<\/p>\n<p>Go to the Location ID column and enter the following for equals 2B into the first row below the label:<\/p>\n<pre>=\"=2B\"<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848061\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/SingleCriteria-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Single condition and column in the criteria range\" width=\"478\" height=\"172\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Next, select a cell in your data set, head to the Data tab, and pick \u201cAdvanced\u201d in the Sort &amp; Filter section of the ribbon.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-848062 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/DataAdvanced-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Advanced filter on the Data tab\" width=\"515\" height=\"155\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>In the pop-up box, start by choosing where you want the filtered data to <a href=\"https:\/\/buradabiliyorum.com\/en\/category\/download-scripts-themes-apps\/\" data-internallinksmanager029f6b8e52c=\"9\" title=\"Download Scripts &amp; Themes &amp; Apps\" target=\"_blank\" rel=\"noopener\">app<\/a>ear. You can filter it in place or in another location. If you choose the latter, enter the location in the Copy To box.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848064\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/CopyTo-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Copy To field for filtering in another location\" width=\"315\" height=\"306\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Now confirm the cells in the List Range box. Excel should have added them for you automatically, so simply make sure they\u2019re correct.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848063\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/ListRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"List Range for a filter\" width=\"315\" height=\"306\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Then, enter the cell range into the Criteria Range box. You can do this easily by clicking inside the box and then using your cursor to <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/354258\/how-to-easily-select-a-block-of-cells-in-excel\/\">drag through the range in your sheet. Be sure to include the column label cells and only additional rows with cells that contain criteria. If you include empty rows, it\u2019s highly likely that your filter results will be incorrect.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848065\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/CriteriaRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Criteria range for an advanced filter\" width=\"650\" height=\"229\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Optionally check the box if you want unique records only. Click \u201cOK\u201d when you finish.<\/p>\n<p>You should then see your filtered data. If you chose to filter your data in place, the other rows should be hidden. Here, we picked a location in our sheet for the filtered data.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848066\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/SingleFiltered-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Single condition filtered\" width=\"650\" height=\"230\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Count Unique Values in Microsoft Excel<\/em><\/strong><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Use_a_Multiple_Criteria_Single_Column_Excel_Filter\"><\/span>How to Use a Multiple Criteria, Single Column Excel Filter<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Maybe you want to filter data using multiple conditions that appear in a single column. You can do that with an advanced Excel filter. As an example, we\u2019ll filter our data for Location ID\u2019s 1B and 2B.<\/p>\n<p>Go to the Location ID column and enter the criteria in two separate rows, 2 and 3, starting directly below the label.<\/p>\n<pre>=\"=1B\"<\/pre>\n<pre>=\"=2B\"<\/pre>\n<p>It should look like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-848067 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaSingleColumn-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Multiple conditions for one column in the criteria range\" width=\"480\" height=\"124\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Select a cell in your data set, go to the Data tab, and pick \u201cAdvanced\u201d to open the filter tool.<\/p>\n<p>Complete the same details as before, but this time, expand the criteria range to include the additional condition. Click \u201cOK\u201d to apply the filter.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-848068 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaSingleColumnCriteriaRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Multiple conditions for one column criteria range\" width=\"650\" height=\"260\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You should then see both results from the filter in the location you chose.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone wp-image-848069 size-full\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaSingleColumnFiltered-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Multiple conditions for one column filtered\" width=\"650\" height=\"232\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h2 role=\"heading\" aria-level=\"2\"><span class=\"ez-toc-section\" id=\"How_to_Use_a_Multiple_Criteria_Multiple_Column_Excel_Filter\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_4\">How to Use a Multiple Criteria, Multiple Column Excel Filter<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Next, we\u2019ll look at using multiple conditions in an advanced Excel filter. This can be <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/401998\/how-to-use-logical-functions-in-excel-if-and-or-xor-not\/\">AND or OR criteria. For example, you can filter for Location ID equals 1A <i>and<\/i> Lead equals Jones where all conditions are true. Or you can filter for Location ID equals 1B <i>or<\/i> Lead equals Jones where any conditions are true.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Use Logical Functions in Excel: IF, AND, OR, XOR, NOT<\/em><\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"All_Conditions_True\"><\/span>All Conditions True<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>To filter with an AND condition, you\u2019ll place both criteria in the same row below their corresponding labels.<\/p>\n<p>So, we enter the following below the Location ID label in row 2:<\/p>\n<pre>=\"=1A\"<\/pre>\n<p>Then, we enter the following below the Lead label, also in row 2:<\/p>\n<pre>=\"=Jones\"<\/pre>\n<p>It looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848070\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnAND-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND conditions in the criteria range\" width=\"480\" height=\"100\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>And just like before, select a cell in the data set, go to the Data tab, and pick \u201cAdvanced\u201d to open the tool.<\/p>\n<p>For this filter, we change our criteria range since it only includes rows 1 and 2. Adjust the other options as necessary and click \u201cOK.\u201d<\/p>\n<blockquote class=\"admonishment_note\"><p><strong>Note:<\/strong> Notice in the screenshot that Excel has <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/251590\/how-to-see-all-of-the-named-cell-ranges-in-an-excel-workbook\/\">named our criteria range for us. You may see the same thing when reusing the same cell range.<\/p><\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848072\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnANDCriteriaRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND conditions criteria range\" width=\"650\" height=\"257\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>We then have our one result. Remember that placing the criteria in the same row indicates the AND operator. So, even though we have Jones as a Lead for two locations, we filtered for only location 1A with Jones.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848071\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnANDFiltered-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND conditions filtered\" width=\"650\" height=\"228\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to See All of the Named Cell Ranges in an Excel Workbook<\/em><\/strong><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Any_Conditions_True\"><\/span>Any Conditions True<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Next, we\u2019ll filter by multiple conditions again, but using OR criteria. For this, you place the conditions in separate rows below the corresponding labels.<\/p>\n<p>So, we enter the following below the Location ID label in row 2:<\/p>\n<pre>=\"=1B\"<\/pre>\n<p>Then, we enter the following below the Lead label, but in row 3:<\/p>\n<pre>=\"=Jones\"<\/pre>\n<p>It looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848075\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnOR-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"OR conditions in the criteria range\" width=\"480\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Open the Advanced filter tool as before, adjust the criteria range to accommodate the additional row, and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848074\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnORCriteriaRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"OR conditions criteria range\" width=\"650\" height=\"259\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>As you can see, we have three results, one for 1B and two for Jones. Because we used the OR criteria, any conditions we included were met.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848073\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnORFiltered-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"OR conditions filtered\" width=\"650\" height=\"231\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>The key to setting up a multiple criteria filter in Excel is that for AND criteria, you place the conditions in the same row and for OR criteria, you place the conditions in separate rows.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848076\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/ANDvsOR-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"Criteria range setup for AND versus OR\" width=\"485\" height=\"333\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Any_and_All_Conditions_True\"><\/span><a rel=\"nofollow noopener\" target=\"_blank\" name=\"autotoc_anchor_7\">Any and All Conditions True<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>For one final example, we\u2019ll apply a more complex filter using AND and OR criteria together with a different comparison operator. We\u2019ll filter for Location ID equals 1A <i>and<\/i> Lead equals Jones <i>or<\/i> Sales is greater than 50,000.<\/p>\n<p>In row 2, we enter the following criteria below Location ID and Lead respectively:<\/p>\n<pre>=\"=1A\"<\/pre>\n<pre>=\"=Jones\"<\/pre>\n<p>In row 3, we enter the next condition below the Sales label:<\/p>\n<pre>=\"&gt;50000\"<\/pre>\n<p>This setup looks like this:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848079\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnANDOR-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND with OR conditions in the criteria range\" width=\"480\" height=\"125\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>Open the Advanced filter tool, double-check or adjust the fields as needed, and click \u201cOK.\u201d<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848078\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnANDORCriteriaRange-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND with OR conditions criteria range\" width=\"650\" height=\"259\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>You\u2019ll then see the results. Here, we have row 2 containing our AND criteria, 1A and Jones. Then, additional rows 3 through 5 containing our OR criteria for Sales greater than 50,000.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-848077\" data-pagespeed-lazy-src=\"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2022\/11\/MultipleCriteriaColumnANDORFiltered-ExcelAdvancedFilter.png?trim=1,1&amp;bg-color=000&amp;pad=1,1\" alt=\"AND with OR conditions filtered\" width=\"650\" height=\"228\" src=\"\/pagespeed_static\/1.JiBnMqyl6S.gif\" onload=\"pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\" onerror=\"this.onerror=null;pagespeed.lazyLoadImages.loadIfVisibleAndMaybeBeacon(this);\"\/><\/p>\n<p>If you have a large amount of data in your spreadsheet and need a more <a rel=\"nofollow noopener\" target=\"_blank\" href=\"https:\/\/www.howtogeek.com\/769058\/how-to-apply-a-filter-to-a-chart-in-microsoft-excel\/\">robust filter option, keep the advanced filter in Excel in mind.<\/p>\n<p><strong>RELATED:<\/strong> <strong><em>How to Apply a Filter to a Chart in Microsoft Excel<\/em><\/strong><\/p>\n<\/div>\n<p><script>\n setTimeout(function(){\n  !function(f,b,e,v,n,t,s)\n  {if(f.fbq)return;n=f.fbq=function(){n.callMethod?\n  n.callMethod.apply(n,arguments):n.queue.push(arguments)};\n  if(!f._fbq)f._fbq=n;n.push=n;n.loaded=!0;n.version='2.0';\n  n.queue=[];t=b.createElement(e);t.async=!0;\n  t.src=v;s=b.getElementsByTagName(e)[0];\n  s.parentNode.insertBefore(t,s) } (window, document,'script',\n  'https:\/\/connect.facebook.net\/en_US\/fbevents.js');\n   fbq('init', '335401813750447');\n   fbq('track', 'PageView');\n  },3000);\n<\/script><\/p>\n<blockquote><p><strong><span style=\"color: #ff6600;\">If you liked the article, do not forget to share it with your friends. Follow us on\u00a0<span style=\"color: #ff0000;\"><a style=\"color: #ff0000;\" href=\"https:\/\/news.google.com\/publications\/CAAqBwgKMLG0nwswvr63Aw\" target=\"_blank\" rel=\"nofollow noopener noreferrer\">Google News<\/a><\/span>\u00a0too, click on the star and choose us from your favorites.<\/span><\/strong><\/p><\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\">For forums sites go to <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/forum.buradabiliyorum.com\/\" target=\"_blank\" rel=\"noopener\">Forum.BuradaBiliyorum.Com<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<blockquote>\n<p style=\"text-align: center;\"><strong>If you want to read more like this article, you can visit our <span style=\"color: #ff9900;\"><a style=\"color: #ff9900;\" href=\"https:\/\/en.buradabiliyorum.com\/technology\/\" target=\"_blank\" rel=\"noopener\">Technology category.<\/a><\/span><\/strong><\/p>\n<\/blockquote>\n<p><span style=\"color: black;\"><a style=\"color: #ff9900;\" href=\"https:\/\/www.howtogeek.com\/848055\/how-to-use-advanced-filter-in-excel\/\" target=\"_blank\" rel=\"noopener\">Source<\/a><\/span><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8220;How to Use an Advanced Filter in Microsoft Excel&#8221; To create an advanced filter in Excel, start by setting up your criteria range. Then, select your data set and open the Advanced filter on the Data tab. Complete the fields, click OK, and see your data a new way. While Microsoft Excel offers a built-in&#8230;<\/p>\n","protected":false},"author":1,"featured_media":518410,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"fifu_image_url":"https:\/\/www.howtogeek.com\/wp-content\/uploads\/2021\/09\/microsoft_excel_hero_1200x675.jpg?height=200p&trim=2,2,2,2","fifu_image_alt":"","footnotes":""},"categories":[18],"tags":[],"class_list":["post-518409","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-technology"],"_links":{"self":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/518409","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/comments?post=518409"}],"version-history":[{"count":0,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/posts\/518409\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media\/518410"}],"wp:attachment":[{"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/media?parent=518409"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/categories?post=518409"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/buradabiliyorum.com\/en\/wp-json\/wp\/v2\/tags?post=518409"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}