How To Add Filter Just In Column A And D In Excel

Excel Filter: Add together, Use, Remove and more advanced operations in Excel

The Excel Filter control can assist to filter data in a range or table to but show the information you need and hibernate the rest. You lot tin can apply its congenital-in operators to easily filter numbers, texts or dates such as filter all numbers which is greater than or equal to a certain number, filter text begins, ends or contains specific graphic symbol or give-and-take, or only show rows where the due date is earlier or after a sure date and and so on. Once you filter information in a range or a tabular array, if the information is changed, you can either reapply a filter to go the new data, or clear a filter to display all of the information.

In this tutorial, we will demonstrate how to add, utilise or remove filters in Excel. Across that, we will guide you how to enhance the filter feature to handle more complicated Excel problems.

This tutorial primarily covers: (Click on any heading in the table of contents below or on the right to navigate to the corresponding content.)

one. How to add filter in Excel

To filter data in a range or table, firstly y'all need to add filter to your data. This section provides 3 ways to add filter in Excel.

1.i Filter control on Information tab

Select any cells in a range or table you want to add filter, click Information > Filter.

1.2 Filter command on Data tab

Select any cells in a range or table yous desire to add together filter, click Home > Sort & Filter > Filter.

1.3 Add filter with shortcut

Select any cells in a range or table you want to add filter, and then printing Ctrl + Shift + L keys.

After applying ane of the above operations, you tin encounter drop down arrows are added in the column headers of selected cells.

2. How to utilise filter in Excel (one or multiple criteria)

After adding the filter, yous need to manually apply it. This section is going to bear witness you how to apply filter in one or more columns in Excel.

2.ane Utilize filter to one cavalcade (one criteria)

If you lot merely desire to apply the filter to one column, such every bit filter data in column C as the below screenshot shown. Please go to that cavalcade, then do as follows.

one) Click on the driblet down arrow in the column header.

2) Specify a comparison from the built-in operations. Or you lot tin uncheck the Select All box to deselect all data, and and then only check the boxes next to the data you want to show.

3) Click the OK button to start filtering. See screenshot:

Now the filter is applied to cavalcade C. All yous specified data are displayed and the residuum are hidden.

Afterward applying filter, you lot can run into the drop down arrow turns to a filter icon .

It is very considerate that when you hover the cursor over the filter icon, the filter criteria you have specified will be displayed as a screen tip as the below screenshot shown. So, if you forget the criteria you have specified to a filter, only hover the cursor over the filter icon.

two.2 Apply filter with multiple criteria to multiple columns (multiple criteria)

2.two.1 Utilize filter with multiple criteria to multiple columns one past one

If y'all want to apply filter to multiple columns with multiple criteria, simply repeat the above method to multiple columns ane by one.

Later on applying filter to multiple columns, you can see the drib down arrows in filtered columns are turned to filter icons.

2.2.2 Simultaneously use filter with multiple criteria to multiple columns

With the above method, you need to apply filter to columns one by one, and the nearly important betoken is that this method only supports AND criteria. Here introduce the methods for you not only apply filter to multiple columns simultaneously, but as well utilise both the AND and OR criteria.

Supposing you have a data table as the below screenshot shown, and want to filter data from multiple columns based on multiple criteria: Product = AAA-i and Order > 80, or Total Price > 10000. Please endeavour ane of the following methods to get it done.

2.two.2.1 Apply filter to multiple columns with the Advanced Filter office

The Advanced Filter function may help you solve this problem, please exercise every bit follows step by step.

1. Firstly, create the criteria in the worksheet as the below screenshot shown.

Note: For the AND criteria, please place the criteria values in different cells of the aforementioned row. And identify the OR criteria value on the other row.

2. Click Data > Advanced to turn on the Advanced Filter office.

3. In the Advanced Filter dialog box, please configure as follows.

three.1) In the Action section, select the Filter the listing, in-place option;

iii.2) In the List range box, select the original data range or table y'all desire to filter (here I select A1:D9);

iii.3) In the Criteria range box, select the range containing the criteria values you take created in step i;

iii.4) Click the OK push button.

Now the columns are filtered simultaneously based on the given criteria as the below screenshot shown.

2.ii.2.2 Easily apply filter to multiple columns with an amazing tool

As the AND and OR filter criteria are not like shooting fish in a barrel to manage in higher up method, hither highly recommend the Super Filter feature of Kutools for Excel. With this feature, you can use filter to multiple columns with both AND and OR criteria hands in Excel.

1. After installing Kutools for Excel, click Kutools Plus > Super Filter.

Then the Super Filter pane is displayed on the right side of the worksheet.

By default, two bare criteria groups are added with OR relationship between them in the Super Filter pane. And the human relationship among the criteria in the aforementioned group is AND. You tin change the relationship betwixt different groups based on your needs.

2. In the Super Filter pane, delight configure the filter criteria equally follows.

2.1) Check the Specified box, click the button to select the original range or tabular array you will filter;

2.2) In the Relationship driblet-downwardly list, cull Or;

3.3) Click on the first bare line in the start group, and then specify the criteria based on your demand;

Tips: The outset drop-downward list is for column headers, the second is for filter types (you can cull Text, Number, Date, Year, Text format then on from this driblet-downwardly), the 3rd is for criteria types, and the final text box is for criteria value.

Every bit the example we mentioned above, here I cull Product > Text > Equals separately from the three drop-down lists, and and so type in AAA-i into the text box. Meet screenshot:

2.four) Keep creating the remainder criteria, and the Or criteria needs to create in a new group. As the below screenshot shown, all the criteria are created. You can delete the blank criterion from the groups.

2.5) Click the Filter button to start filtering.

Now only the matched data are displayed in the original data range, and the rest are hidden. See screenshot:

Tips: With this handy feature, you can add more criteria in a grouping, add together more groups, salvage current filter settings every bit a scenario for future using and then on. Information technology is an indispensable tool which tin can save a bunch of working time and heighten working efficiency.

If you want to have a free trial ( 30-day) of this utility, please click to download information technology, and then become to apply the functioning according in a higher place steps.

Click to know more well-nigh this feature.

three. How to use filter in Excel

In this section, yous will learn how to apply filter control to filter unlike kinds of information types such as text, numbers, dates and formats.

three.i Filter text values

3.1.1Filter text cells with specific criteria (begin with, terminate with, comprise and so on)

Actually, the built-in filter operator – Text Filters supplies many useful criteria for yous to easily filter text. Supposing y'all want to filter cells which begin with a specific character such equally J, please exercise as follows to become information technology washed.

1. Add filter to the cavalcade header of original data range. Click to know how.

2. Click the drop downwards arrow in the header cell to unfold the filter menu.

3. Click Text Filters > Begins With.

4. In the Custom AutoFilter dialog box, enter the specific character (here I type in a J) into the text box, and then click OK.

Tips: You can add another And or Or relationship criteria as you demand.

Now all cells begin with graphic symbol J are displayed in cavalcade D every bit the beneath screenshot shown.

3.1.2 Filter with case sensitive

It seems that it is easy to filter text cells based on specific criteria with the built-in filter operator. However, as the Filter function does non back up filtering text with case sensitive, how can we do a example sensitive filter in Excel? This department will testify you lot methods to achieve it.

3.1.ii.1 Filter certain text with case sensitive by formula and the Filter command

Supposing you want to filter all uppercase of a certain text such equally "TEXT TOOLS" in column B, please practice as follows.

1. Create a helper cavalcade besides the original data range (here I choose the column D as the helper cavalcade). Enter the beneath formula into the second cell and and then press the Enter key. Select the result cell, elevate its AutoFill Handle down to get the other results.

=EXACT(B2,UPPER(B2))

Notation: This formula helps to identify the upper-case letter and lowercase cells. If a prison cell contains all uppercase characters, the result will be Truthful, otherwise, yous volition get the issue as Imitation.

2. Select column B and D (the column C will be selected, never heed), click Data > Filter to add filters to them.

iii. Go to column B (the column contains the texts yous will filter), and and so configure as follows.

3.i) Click the driblet down arrow in column B;

three.2) Uncheck the Select All box to deselect all items;

3.3) Check the check box next to TEXT TOOLS;

3.four) Click the OK button. See screenshot:

Now merely uppercase and lowercase of "text tools" are displayed in cavalcade B.

iv. Click the drib down pointer in column D, uncheck the Select All check box, check the cheque box next to the True item, so click the OK button.

Then all uppercase of text "text tools" in column B are filtered as the beneath screenshot shown.

three.1.2.2 Easily filter cells with instance sensitive with an amazing tool

If you only desire to filter all capital or lowercase text in a cavalcade, hither recommend the Special Filter characteristic of Kutools for Excel. With this characteristic, all uppercase or lowercase text volition be filtered easily with only several clicks.

1. Select the column range you will filter texts inside, and so click Kutools Plus > Special Filter > Special Filter.

2. In the Special Filter dialog box, please do the below settings.

2.i) In the Range box, you can see the selected range are listed out. Yous can alter the range as y'all demand;

2.2) In the Filter rules section, cull the Text pick, and so select Majuscule Text or Lowercase Text from the below drop downwardly list;

two.3) Click OK.

3. Then a Kutools for Excel dialog box pops upwardly to tell yous how many cells are found and will exist filtered, click the OK push button.

Now all uppercase or lowercase cells are filtered immediately as the below screenshot shown.

If y'all want to take a complimentary trial ( 30-day) of this utility, please click to download it, then get to apply the operation according above steps.

Click to know more almost this characteristic.

three.1.3 Filter by text length

If you want to filter cells by text length, for example, to filter cells with text lengths equal to ten characters, what should you practice? Here the below three methods can do you lot a favor.

three.1.iii.1 Filter cells by text length with the Filter command

Actually, the Filter command has the built-in operator to solve this trouble, please do as follows.

1. Select the range of cells that to be filtered (hither I select B1:B27), and and then add a filter to this column range by clicking Data > Filter.

2. Click the drop downwardly arrow in the column header, and so click Text Filters > Custom Filter. Come across screenshot:

