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

**Table of Contents**hide

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

`=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**.

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

`=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**.

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

`=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**.

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

`=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.

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

`=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.

**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)**