Excel could be unexpected at times, especially when you copy and paste a cell range. You will notice all the cells are being copied and pasted even the hidden cells are also included in it. Today, you will get to know how to select only filtered cells in Excel formula. It does not matter if you have applied a filter to hide cells, rows, or columns, or subside an outline. You can still select only filtered cells in Excel by using multiple tricks.
Let’s dig in to see how it happens:
How to Select Only Filtered Cells in Excel Formula with a Keyboard Shortcut?
In the first method, you will get to learn a shortcut that lets you select filtered cells. You can use filtered cells in the Excel formula by selecting them by applying a keyboard shortcut. For this, you need to follow these steps:
- Select the Filtered cells.
- Press ALT +; keys from the keyboard to use the filtered cells in the Excel formula.
- Now, you need to enter the Excel formula according to the procedure. Without any other function, simply press the equal = sign to enter the formula.
- Excluding the equal = sign will not let you select the Filtered cell.
Below is the formula:
=C5*D5
In this example, you can see the Quantity with Unit Price is included to find the Sales value.
- Once the formula is entered, now press CTRL + ENTER and get all the Sales values.
- You can see the Filtered cells with the Sales value.
- The multiplication formula is being added to only the Filtered cells.
How to Select Only Filtered Cells in Excel Formula with Go To Special Feature?
With the Go To Special feature, you can choose Filtered cells to use in the Excel formula. To apply this method, you need to follow the steps given below:
- First, select cells to which you need to apply the Excel formula.
- Open the Home tab and click on the Editing menu.
- Choose Find & Select option from the menu and then click on the Go To Special option.
- Go To Special dialog box will appear.
- From this box, you need to check the Visible Cells Only option.
- Press OK and all the changes will get saved.
Now, enter the formula according to the procedure. Simply press the equal = sign to enter the formula. Without adding the equal sign, you cannot select filtered cells.
=D5*C5
Once again, you can see the formula includes a multiplication function that simply shows Unit Price with Quantity to find the Sales value.
- Press ENTER key from the keyboard or else you can press CTRL + ENTER.
- For pasting the formula, drag the Fill Handle icon.
- Eventually, you will get the Sales values.
- The multiplication formula is applied to only the Filtered cells.
All done!
Your filtered cells are now ready to be used in the Excel formula.
To Wrap Up:
Now, you have learned how to select only filtered cells in Excel formula with the help of multiple methods. Excel itself has many features and tricks that can assist you in the execution of different procedures. You just have to practice each method, so that you can grip over each function.