3. In the Custom AutoFilter dialog box, select criteria as equals, type ten question marks (?) equally pattern mode into the text box, and then click the OK button.

Tips: These x question marks bespeak that information technology will match the text string that the length is x.

At present all cells that the text cord length is 10 (include spaces) are filtered immediately.

3.1.iii.two Filter cells by text length with formula and the Filter command

Besides, you tin can utilize the LEN function to summate the text string length of each cell, and and then use the Filter command to filter the needed text length cells based on the calculated outcome.

i. Create a helper column next to the original data range. Enter the below formula into it and then press the Enter key. Select the result prison cell and then elevate its AutoFill Handle down to go the other results.

=LEN(B2)

Now you are getting the text length of each prison cell in specified cavalcade.

ii. Select the helper cavalcade (include header), click Information > Filter to add a filter to information technology.

3. Click the drop-down arrow, uncheck the Select All box to deselect all items, and and so simply cheque the box next to number 10, and finally click the OK button.

At present all cells that the text string length is ten (include spaces) are filtered immediately.

3.i.3.3 Easily filter cells by text length with an astonishing tool

Here recommend the Special Filter utility of Kutools for Excel to aid yous hands filter cells by text length in Excel.

i. Select the column range you will filter cells based on certain text length, click Kutools Plus > Special Filter > Special Filter. Meet screenshot:

ii. In the Special Filter dialog box, please configure every bit follows.

2.1) The selected range displays in the Range box, y'all can change information technology every bit you demand;

ii.2) In the Filter rules department, choose the Text pick;

two.three) Select Text length equal to option from the drib down list, and so enter number ten into the text box;

2.four) Click OK.

3. A Kutools for Excel dialog box pops up to tell y'all how many cells are found and will be filtered, click OK to go ahead.

Then all cells with the text string lengths equal to 10 are filtered as the beneath screenshot shown.

If you lot want to take a complimentary trial ( 30-twenty-four hour period) of this utility, please click to download it, and then go to utilize the functioning co-ordinate higher up steps.

3.2 Filter numbers

In Excel, it is also very like shooting fish in a barrel to filter numbers with the Number Filters command.

Supposing you want to filter cells with numbers between 15000 and 20000 in a column (such as cavalcade C as the beneath screenshot shown), you can do as follows to achieve it.

1. Select the column range contains the numbers you will filter, click Data > Filter to add a filter.

2. After calculation filter, please configure as follows.

2.ane) Click the drop downward arrow to unfold the filter;

2.2) Click Number Filters > Between;

2.three) In the Custom AutoFilter dialog box, enter the criteria and and so click OK.

Tips: Every bit I want to filter cells with numbers between 15000 and 20000, hither I enter 15000 and 20000 separately into the text boxes.

At present cells with numbers between 15000 and 20000 are filtered as the below screenshot shown.

iii.3 Filter dates

By default, the built-in feature Date Filters provides many common criteria for filtering dates. As you can run across, there is no born option for filtering dates by mean solar day of week, weekends or workday. This section will teach you how to achieve these operations.

3.3.1 Filter dates by day of week or weekends

Supposing y'all take a data table every bit the below screenshot shown, if you want to filter dates past day of week or weekends, please use one of the below methods.

3.three.one.1 Filter dates by twenty-four hour period of week or weekends with formula and the Filter control

In this department, you lot volition use the WEEKDAY office to summate the 24-hour interval of calendar week of each date, and then employ the filter to filter the certain day of week or weekends as you demand.

one. In a bare cell (D2 in this case), enter the below formula into it and printing the Enter primal. Select the outcome prison cell and and then drag the AutoFill Handle over the below cells to use this formula.

=WEEKDAY(A2)

Tips:

1) As we need to filter date cells based on the helper column values, the result values and the original date cells should be on the aforementioned rows.

2) In the formula, A2 is the starting time cell contains the appointment you lot want to filter.

Note: Equally you can see, the formula returns numbers from 1 to 7, which indicates the 24-hour interval of week from Sunday to Sabbatum (one is for Sunday, vii is for Saturday).

2. Select the whole formula results (include the header cell), click Data > Filter.

3. Click the drop down pointer, and so uncheck the Select All checkbox.

ane) If you want to filter all weekends, check the checkboxes next to number 1 and vii;

two) If you want to filter a certain 24-hour interval of week except weekends, check the checkbox next to the numbers except ane and 7. For example, you lot want to filter all Fridays, just check the checkbox adjacent to number 6.

So all weekends or certain twenty-four hours of week cells are filtered. Encounter screenshot:

three.three.1.ii Easily filter dates by twenty-four hours of calendar week or weekends with an amazing tool

If the above method is non convenient for you, here recommend the Special Filter utility of Kutools for Excel. With this characteristic, yous can easily filter cells containing whatsoever twenty-four hours of week with several clicks just.

1. Select the cells containing the dates you want to filter based on certain solar day of calendar week.

2. Click Kutools Plus > Special Filter > Special Filter.

3. In the Special Filter dialog box, delight configure as follows.

3.1) The selected range displays in the Range box. You can modify the range every bit y'all need;

three.2) In the Filter rules section, select the Date option, and and then cull an option from the drop down list.

For filtering all weekend cells, choose Weekend from the drop downward;

For filtering whatsoever day of week except for weekends, cull Monday, Tuesday, Wednesday, Thursday or Fri from the drib downwardly as y'all need.

iii.3) Click OK. Run into screenshot:

4. Then a Kutools for Excel dialog box pops upward with how many cells are found and volition exist filtered, click OK to go ahead.

At present all weekends or any day of week cells are filtered.

If you want to take a free trial ( thirty-day) of this utility, please click to download it, and so go to apply the performance co-ordinate above steps.

3.3.ii Filter cells by workdays with an astonishing tool

Likewise filter cells by day of week or weekends, the Special Filter utility of Kutools for Excel tin can also help to filter cells by workdays.

one. Employ the same steps every bit above to enable the Special Filter utility.

2. In the Special Filter dialog box, do the below settings.

2.one) The selected range displays in the Range box. You can change it as you need;

ii.2) In the Filter rules department, select the Date option, and so choose Workdays from the drop downward list;

2.three) Click OK. Run into screenshot:

3. Then a Kutools for Excel dialog box pops up. Click OK to go ahead.

Now all workday cells are filtered.

3.4 Filter formats

Normally, Excel supports filtering data based on visual criteria such as font color, cell color or icon sets as the below screenshot shown.

However, if you want to filter data based on other visual criteria such as font manner (bold, italics), font effects (strikethrough) or special cell (containing formulas), Excel does non help to accomplish. This section provides methods to help you solve these problems.

3.four.1 Filter by bold/italics formatted text

Supposing you desire to filter information by bold or italics formatting text equally the below screenshot shown, the following methods can do you a favor. Delight do as follows.

3.4.1.i Filter assuming/italics formatted text with formula and the Filter command

The combination of the Get.Cell formula and Filter command tin can assistance to filter bold formatted text in a column range.

1. Click Formulas > Ascertain Name.

ii. In the New Name dialog box, you need to:

two.1) Blazon a name into the Name box;

ii.two) Select Workbook from the Scope drop-down list;

2.3) Enter the below formula into the Refers to box;

For filtering bold text cells, apply the below formula:

=Go.CELL(20,$B2)

For filtering italics text cells, utilize this one:

=Go.CELL(21,$B2)

2.iv) Click OK. Run into screenshot:

Formula Syntax:

=Become.CELL(type_num, reference)

Formula Arguments

Type_number: is a number that specifies what type of prison cell information yous want;

Hither nosotros type in number 20, if the cell has a bold font format, It returns TRUE, otherwise, information technology returns FLASE.

Or you tin can go to this page to know more than near the Type_num and the corresponding results.

Reference: is the cell reference that you desire to analyze.

3. Select a blank jail cell in the same row of B2, type in the beneath formula into it and then press the Enter key. Select the result cell, drag its AutoFill Handle over the below cells to apply this formula.

=Filter_Bold_Cells

4. Select the whole result cells (include header), click Information > Filter.

5. Click the drop downward arrow, but check the box next to the TRUE option, and and then click OK.

Then all bold or italics text cells are filtered. See screenshot:

three.iv.1.2 Filter bold or italics formatted text with the Discover and Replace and Filter commands

You lot tin also apply the combination of Find and Replace and Filter commands to achieve.

1. Select the column range containing the bold or italics text cells yous volition filter, so press Ctrl + F keys.

2. In the Find and Replace dialog box, you need to configure as follows.

ii.1) Click the Options push to expand the dialog box;

2.2) Click the Format button;

2.3) In the opening Observe Format dialog box, click the Font tab, choose Italic or Bold in the Font fashion box, and so click OK;

two.4) When it returns to the Find and Replace dialog box, click Find All;

ii.5) Then all results are listed in the dialog box, select one of them, and so press the Ctrl + A keys to select all of them;

2.six) Close the Find and Supersede dialog box. See screenshot:

3. Now all bold or italics text cells are selected in the original range, click Habitation > Fill up Colour, and then cull a fill color for the selected cells.

4. Select the whole column range over again, click Data > Filter to add a filter to it.

five. Click the drib down arrow, select Filter by Color, and then click the fill color yous have specified just at present under Filter by Cell Color. Run into screenshot:

Then all bold or italics text cells are filtered.

3.4.1.3 Easily filter bold or italics formatted text with an amazing tool

Equally we mentioned above, the Special Filter utility of Kutools for Excel can help to easily filter with case sensitive, filter past text length, filter dates. Hither, nosotros will teach how to utilise this feature to filter bold or italics formatted text cells in Excel.

1. Select the column range (include header) contains the bold or italics formatted cells you will filter.

2. Click Kutools Plus > Special Filter > Filter Bold / Filter Italic. See screenshot:

3. Click OK in the popping upwardly Kutools for Excel dialog box to go ahead (this dialog box pops up to tell you how many cells meet the criteria).

Now all assuming or italics formatted text cells are filtered.

If you want to have a free trial ( xxx-twenty-four hours) of this utility, please click to download it, and then get to apply the performance according in a higher place steps.

iii.4.2 Filter by strikethrough formatted text

Supposing you lot take received a listing with people cutting past calculation strikethrough to them, and demand to observe out all strikethrough cells past filtering, the below methods tin do you a favor.

3.4.two.1 Filter strikethrough formatted text with User-divers function and the Filter command

Y'all can use the User-defined function to identify the strikethrough formatted text cells, and then employ the Filter control to filter all strikethrough cells based on the results.

1. Press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module. And then copy the below VBA lawmaking into the Code window.

            Role HasStrike(Rng As Range) As Boolean HasStrike = Rng.Font.Strikethrough Stop Function

three. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

four. Select a blank prison cell (this jail cell should be in the same row of the cell you desire to calculate), enter the below formula and press the Enter key. Select the result prison cell, drag its AutoFill Handle over the below cell to use this formula.

=HasStrike(B2)

Annotation: If the respective cell has strikethrough font upshot, it returns TRUE, otherwise it returns False.

5. Select the whole result cells (include the header cell), click Data > Filter.

6. And then click the drop downwards pointer > but bank check the box next to the TRUE option > click OK. See screenshot shown.

Now yous can see all strikethrough formatted cells are filtered.

iii.4.2.2 Easily filter strikethrough formatted text with an amazing tool

With the Special Filter utility of Kutools for Excel, all strikethrough formatted cells can be filtered direct with several clicks only.

1. Select the cavalcade range yous will filter all strikethrough formatted cells, click Kutools Plus > Special Filter > Filter Strikethrough.

ii. Then a Kutools for Excel dialog box pops upward to tell you how many cells are qualified, click OK to go ahead.

And then all strikethrough formatted cells are filtered as the below screenshot shown.

If yous want to take a free trial ( thirty-solar day) of this utility, delight click to download information technology, and then go to utilise the operation according to a higher place steps.

3.four.3 Filter by font or background color

As we mentioned at the very beginning in this filter formats section, Excel supports filtering data based on visual criteria such every bit font color, jail cell color or icon sets with its built-in feature. This department demonstrates how to apply the Filter by Color characteristic to filter cells past font or background color in details. Meanwhile, nosotros recommend a handy third-party office to assist solve this problem.

iii.4.iii.1 Filter by one font or groundwork colour with the Filter command

You tin directly apply the Filter by Color feature of Filter command to filter cells by specific font or background colour in Excel.

1. Select the cavalcade range you volition filter cells past font or background colour, and and so click Data > Filter.

2. Click the drop down arrow > Filter past Colour. And then yous can see all cell colors and font colors of current column range are listed out. Click on any cell color or font color will filter all cells based on information technology.

3.4.3.2 Filter by multiple background colors with User-defined office and the Filter command

If you want to filter cells past multiple groundwork colors, please apply the below method.

Supposing you want to filter all cells with orange and blueish groundwork colors in column B as the below screenshot shown. Firstly, yous need to summate the color index of these cells.

1. Printing the Alt + F11 keys simultaneously.

ii. In the Microsoft Visual Basic for Applications window, click Insert > Module. And so copy the below VBA code into the lawmaking window.

VBA code: Go cell background colour index

            Function GetColor(x Equally Range) As Integer GetColor = x.Interior.ColorIndex Finish Function

3. Printing the Alt + Q keys to close the Microsoft Visual Bones for Applications window.

iv. In a new column, type a header into the first cell (this cell should be on the aforementioned row of the header of the original range).

5. Select a blank cell side by side to the header prison cell (here I select E2), enter the below formula into it and press the Enter key. Select the upshot cell, and and so drag its AutoFill Handle over the beneath cells to apply this formula.

=GetColor(B2)

Note: If the jail cell has no fill color, information technology returns -4142.

vi. Select the helper column cells (include header), click Data > Filter to add together a filter to the column.

7. Click the driblet-down arrow to open up the drib-downward list, and then configure as follows.

7.ane) Uncheck the box next to the Select All item to deselect all items;

seven.2) Only bank check the boxes adjacent to the numbers you need to display. In this example, I bank check the boxes next to number 19 and xx, as xix is the colour index of background "orange", and twenty is the colour index of background "blue";

vii.3) Click OK. See screenshot:

Now cells are filtered by specified groundwork colors as the below screenshot shown.

3.4.3.3 Easily filter by font or background color with an amazing tool

Undoubtedly, information technology is easy to use the Filter by Colour congenital-in feature to filter cells by font or background color. However, the disadvantage is that the drop-downwardly listing box covers the content of the original data so that we cannot view the data at any time for properly choosing a font or background color. To avert this problem, hither recommend the Special Filter utility of Kutools for Excel.

1. Select the column range you will filter cells by font or background color, and so click Kutools Plus > Special Filter > Special Filter.

two. In the Special Filter dialog box, please configure as follows.

2.ane) The cell accost of selected cavalcade range displays in the Range box;

2.2) In the Filter Rules section, select the Format selection;

ii.three) Choose Background Color or Font Color from the driblet down listing;

two.4) Click the straw icon ;

2.5) In the opening Filter special cells dialog box, select a cell containing the font colour or background color you volition filter based on, and and then click OK.

