This tutorial talks about how to select cells with formula in Excel. In tutorial below I have compiled various methods to do the same. And the best part is that most the methods can select cells with formula without using any other third party software. I have also added a plugin in the following list to select cells in Excel that contain specific formula.
Selecting cells that contain formula or specific formula can be useful in many cases. Let’s say, you have an Excel sheet that has tons of cells in it with various formulas and you want to delete or change those cells. Doing it manually will take a fair amount of time. That’s where this tutorial comes in handy. In this tutorial I will explain how to select cells with formula in Excel.
You may already know how to select and count cells by background or font color. In the same way you can also select cells with formula in Excel with the help of the following methods. Let’s see how.
Do note that there could be various scenarios of selecting cells with formaula in Excel. You might want to simply select all the cells that have any formula, or you might want to select only those cells that have a spefic formula. I have explained all these scenarios here. If you have a selection scenario that I missed to cover here, do let me know in comments, and I will try to find a way for that as well.
How to Select Cells with Formula in Excel
This is the most basic scenario in which you want to simply select all the cells in a worksheet or selection that contain a formula. I will use the “Go to Special” tool of Excel to do the same. This Excel tool lets you select all the cells that have formula in them. Also, using the same tool, you can also select cells based on what is the output of formula in them. For example, you can choose to select only those cells in which formula would always product a numeric output (like, Sum, Avg, etc.).
To select cells that contain a formula, follow these simple steps.
Step 1: Open your Excel sheet in which you want to select the cells that have formula. And invoke Go to Special Tool from the Home ribbon.
Step 2: From the Go To Special tool’s interface, check the Formula option and you can also filter the result by specifying the output type of the formula.
Step 3: To select all the cells containing a formula, hit the OK button. After that you will see all the cells that have formula have been selected.
This is the fastest and easiest way to select cells with formula in Excel. And after you have your selected cells, you can do whatever you want to do with them.
How to Select Cells That Have a Specific Formula in Excel
Let’s say you don’t want to select all the cells that have a formula, but only want to select those cells that have a specific formula. That is possible as well. You would be surprised to know that “Find” tool of the Excel will do it for you. The Find tool of Excel is very powerful. This tool can not only find the value, but also list all the cells that contain the specified formula text. And after that you can do whatever you want with the selected cells.
Follow these simple steps to select cells in Excel that contain a specific formula:
Step 1: Open any Excel sheet that contains cells with formula that you want to select. And click on the Find tool from the end of the Home tab of Excel. Or just to Ctrl+F.
Step 2: Now, Expand the interface of the Find tool by clicking on the Options button. After that, navigate to “Look in” drop down and select “Formulas” from it.
Step 3: Type the formula text in the “Find What” text box, and hit Find All button. You will see that it will list all the cells that contain that formula text.
Now, if you want to select all the cells, click on any empty space in the result cells list and press Ctrl+A keyboard shortcut. After that, all the cells will get selected and you may now close the Find tool.
So, in this way you can easily select cells in Excel that contain a specific formula.
How to Select Cells With Formula in Excel that have a Specific font or font color
Let’s say you have a crazy scenario that you want to select all the cells in Excel that have a formula, and also they have a specific font, or a specific font color. I am not sure why you would run in this type of scenario, but in case you do, worry not. Here I have provided a solution for that too.
In this method I will use some filters along with the Find tool of Excel. After searching a specific formula text, the method will list all the cells that have the specified font or font color.
Here is how to do this:
Step 1: Open up any worksheet that contains some formatted cells and also have formulas in them. Open Find tool from the Home ribbon.
Step 2: Now, Expand the interface of the Find & Select tool by clicking on the Options button. After that, navigate to “Look in” drop down and select “Formulas” from it.
Step 3: Click on Format button and a window will pop up with various tabs it. Navigate to the Fonts tab and specify the font type there. For saving time, you can also directly specify font formatting from an existing cell from your sheet. To do this use the Choose Format From Cell button and specify the cell by selecting it from your Excel sheet.
Step 4: Type the formula text in the Find What text box, and hit Find All button. You will see that it will list all the cells that contain that formula text and has the font formatting that you selected.
Now, if you want to select all the cells then click on any empty space in the result window and press the Ctrl+A keyboard shortcut. After that, all the cells will get selected and you may now close the Find & Select tool.
So, in this way you can select all the cells that contain a specific formula and font formatting. And you can do it in seconds.
Now, what if you want to select all the cells that had any formula, but have a specific formatting. For that, you can use Asap Utilities, that I have covered at the end of this tutorial.
Method 4: Select Cells With Formula in Excel That Have a Specific Background color
In the previous method I have explained how to select cells that contain specific formula and font. Now, in a similar way I will show how to select cells in Excel that have a specific formula and a specific background color. Let’s see how.
Step 1: Open an Excel sheet that has some colored cells with formulas in it. Invoke the Find tool using the keyboard shortcut Ctrl+F.
Step 2: After expanding the interface of the Find tool, configure it to search text in formulas as I have explained in above methods.
Step 3: Click on Format button and a window will pop up. Navigate to the Fill tab and specify the background color there. You can also directly specify the fill color from an existing cell of your sheet. To do this use the Choose Format From Cell button and specify the reference cell by selecting it from your Excel sheet.
Step 4: Type the formula text in the Find What text box, and hit Find All button. You will see that it will list all the cells that contain that formula text and background color.
If you want to select all the cells, then click on any empty space in the result window and press the Ctrl+A keyboard shortcut. After that, all the cells will get selected and you can now close the Find tool.
So, in this way, you can select cells with formula in Excel and with the specified background color.
Bonus: Select Cells with Formula in Excel via Plugin
In the above mentioned methods I have explained how to select cells with formula in Excel and with the various formatting options. And for them I used built in functions of Excel. Now, in this section I will show you how you can do the same using an Excel plugin.
Asap Utilities package for Excel comes with tons of features to manipulate excel data that is difficult to do using the built in functions of Excel. However, it is only free for educational use.
One of the modules in the Asap Utilities package is Selecting cells based on content, formatting and more. I will use this tool of Asap Utilities to do the same. To see how to do it, simply follow these steps.
Step 1: Download and install Asap Utilities using this link. After installing it, you will see that it adds an additional tab in the Excel window
.Step 2: Open up any Excel sheet and navigate to the Asap Utilities tab and click on the Select drop down.
Step 3: Choose Selecting cells based on content, formatting and more option from the list and a window will pop up. And, you will have to configure various options there.
Step 4: Go to the first tab “Based on Content”. In this, choose “contains” from the first drop-down. And in the “Look in” drop down, choose “Formulas”.
Step 5: Type the name of the formula to be searched and hit the OK button.
At the end of the step 5, you will see that all the cells will get selected that meet with the specified condition.
So, it was the other nice way to select cells in Excel. If you are a plugin enthusiast, then you may give this method a try.
Also, if you want to select all cells that have a formula and have a specific formatting, then leave the drop-down next to “contains” blank. Then head over to “Based on formatting and more” and specify the formatting that you wish to select. In this way you will be able to select cells with formula and specific formatting.
Closing Thoughts
In the tutorial above, I have demonstrated various methods to select cells with formula in Excel. I have also explained to achieve the same with various criteria. So, if you are looking for the ways to do the same, then this tutorial should help you. Depending on what suits you need, you can give a try to any of the mentioned methods.
