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

Download Practice Workbook

You can download the practice workbook from here:

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.

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.

Read More: How to Go to Last Non Empty Cell in Column 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 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.

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.

Read More: How to Select Cells in Excel Using Keyboard (9 Ways)

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:

• 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 Quick Access Toolbar.
• 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.

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.

Read More: How to Select a Range of Cells in Excel (9 Methods)

Similar Readings

• How to Go to the End of Excel Sheet (2 Quick Methods)
• How Do I Quickly Select Thousands of Rows in Excel (2 Ways)
• [Solved!] CTRL+END Shortcut Key Goes Too Far in Excel (6 Fixes)
• Excel VBA to Protect Sheet but Allow to Select Locked Cells (2 Examples)
• How to Select Data in Excel for Graph (5 Quick Ways)

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.

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 a Range of Cells in Excel Formula (4 Methods)

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.

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 Highlighted Cells in Excel (4 Easy Techniques)

💬 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.

Conclusion

I hope you found this article helpful. Here, I have explained 5 suitable methods to select only Filtered Cells in Excel Formula. You can visit our website Exceldemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• How to Select Cells in Excel Without Dragging (7 Ideal Examples)
• How to Select Multiple Columns in Excel for Graph (3 Methods)
• [Fixed!] Selected Cells Not Highlighted in Excel (8 Solutions)
• How to Select Large Data in Excel Without Dragging (5 Easy Ways)
• Select Random Cells in Excel (5 Ways)
• How to Select & Delete Blank Cells in Excel (3 Quick Ways)