two.6) When information technology returns to the Special Filter dialog box, the font color or the background color of selected jail cell is displaying in the text box (you can alter the color equally y'all need), click OK push to offset filtering cells.

Then all cells with specified font colour or groundwork color in selected range are filtered.

If you desire to take a free trial ( 30-twenty-four hours) of this utility, please click to download information technology, and and then become to apply the functioning co-ordinate higher up steps.

3.four.iv Filter cells containing formulas

If you accept a long list of data which contains both bodily values and formulas, and only demand to filter the formula cells, what can you exercise? This section provides two methods to achieve it.

3.iv.4.one Filter formula cells with User-divers function and the Filter command

Firstly, yous need to find out all formula cells in the list with the User-defined role, then apply the Filter control to filter the formula cells based on the results.

i. Printing the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and so re-create the below VBA code into the Code window.

            Function HasFormula(Cell) HasFormula = Cell.HasFormula End Function

3. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

4. Select a blank jail cell, this cell should be in the same row of the cell you desire to check if it is a formula cell, enter the below formula into it and then printing the Enter central. Select the effect cell, elevate its AutoFill Handle over the beneath cells to apply this formula.

=HasFormula(C2)

As the higher up screenshot shown, the results are Fake and True, which betoken that if the corresponding cell is a formula cell, it returns TRUE, otherwise returns FALSE.

five. Select the result cells (include the header cell), click Data > Filter to add a filter to information technology.

six. Click the drib down pointer, merely check the box next to the Truthful box, and then click OK.

Then you can see all formula cells are filtered.

3.iv.4.2 Easily filter formula cells with an amazing tool

Here demonstrate the Special Filter utility of Kutools for Excel to assist you lot easily filter formula cells in a listing with several clicks merely.

1. Select the list y'all want to filter all formula cells, and so click Kutools Plus > Special Filter > Filter Formula.

two. A Kutools for Excel dialog box pops up to tell you how many cells are qualified, click OK to go ahead.

Then all formula cells are filtered every bit the below screenshot shown.

In addition, you can use the Special Filter feature of Kutools for Excel to hands filter cells with other formatting, such as:

Filter all cells with comments, click to know more...

Filter all merged cells based on certain value, click to know more...

If y'all want to accept a free trial ( 30-day) of this utility, delight click to download it, and then go to apply the operation according above steps.

iii.five Filter with wildcard

Sometimes, you may forget the verbal search criteria when filtering. In this case, we recommend you to use the wildcard characters.

There are only 3 wildcard characters in Excel:

Wildcard character	Description	Example
* (asterisk)	Represents any number of characters	For instance, berry finds "Blackberry", "Strawberry", "Blueberry*" and so on
? (question mark)	Represents any single graphic symbol	For example, l?ck finds "lick", "lock", "lack" and so on
~ (tide) followed by *, ?, or ~	Represent a real *, ? or ~ graphic symbol	For example, *Face~Store** finds "Face*Shop"

Let's run into how to utilize wildcard in filtering.

Supposing you need to filter all cells that end with Market in column B equally the below screenshot shot, please do as follows.

ane. Firstly, create a criteria range. Enter a header the aforementioned as the original column header, and then blazon the filter criteria in the beneath cell. See screenshot:

two. Click Data > Advanced.

3. In the opening Advanced Filter dialog box, configure every bit follows.

3.ane) In the Action section, select the Filter the list, in-place option;

3.2) In the List range box, select the original data range you desire to filter;

3.3) In the Criteria range box, select the cells containing the header and filter criteria you take created in footstep 1;

3.4) Click OK. Run into screenshot:

Then all cells end with Market are filtered. See screenshot:

The usage of * and ~ wildcard characters in filtering are the same as above operations.

iii.6 Filter with build-in search box

If you are using Excel 2010 or the later versions, y'all may notice that there is a build-in search box in Excel Filter. This section will demonstrate how to use this search box to filter data in Excel.

As the below screenshot shown, y'all want to filter all cells that contain "Marketplace", the search box can help yous to hands get it washed.

ane. Select the column range you volition filter data, click Data > Filter to add a filter to it.

2. Click the drop downward arrow, enter "Market" into the search box, and then click OK.

You can see all qualified texts are listed in existent fourth dimension while entering information in the search box.

And then all cells incorporate "Market" are filtered as the below screenshot shown.

four. Copy visible data merely (ignore hidden or filtered data)

By default, Excel copies both visible and hidden cells. If you want to but copy the visible cells later on filtering, y'all tin effort one of the below methods.

Copy visible data just with short cut keys

Y'all can use curt cut keys to merely select the visible cells, so manually re-create and paste them to a needed place.

1. Select the range you desire to copy the visible cells just. Both visible and subconscious cells are selected in this step.

2. Press the Alt and ; keys at the same time. Now only the visible cells are selected.

3. Press the Ctrl + C keys to re-create the selected cells, and then press Ctrl + V keys to paste them.

Easily copy visible information only with an amazing tool

Here introduce the Paste to Visible characteristic of Kutools for Excel for yous to easily copy visible data only in Excel. Besides, with this feature, you tin can copy and paste values to only visible cells in a range that has been filtered.

