How to Select Only Filtered Cells in Excel Formula (5 Quick Ways)

Sometimes you may need to apply Excel Formulas within some Filtered Cells. If you know how to select only Filtered Cells in Excel Formula, then it will be very easy for you. Thus, I will demonstrate how to select only Filtered Cells to use in Excel Formula.

How to Select Only Filtered Cells in Excel Formula (5 Easy Methods)

Here, I will describe 5 suitable methods to select only Filtered Cells to use in Excel Formula. In addition, for your better understanding, I’m going to use the following dataset, which contains 3 columns. They are States, Quantity, and Unit Price.

1. Employing Go To Special Feature to Select Only Filtered Cells in Formula

You can employ the Go To Special feature to select only Filtered Cells to use in Excel Formula. Here, let’s have the following Filtered dataset.

Now, I’m going to talk about the steps.

• Firstly, you have to select the cells for which you want to apply the Excel formula.
• Secondly, from the Home tab >> go to the Editing feature >> then select Find & Select command >> after that choose Go To Special option.

At this time, a dialog box named Go To Special will appear.

• Now, you need to check Visible cells only.
• Then, press OK to get the changes.

• Subsequently, write down the formula according to your necessity. The most important part is that you should not click anything else after the previous step. Actually, you should immediately press the Equal (=) sign to write the formula. Otherwise, the selected Filtered cells may not remain selected.
• Here, I have written the following formula.

In this formula, simply I have multiplied the Unit Price by Quantity to get the Sales amount.

• After that, press ENTER to get the result. Or, you can press CTRL+ENTER to get the result for all the Filtered cells.

• Now, drag the Fill Handle icon to paste the used formula respectively to the other Filtered cells of the column (E6, E8, and E10).

Lastly, you will get the Sales amount for all the Filtered cells.

Here, you can see the multiplication formula applied in only the Filtered cells.

Read More: Select All Cells with Data in Excel 

2. Using Keyboard Shortcuts to Select Only Filtered Cells in Excel Formula

You can apply the Keyboard Shortcuts to select only Filtered Cells using the Excel formula. The steps are given below.

Steps:

• Firstly, you have to select the Filtered cells.
• Secondly, press the ALT+;  keys to use Excel Formula for only Filtered Cells.

• Subsequently, write down the Excel formula according to your necessity. The most important part is that you should not click anything else after the previous step. Actually, you should immediately press the Equal (=) sign to write the formula. Otherwise, the selected Filtered cells may not remain selected.
• Here, I have written the following formula.

In this formula, simply I have multiplied Quantity by Unit Price to get the Sales amount.

• Then, press CTRL+ENTER to get all the Sales amount.

Lastly, you will get the Sales amount for all the Filtered cells.

Here, you can see the multiplication formula applied in only the Filtered cells.

Read More: Select All Cells with Data in a Column in Excel 

3. Use of Quick Access Toolbar to Select Only Filtered Cells

You can use the Quick Access Toolbar to select only Filtered Cells to use in the Excel formula. The steps are given below.

Steps:

• Firstly, click on the Customize Quick Access Toolbar.
• Secondly, you need to choose the More Commands option.

At this time, a window named Excel Options will appear.

• Firstly, select the Quick Access Toolbar.
• Secondly, choose All Commands in the Choose commands from section.
• Thirdly, you need to select the Select Visible Cells.
• Fourthly, press on Add >>.
• Finally, click on OK to get the changes.

As a result, you will get a new Toolbar named Select Visible Cells.

• Now, select the Filtered cells.
• Then, click on the Select Visible Cells Toolbar.

• Subsequently, write down the formula according to your necessity. The most important part is that you should not click anything else after the previous step. Actually, you should immediately press the Equal (=) sign to write the formula. Otherwise, the selected Filtered cells may not remain selected.
• Here, I have written the following formula.

In this formula, simply I have multiplied Quantity by Unit Price to get the Sales amount.

• Then, press CTRL+ENTER to get all the Sales amount.

Lastly, you will get the Sales amount for all the Filtered cells only.

Read More: How to Select Cells with Certain Value in Excel

4. Use of SUBTOTAL Function to Select Only Filtered Cells

You can use the SUBTOTAL function as an Excel formula to apply only for Filtered Cells. The steps are given below.

Steps:

• Firstly, you have to select a cell where you want to keep the result.
• Secondly, write down SUBTOTAL in that cell. As you can see there are lots of functions under the SUBTOTAL function. So, you can choose your preferred one.

Here, I will find out the summation of the Filtered Sales.

• Now, write down the corresponding formula in cell E16.

In this formula, 9 denotes the SUM Function. Which will return the summation of the data range E5:E10. But, this SUBTOTAL function will consider only the visible cell values.

• Then, press ENTER to get the result.

Now, I will show you the use of another function under the SUBTOTAL function. Which is the MAX Function.

• So, write down the corresponding formula in cell E17.

In this formula, 4 denotes the MAX function. Which will return the maximum value of the data range E5:E10. But, this SUBTOTAL function will consider only the visible cell values.

• Then, press ENTER to get the result.

Here, the fact is that if you unfiltered the cells then those formulas will be applicable for all the cells.

Read More: How to Select Random Cells in Excel

5. Applying AGGREGATE Function to Select Only Filtered Cells

You can use the AGGREGATE function as an Excel formula to apply only for Filtered Cells. The steps are given below.

Steps:

• Firstly, you have to select a cell where you want to keep the result.
• Secondly, write down AGGREGATE in that cell. As you can see there are lots of functions under the AGGREGATE function. So, you may choose your preferred one.

• Here, to apply the formula in the Filtered cells only, choose 5 which will ignore all the hidden rows.

Now, I will find out the total cell number of the Filtered Cells.

• At this time, write down the corresponding formula in cell E16.

In this formula, 2 denotes the COUNT function. Which will return the total cell count of the data range E5:E10. Then, 5 denotes that this function will ignore only the hidden rows.

• Then, press ENTER to get the result.

Now, I will show you the use of another function under the AGGREGATE function. Which is the AVERAGE function.

• Now, write down the corresponding formula in cell E17.

In this formula, 1 denotes the AVERAGE function. Which will return the average of the data range E5:E10. Then, 5 denotes that this AVERAGE function will ignore only the hidden rows.

• Then, press ENTER to get the result.

Here, the fact is that if you unfiltered the cells then those formulas will be applicable for all the cells.

Read More: How to Select Blank Cells in Excel and Delete 

💬 Things to Remember

• Here, in the case of method 4 (SUBTOTAL) and 5 (AGGREGATE), if you unfiltered the cells then your formula will be applicable for all the cells.
• With methods 1, 2, and 3 you can apply many Excel formulas. Not only this but also when you unfiltered the data range the output remains constant.

Practice Section

Now, you can practice the explained method by yourself.

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

I hope you found this article helpful. Here, I have explained 5 suitable methods to select only Filtered Cells in Excel Formula. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Select Highlighted Cells in Excel 
• [Fixed!] Selected Cells Not Highlighted in Excel
• Selecting Non-Adjacent or Non-Contiguous Cells in Excel

<< Go Back to Select Cells | Excel Cells | Learn Excel