Excel - Highlight all Formula Cells in Excel Using Conditional Formatting - Episode 1351
1,528 views · Published 1 March 2011 · 3:06 · Indexed 16 November 2026
Channel: MrExcel.com · 2011 · Education
Microsoft Excel Tutorial - Mark All Formula Cells in Excel Welcome to another episode of the MrExcel podcast. In today's episode, we will be discussing a question sent in by Ricardo, who suggests that this would be a great topic for a dueling podcast. However, there is only one way to solve this problem, so it won't be much of a duel. Ricardo's question is about marking formulas in a series of numbers and changing the color of the cell once it is marked as complete. While there are a few ways to approach this, we will be using conditional formatting to solve this issue. The problem with using conditional formatting is that it cannot detect if a cell contains a formula or not. We could use the "Go to Special" function to select all the formulas and then color them, but this is a static process and won't update if the formula is changed to a value. So, we need to find a way to identify which cells contain formulas and which ones have been converted to values. This is where a small amount of VBA code comes in handy. In order to use VBA, we will insert a module and create a custom function called "HasFormula". This function will check if a cell contains a formula and return TRUE or FALSE. We can then use this function in our conditional formatting rule to color the cells accordingly. This method was first mentioned in one of my very first books, "MrExcel on Excel", and it still proves to be a useful solution to this day. To use this method, we will select the cells we want to format, go to "Conditional Formatting", and create a new rule using the formula "=HasFormula(A1)". This will check the top left cell (A1) and apply the formatting if it contains a formula. We can then choose the desired formatting, in this case, the color red. Now, all the cells with formulas will be highlighted in red, and if we change any of the formulas to values, the formatting will update accordingly. Unfortunately, this method does require a small amount of VBA, which means we will have to save the file as a .xlsm or .xls (for older versions of Excel) in order for the macro to run. But, it is a simple and effective solution to Ricardo's problem. I want to thank Ricardo for sending in this question and for tuning in to the MrExcel podcast. Don't forget to subscribe and hit the notification bell to stay updated on our latest episodes. See you next time! 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-on-youtube/ Table of Contents (00:00) Mark or Highlight formula cells in Excel (00:50) To use Conditionl Formatting, you need some VBA (01:01) Go To Special can select all formula cells but it is static (01:26) Open VBA and create a HasFormula function (02:01) Set up a new rule in conditional formatting (02:19) All formula cells are red (02:38) Clicking Like really helps the algorithm This video answers these common search terms: how to highlight all formula cells in excel how to highlight formulas in excel how to highlight excel cells with formula how to highlight cells that have a formula in excel how to select highlight cells that are formulas in excel how to find and highlight formulas in excel how to get excel to highlight cells with formula Ricardo has a Spreadsheet where active projects include live Formulas. Once a project is complete, he converts the Formulas to Values. He wants a way to mark all of the Cells which are still live Formulas by changing the color of the Cell's Data. Today, in Episode #1351, Bill shows us how to use a UDF [User Defined Function] to accomplish the task - using one (1) line of VBA [Visual Basic for Applications] to keep the process dynamic and reflect future changes in the Data Set. For all of your Microsoft Excel needs visit MrExcel.com -- Your One Stop for Excel Tips and Solutions. Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153031/
More from this channel
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920