Find Cells containing Formulas in Excel

Method 1: Using ‘Go To Special’ Option:

1. With your excel sheet opened navigate to ‘Home’ tab > ‘Find & Select’ > ‘Go To Special’. Alternatively, you can also press ‘F5’ and then ‘Alt + S’ to open the ‘Go to Special’ dialog.

2. Next, in the ‘Go to Special’ window select the ‘Formulas’ radio button. After checking this radio button you will notice that few checkboxes (like: Numbers, Errors, Logicals and Text) are enabled, these checkboxes signify the return type of the formulas.

So, if you select ‘Formulas’ radio button and only check the ‘Numbers’ checkbox then it will just search the Formulas whose return type is a number. Here in our example we will keep all of these return types checked.

3. After this click the ‘Ok’ button and all the cells that contain formulas get selected.

4. Next, without clicking anywhere on your spreadsheet change the background colour of all the selected cells.
5. Now your formula cells can be easily identified.

Method 2: Using a built-in Excel formula:

Excel has a formula that can find whether a cell contains a formula or not.

=ISFORMULA(reference)

Here ‘reference’ signifies the cell position which you wish to check for the presence of a formula.

For example: If you wish to check the cell ‘A2’ for the existence of a formula then you can use this function as =ISFORMULA(A2)

This function results into a Boolean output i.e. True or False. True signifies that the cell contains a formulas while False tells that cell doesn’t contain any formulas.

Method 3: Using a Macro for identifying the cells that contain formulas:

I have created a VBA Macro that can find and colour any cells that contain formula in the total used range of the Active sheet. To use this macro simply follow the below procedure:

1. Open your spreadsheet and hit the ‘Alt + F11’ keys to open the VBA editor.

2. Next, navigate to ‘Insert‘ > ‘Module‘ and then paste the below macro in the editor.

view plaincopy to clipboardprint?

1. Sub FindFormulaCells()  
2. For Each cl In ActiveSheet.UsedRange  
3. If cl.HasFormula() = True Then  
4. cl.Interior.ColorIndex = 24  
5. End If  
6. Next cl  
7. End Sub  

3. For running this formula press “F5” key.

4. This Macro will change the background colour of all the formula containing cells and thus makes it easier to identify them easily.

What is syspolicy_purge_history

This job is created by default when an instance of SQL Server 2008 is installed or upgraded to SQL Server 2008.

SQL Server 2008 introduced a new feature called Policy Based Management. A Policy can be something like xp_cmdshell should not be enabled on an instance. When a policy runs it stores the results in MSDB. Over a period of time, this may will cause unnecessary data getting piled up in MSDB. The job syspolicy_purge_history prunes the data older than the the days defined in HistoryRetentionInDays property of Policy Management.

In the above screenshot it is set to 0 which means that the Policy Evaluation History will never be deleted. So on this instance the job is running just like that 

If this job is missing for some reason, it can always be recreated using the below query

EXEC msdb.dbo.sp_syspolicy_create_purge_job