one. Select the filtered range y'all want to copy visible cells simply, so click Kutools > Range > Paste to Visible > All / Simply Paste Values.

For formula cells, cull All copies both the outcome and formulas, choose Only Paste Values only copies the actual values.

ii. In the popping up Paste to Visible Range dialog box, choose a bare cell to output the copied cells, and and so click OK.

So only visible cells in selected filtered range are copied and pasted to a new place.

Note: If the destination range has been filtered, the selected values will paste to the visible cells only.

If you desire to have a gratis trial ( xxx-day) of this utility, please click to download it, and then go to apply the operation according above steps.

5. Delete subconscious or visible rows afterwards filtering

For a filtered list, you may need to delete the subconscious rows in social club to go on simply the visible data. Here in this section, you lot will learn three methods to delete subconscious or visible rows in a filtered list in Excel.

Delete all hidden rows from current worksheet with VBA lawmaking

The below VBA code can help to delete all hidden rows from current worksheet in Excel.

Note: This VBA remove not only the subconscious row in filtered list, but also remove the rows yous have manually hidden.

i. In the worksheet contains subconscious rows you want to remove, press the Alt + F11 keys simultaneously to open up the Microsoft Visual Bones for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then copy the below VBA code into the Module window.

VBA code: Delete all hidden rows from electric current worksheet

            Sub RemoveHiddenRows()     Dim xRow As Range     Dim xRg Equally Range     Dim xRows Every bit Range     On Error Resume Next     Set xRows = Intersect(ActiveSheet.Range("A:A").EntireRow, ActiveSheet.UsedRange)     If xRows Is Nothing And then Go out Sub         For Each xRow In xRows.Columns(1).Cells             If xRow.EntireRow.Subconscious Then                 If xRg Is Null Then                     Set xRg = xRow                 Else                     Set xRg = Union(xRg, xRow)                 Stop If             End If         Side by side         If Not xRg Is Goose egg And then             MsgBox xRg.Count & " subconscious rows have been deleted", , "Kutools for Excel"             xRg.EntireRow.Delete         Else             MsgBox "No hidden rows found", , "Kutools for Excel"         End If Finish Sub

3. Press the F5 key to run the code.

4. Then a Kutools for Excel dialog box pops up to tell you how many hidden rows accept been removed, click OK to close it.

At present all hidden rows (include auto hidden rows and manually hidden rows) are removed.

Delete visible rows afterwards filtering with Become To feature

If you only want to remove visible rows in a certain range, the Go To feature can help you.

i. Select the filtered range you lot want to remove the visible rows, press the F5 key to open the Go To dialog box.

two. In the Become To dialog box, click the Special push button.

three. In the Go To Special dialog box, select the Visible cells just choice, so click OK button.

4. Now all visible cells are selected. Right click the selected range and click Delete Row in the context menu.

Then all visible cells are deleted.

Easily delete hidden or visible rows afterward filtering with an amazing tool

The above methods are slow and time-consuming. Here recommend the Delete Hidden (Visible) Rows & Columns feature of Kutools for Excel. With this feature, you lot can easily delete subconscious or visible rows not only in a selected range, but also in current worksheet, multiple selected worksheets or the whole workbook. Please do as follows.

1. Select a range you want to remove all hidden or visible rows from it.

Notes:

ane) For removing subconscious or visible rows from current worksheet or the whole workbook, ignore this footstep;

2) For removing hidden or visible rows from multiple worksheets at the same time, you need to select these worksheets i past ane past property the Ctrl key.

ii. Click Kutools > Delete > Delete Hidden (Visible) Rows & Columns.

3. In the Delete Hidden (Visible) Rows & Columns dialog box, you need to configure as follows.

3.one) In the Await in drop-down listing, choose an option as you need;

There are 4 options: In selected range, In agile sheet, In selected sheets, In all sheets.

3.2) In the Delete type department, choose the Rows options;

three.iii) In the Detailed blazon section, choose Visible rows or Hidden rows based on your needs;

three.four) Click OK. See screenshot:

4. And then all visible or hidden rows are deleted at once. Meanwhile, a dialog box pops upwards to tell you the number of deleted rows, click OK to terminate the whole performance.

If you want to take a free trial ( 30-twenty-four hours) of this utility, please click to download it, and then go to apply the performance according above steps.

6. Filter across multiple sheets

Normally, it is piece of cake to filter data in a worksheet. In this section, you volition learn how to filter data with same criteria across multiple worksheets that have common data structure.

Supposing a workbook contains three worksheets as the below screenshot shown, at present y'all want to filter information across these 3 worksheets at the same time with the aforementioned criteria "Product = KTE", the beneath VBA code can do you a favor.

one. Press the Alt + F11 keys to open the Microsoft Visual Bones for Applications window.

2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and and then re-create the below VBA code into the module window.

VBA code: Filter data beyond multiple worksheets at the same time

            Sub apply_autofilter_across_worksheets() 'Updateby Extendoffice 20210518     Dim xWs Equally Worksheet     On Error Resume Next     For Each xWs In Worksheets         xWs.Range("A1").AutoFilter 1, "=KTE"     Next End Sub

