Often you may want to apply some formula in Excel only to filtered cells.
Fortunately this is straightforward to do and the following example shows how to do so.
Example: Apply Formula Only to Filtered Cells in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we would like to create a new column that uses a formula to display the value in the Points column doubled, but only for players who have a value of “Guard” in the Position column.
To do so, we can first filter the dataset to only show the rows where the value is “Guard” in the Position column:
Notice that players with a value of “Forward” or “Center” in the Position column are currently hidden.
Next, we can type the following formula into cell D2:
We can then manually click and drag this formula down to each remaining cell in column D:
Column D now shows the value of the corresponding cell in the Points column multiplied by 2.
Lastly, we can remove this filter and we’ll see that this formula has only been applied to the players with a value of “Guard” in the Position column:
The value in the new Double Points column is simply blank for all players who were not visible when we applied the filter.
The following tutorials explain how to perform other common operations in Excel: