6 Ways to Highlight Cells With Formulas in Microsoft Excel

Are you tired of scrolling through hundreds of rows and columns, trying to identify cells with formulas in your Excel spreadsheets?

No worries! This guide will unveil various ways to highlight cells containing formulas in an Excel worksheet.

Excel is the best available data analysis and visualization tool for every business dealing with numbers and figures. Even, you could find Excel as the go-to app to store household shopping lists, grocery lists, or inventories.

To do all sorts of data analysis and trend generation you use many formulas in Excel. When the Excel worksheet becomes large and scrolling through the entire sheet becomes impossible, it’s a hectic job to audit the worksheet and resolve calculation errors.

In those situations, you can try any of the below-mentioned methods to quickly highlight all the formula cells so you can concentrate on functions that derive the insights.

Reasons to Locate Cells With Formulas in Excel

Here are the reasons to highlight cells with formulas in Excel:

1. Highlighting cells with formulas allows you to quickly identify and validate the consistency, accuracy, validity, and integrity of your data.
2. If there are any faults in the Excel spreadsheet due to incorrect formula usage, you can quickly locate that by using highlight formula cells in Excel skill.
3. If you’re presenting an Excel report to the audience, you may want to highlight the cells containing formulas. It helps the audience understand how did you arrive at certain results in your report.
4. When working with large datasets, highlighting cells with formulas streamlines your workflow by minimizing manual effort. You can simply focus on areas where formulas were used and complete your review quickly.
5. If you’re an Excel instructor, you should highlight the cells containing custom formulas so that your students can catch up with the training.

Now is the time to explore various proven and easy ways to highlight formula cells in Excel in the below sections.

Highlight Formulas with a Keyboard Shortcut

By default, Excel would only show the result of a formula inside the cell where you create the formula. However, Excel also gives you a powerful tool to visualize the exact construction of all the formulas inside their respective cells within a worksheet. This tool is known as Show Formulas and is available inside the Formula Auditing block in the Formulas tab.

However, you don’t need to navigate your way to the above destination to find all the formulas in an Excel worksheet. Instead, you can simply hit the following keys altogether to visualize all formulas.

Ctrl + ` (accent) (On a Windows PC)

^ + ` (accent) (On a Mac)

Pressing all the above keys at once either on a PC or Mac will format the worksheet in a way so you can see all the formulas in cells. Now, you can manually highlight these cells from Home > Font > Fill Color.

If you wish to go back to the default view, simply hit the hotkeys again. The cell highlighting that you did previously won’t go away. So, you can easily notice the formula cells in the default view as well.

Highlight Formulas with the Go To Function

In the above method, Excel doesn’t highlight the formula cells automatically. You need to do that manually once you see which cells contain formulas. If you want an automatic way to find and highlight formula cells, then you can use the Excel Editing tool Go To. Here’s how to use this tool:

1. Access the Excel worksheet where you need to highlight all the cells with custom formulas.
2. Click the Find & Select button inside the Editing block in the Home tab of the Excel ribbon menu.
3. Choose the Go To Special option on the context menu that opens.

1. You should now see the Go To Special dialog box in front of you.
2. There, click the radio button for the Formulas option and hit OK.

1. Excel will promptly highlight all the cells containing formulas in your worksheet.

This method is good for a quick glance at the Excel worksheet for the positions of the formulas. The highlighting is volatile. As soon as you select any cell on the worksheet, Excel un-highlights the formula cells.

Highlight Formulas with the Find and Replace Tool

Another intuitive way to highlight formula cells without using any formula or codes is the Find and Replace tool. Here’s how you can use it:

1. Open your Excel worksheet and hit the Ctrl + F keys altogether to launch the Find and Replace tool.
2. In the Find what field, type in the equals sign (=).
3. Now, click the Options>> button to expand the customizations you can do in Find and Replace.
4. Click the drop-down list for the Look in field and choose Formulas.
5. Hit the Find All button on the Find and Replace dialog box to list all cells containing the equals sign and formulas.

1. Select one entry on the list and hit Ctrl + A keys to select all the entries.

1. This will also create a grey-colored highlighting on the actual cells in your worksheet.

1. Go to Home >Font >Fill Color > and choose any color to highlight these cells.
2. Now, you can close the Find and Replace dialog box but the highlighted cells will still stand out in your spreadsheet.

Highlight Formulas with Conditional Formatting

All the methods explained so far offer static highlighting of formula cells. How about creating highlights that’ll stay forever and will change dynamically when you add or remove new formula cells?

Yes, you can easily get that by using Conditional Formatting in Excel. Find below the easy steps to implement this dynamic highlighting of all formula cells in your worksheet:

1. Open your Excel spreadsheet.
2. Select the range of cells where you want to highlight the formulas.
3. Navigate to the Home tab in the ribbon menu of Excel.
4. Click on the Conditional Formatting button.
5. Select New Rule from the drop-down menu.

1. Click the “Use a formula to determine which cells to format” option under the Select a Rule Type section.
2. Enter the following formula inside the Format Values… field:

=ISFORMULA(A1)

1. You must replace the cell reference A1 with another cell reference according to your worksheet. You must refer to the top-left cell of the cell range in which you want to find formula cells.
2. Click on the Format button to create a custom formatting for the formula cells.

1. Go to the Fill tab in the Format Cells dialog box.
2. Choose a color or pattern for highlighting the cells with formulas.
3. Hit the OK button to get out of the Format Cells dialog box.

1. Back in the New Formatting Rule dialog box, click OK to apply the formatting rule.

The ISFORMULA function is available since Excel 2013 edition. So, the method won’t work on earlier Excel editions than Excel 2013.

The latest Excel web app has the ISFORMULA function so you can use Conditional Formatting to highlight formula cells on Excel for the Web as well.

Highlight Formulas with VBA Scripts

If highlighting formula cells in Excel is part of automation in your Excel worksheet involving VBA scripts, then this section is particularly for Excel masters like you. Below, I’m explaining how to use two VBA scripts to highlight cells containing formulas in a selected range and the entire worksheet:

1. Bring up the VBA Editor on your Excel worksheet by pressing Alt + F11 keys together.
2. Inside the VBA Editor, click the Insert menu and choose Module.
3. On the new Module, copy and paste the following VBA code:

Sub HighlightFormulaCells() Dim r As Range With ActiveSheet.UsedRange .Interior.ColorIndex = xlNone For Each r In .Cells If r.HasFormula Then r.Interior.ColorIndex = 6 'yellow Next End With End Sub

1. Click the Save button.
2. Excel will show a warning to save the file as a macro-enabled file. Click Yes to allow.
3. Close out of the code editor.

1. Now, press Alt + F8 to call the Macro dialog box.
2. There, select the HighlightFormulaCells macro and hit the Run button.
3. Excel will instantly highlight all the cells that contain a formula in the active worksheet.

If you wish the VBA program to highlight formula cells in a given cell range and not in the whole worksheet, then use the following VBA code:

Sub Highlight_Formulas_Range() Dim Rng As Range For Each Rng In Range("A1:G15") 'Range to highlight cells If Rng.HasFormula Then Rng.Interior.ColorIndex = 3 'red Else Rng.Interior.ColorIndex = 0 'blank End If Next Rng End Sub

In the above code, modify the value in the Range("A1:G15") code element according to your own worksheet data. For example, you could make it Range("B1:H15") to pinpoint formulas in the said cell range.

These are irreversible changes. You won’t be able to go back to the previous formatting. So, create a copy of the existing worksheets before executing these VBA scripts.

Highlight Formulas with Office Scripts

If you need to automate the Conditional Formula-based process to highlight formula cells, you can use the following script in Excel Automate (Office Scripts). The script is valid for both the Excel 365 desktop app and Excel on the Web app.

1. Go to the worksheet where you need to highlight cells with formulas.
2. Click the Automate tab on the Excel ribbon and choose New Script.
3. Inside the Code Editor on the right-side panel, copy and paste the following script:

function main(workbook: ExcelScript.Workbook) < let conditionalFormatting: ExcelScript.ConditionalFormat; let selectedSheet = workbook.getActiveWorksheet(); // Create custom from range A1:D6 on selectedSheet conditionalFormatting = selectedSheet.getRange("A1:D6").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getRule().setFormula("=ISFORMULA(A1)"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#fce4d6"); conditionalFormatting.setStopIfTrue(false); conditionalFormatting.setPriority(0); >

1. In the above code, change the cell range A1:D6 according to the data on your worksheet. Essentially, this should be the whole worksheet.
2. Click on Save script.
3. Now, hit Run to highlight all cells containing custom formulas.

Conclusions

Now you know all the popular methods to highlight cells with formulas in Excel. If you’re just starting your journey as an Excel user and learning new Excel skills, start with the methods like Go To function, the Show Formulas keyboard shortcut, and the Find & Replace tool method.

If you’ve been using Excel for a while, use the Conditional Formatting-based method to highlight cells depending on a formula. Finally, if you’re an ace Excel user and looking for advanced automation in Excel, check out the VBA scripts and automate the whole process of highlighting formula cells in Excel.

About the Author

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

👉 Find out more about our Advanced Formulas course!

Top Posts

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel