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

Get FREE Advanced Excel Exercises with Solutions!

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 them in Excel Formula.

## 5 Methods to Select Only Filtered Cells in Excel Formula

Here, I will describe 5 suitable methods to select only Filtered Cells to use them 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 them 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.
`=D5*C5`

In this formula, simply I have multiplied Unit Price with 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. ### 2. Using Keyboard Shortcuts to Select Only Filtered Cells in Excel Formula

You can apply the Keyboard Shortcuts to select only Filtered Cells to use them in Excel Formula. The steps are given below.

Steps:

• Firstly, you have to select the Filtered cells.
• Secondly, press 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.
`=C5*D5`

In this formula, simply I have multiplied Quantity with 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. ### 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 them in Excel Formula. The steps are given below.

Steps:

• Secondly, you need to choose the More Commands option. At this time, a window named Excel Options will appear.

• Secondly, choose All Commands in the Choose commands from box:.
• Thirdly, you need to select 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.
`=C5*D5`

In this formula, simply I have multiplied Quantity with 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. ### 4. Use of SUBTOTAL Function to Select Only Filtered Cells

You can use the SUBTOTAL function as 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.
`=SUBTOTAL(9,E5:E10)`

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.
`=SUBTOTAL(4,E5:E10)`

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. ### 5. Applying AGGREGATE Function to Select Only Filtered Cells

You can use the AGGREGATE function as 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.
`=AGGREGATE(2,5,E5:E10)`

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.
`=AGGREGATE(1,5,E5:E10)`

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. ## 💬 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. ## Related Articles #### Musiha Mahfuza Mukta

I am Musiha, graduated from Naval Architecture and Marine Engineering Dept., BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  