Excel - Conditional Format All Formula Cells Episode 2066

Microsoft Excel Tutorial: Highlight All Formula Cells in Excel

Welcome to another episode of the MrExcel Podcast, where we dive into all things Excel. In today's episode, we will be discussing a great question from one of our listeners, Michael, who attended our seminar in Knoxville, Tennessee. Michael asked about a way to use Conditional Formatting to mark all formula cells, and I'm excited to share this solution with you.

In the past, we could use the Home tab, Find & Select, and select Formula cells to apply formatting. However, this was a one-time thing and would not update if any changes were made to the data. But thanks to a new function in Excel 2013, we can now easily identify and highlight all formula cells using Conditional Formatting.

The function we will be using is =ISFORMULA, and it works by checking if a cell contains a formula. To set this up, we first need to select the range of cells we want to apply this formatting to. Then, we create a New Rule and use a formula to determine which cells to format. Remember to use the top-left cell of your selection in the formula, in this case, =ISFORMULA(B2). And just like that, all formula cells will be highlighted with the formatting of your choice.

This new function is a game-changer for those of us who work with formulas regularly. However, it is only available in Excel 2013 or newer versions. If you are still using Excel 2010 or 2007, there is a workaround using a custom user-defined function, but I highly recommend upgrading to take advantage of this feature. And for more tips and tricks like this, be sure to check out my book, Power Excel with MrExcel.

To recap, we started by using the Go To Special feature to highlight formula cells, but this was not a live update. Then, we discovered the new function =ISFORMULA, which can be used in Conditional Formatting to highlight all formula cells in a range. This function is only available in Excel 2013 or newer versions, so make sure to upgrade for the best Excel experience.

Thank you for tuning in to this episode of the MrExcel Podcast. I hope you found this tip helpful and will join us for more Excel insights in the future. Don't forget to click the link in the top-right corner to check out my book, Power Excel with MrExcel. And as always, happy Excel-ing!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-...

#excel
#microsoft
#microsoftexcel
#exceltutorial
#exceltricks
#excelformula
#evergreen
#conditionalformatting

Table of Contents:
(00:00) Highlight All cells with formulas
(00:22) Using Conditional Formatting to mark formulas
(01:01) Discovering the ISFORMULA function
(01:34) Setting up Conditional Formatting using ISFORMULA
(02:17) Recap of the episode
(03:04) Clicking Like really helps the algorithm


This video answers these common search terms:
how to highlight cells that have a formula in excel
how to apply conditional formatting excel formula
how to highlight formula show in excel
how to highlight formulas in excel
how to highlight all formula cells in excel
how to find and highlight formulas in excel
how to highlight cells with a formula in excel

Go To Special, Formulas - but it is not "live"
There is a new function… HASFORMULA? No!
Great new feature in Office 365 to find a function by typing part of the name
The function is ISFORMULA
Select the range
Home, Conditional Formatting, New Rule, Use a Formula
The formula should point to the top left corner cell of your selection
=ISFORMULA(B2)

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads...