Excel Factor 8 Highlight Cells Containing Formulas

• Share on Facebook.
• Share on Twitter.
• Share on Google+
• Share on LinkedIn
• Pin It!

This Excel Factor tip was sent in by Shannon Hommel of the UK.

If you’re setting up a template or a workbook for other’s to use and you can’t password protect it (for whatever reason), then the next best thing is to highlight the cells containing formulas.

This is not only an Excel ‘Best Practice’, but it may also prevent reduce the likelihood of the user breaking them!

What’s even better is you can have Excel do this for you automatically.

There are a few ways you can achieve this, but one of the easiest is to use the GET.CELL function in a named formula.

Define Name Formula

1. Define a new Name. Formulas tab > Name Manager > New

 


2. Give your formula a name. I’ve called mine Format_Formulas. If you want to be able to use the formula in the whole workbook, choose ‘Workbook’ in the scope field.
3. In the 'Refers to' field enter this formula:  

=GET.CELL(48,INDIRECT("rc",FALSE))

Setup Conditional Formatting

Now you have your formula defined you can use it in a Conditional Format:

1. Select the cells you want the formatting applied to.

 
Word of warning; I don’t recommend you select the whole worksheet as this may grind your PC to a halt, instead select the cells you expect to use, and then add a few more for contingency.

2. On the Home tab > Conditional Formatting > New Rule

 


3. Select ‘Use a formula to determine which cells to format’ > enter the name of your defined formula from step 1. Mine is Format_Formulas.

 


4. Click the ‘Format’ button, set your formatting then click OK, and OK when you’re done.

 

Now when you enter formulas in any of the cells contained in the conditional format range they will be highlighted for you automatically.

Note: GET.CELL is actually an Excel 4 Macro Function, which means you need to save your file as a Macro Enabled Workbook with a file extension .xlsm

Want to know what else the GET.CELL function can do?

Check out this list of GET.CELL arguments.

For more tips on Excel Best Practices and formatting data models check out my Excel Dashboard course.

Thanks to Shannon for this cool tip.

Vote for Shannon

If you’d like to vote for Shannon's tip (in X-factor voting style) use the buttons below to Like this on Facebook, Tweet about it on Twitter, +1 it on Google, Share it on LinkedIn, or leave a comment….or all of the above 🙂