Notation: The line "Xws.Range("A1".AutoFilter i, "=KTE")" in the code indicates that you will filter data in cavalcade A with the criteria =KTE, and the number 1 is the cavalcade number of column A. You tin can change them to your need. For instance, if y'all want to filter all numbers that are greater than 500 in cavalcade B, you can change this line to "Xws.Range("B1".AutoFilter ii, ">500")".

three. Press the F5 fundamental to run the code.

Then the specified columns are filtered at the aforementioned time in all worksheets of current workbook. See the below results.

7. Re-apply filter afterward irresolute data

Sometimes you may accept made changes for a filtered range. Even so, the filter effect remains the same no matter what changes yous made to the range (see screenshot beneath). In this section, nosotros are going to bear witness you two methods to manually or automatically reapply the filter on the current range so that changes y'all've made are included.

Manually reapply filter with the Reapply command

Excel has a built-in Reapply feature to help manually reapply filter. You can employ every bit follows.

Click Data > Reapply to reapply the filter in current canvass.

Then you tin can see the filtered range are reapplied to include the changes you have made.

Automatically reapply filter with VBA code

If the filtered listing needs to be inverse ofttimes, you have to repeatedly click to apply this Reapply feature. Here provide a VBA code to help automatically reapply a filter in a real-time when data changes.

1. In the worksheet contains the filter you lot want to reapply automatically, right click the sheet tab and select View Code.

2. In the opening Microsoft Visual Basic for Applications window, copy the below VBA lawmaking into the Code window.

VBA lawmaking: Automatically reapply filter when changing information

            Individual Sub Worksheet_Change(ByVal Target Every bit Range)    Sheets("Sheet2").AutoFilter.ApplyFilter Finish Sub

Notation: In the code, "Sheet2" is the name of current worksheet. You can change it to your needs.

three. Press the Alt + Q keys to shut the Microsoft Visual Basic for Applications window.

From now on, when changing information in the filtered list, the filtered result will be dynamically adjusted. See the below gif picture.

eight. Clear or remove filter

Nosotros have learned how to add, utilise and utilize filter in the above content. Hither we are going to learn how to clear or remove filter in Excel.

viii.1 Clear a filter from a column

After applying filter in a cavalcade, if y'all want to clear it, click the filtered icon, and then click Clear Filter From "Header name" from the drib-down bill of fare.

8.ii Clear all the filters in a worksheet

If you accept applied filter to multiple columns, and want to clear all of them at the same time, click Information > Articulate.

Then all filters are cleared as the below screenshot shown.

8.iii Clear filters from all worksheets in current workbook

Supposing you have practical filters across multiple worksheets in a workbook and want to articulate these filters at one time. The beneath VBA code tin can do you lot a favor.

1. Open up the workbook you will articulate all filters from it, and then printing the Alt + F11 keys simultaneously.

ii. In the opening Microsoft Visual Basic for Applications window, click Insert > Module, and and so copy the beneath VBA code into the module window.

VBA code: Articulate filters from all worksheets in current workbook

            Sub Auto_Open() 'Updated by Extendoffice 20201113     Dim xAF As AutoFilter     Dim xFs As Filters     Dim xLos As ListObjects     Dim xLo As ListObject     Dim xRg As Range     Dim xWs As Worksheet     Dim xIntC, xF1, xF2, xCount As Integer     Application.ScreenUpdating = False     On Error Resume Adjacent     For Each xWs In Awarding.Worksheets         xWs.ShowAllData         Set xLos = xWs.ListObjects         xCount = xLos.Count         For xF1 = 1 To xCount          Set up xLo = xLos.Item(xF1)          Set xRg = xLo.Range          xIntC = xRg.Columns.Count          For xF2 = 1 To xIntC             xLo.Range.AutoFilter Field:=xF2          Next         Side by side     Adjacent     Awarding.ScreenUpdating = Truthful End Sub

iii. Printing the F5 key to run the code. Then all filters are cleared from all worksheets in current workbook.

8.4 Remove all filters in a worksheet

The above methods just help to clear the filtered condition, and the filters still remain in the worksheet. If y'all want to remove all filters from a worksheet, try the below methods.

Remove all filters in a worksheet by turning off the Filter

Click Information > Filter to turn off the feature (the Filter push is not in the highlight status).

Remove all filters in a worksheet by shortcut key

Besides, you tin can apply shortcut key to remove all filters in a worksheet.

In the worksheet contains filters yous want to remove, press Ctrl + Shift + L keys simultaneously.

Then all filters in electric current worksheet are removed immediately.

The Best Part Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

Reuse: Apace insert complex formulas, charts and annihilation that you have used before; Encrypt Cells with countersign; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
Select Duplicate or Unique Rows; Select Bare Rows (all cells are empty); Super Detect and Fuzzy Observe in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more than...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Avant-garde Sort past month/week/day, frequency and more; Special Filter past bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on fundamental columns; Separate Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organisation. Total features 30-solar day free trial. 60-twenty-four hours money back guarantee.

kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint , Publisher, Admission, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every twenty-four hours!

officetab bottom