Identify formula cells

Thu, 09/24/2009 - 14:18 — jburtoncpa

I am the Director of Accounting for a company in Little Rock, Arkansas and need your help.

I utilize Microsoft Excel spreadsheets to complete a wide range of budgeting and forecasting functions. Each new budget cycle I rebuild my Excel files for the new year. I do this because I use hundreds of calculations and link data to many different worksheets and the opportunity for error is high.

Is there an easy way to identify cells containing formulas or links so the files do not have to be rebuilt each year. Some type of automatic highlight or identifier such as when you insert notes.

Your feed back would be very helpful.

Thank you,
Jeff B, CPA
Director of Accounting

‹ Automating Shortcuts Excel Tool Bars ›
Thu, 09/24/2009 - 14:39 — jlenning

J Burton, No problem.  There

J Burton,

No problem.  There are basically two ways to identify "formula" cells.  The first one is so easy, the second way is more tricky.

Way1: Use F5 (goto) and then click "special" and then "formulas"...all cells with formulas will be highlighted.  (Told you that was easy)

Way2: Create a user defined function (UDF) that will return "true" if the cell is a formula and "false" if the cell is not a formula.  Maybe call the UDF "IsCellFormula".  (A sample UDF is below).  Once you have the UDF set up, then you create a Conditional Format, and set the formula as =IsCellFormula(A1).  Then, if the cell is a formula, your conditional format will be applied.

Sample UDF:

function IsCellFormula(c)
  IsCellFormula = c.HasFormula
end function

Remember, if you want your UDF to run, you need to change your Excel security settings to allow macros to run.

Please let me know if you need any additional info, and good luck!!

Thanks,
Jeff

—

Mon, 09/28/2009 - 17:05 — el_guapo

Another shortcut I use is to

Another shortcut I use is to hit Ctrl ` (the control key along with the key directly under Esc on my keyboard). That changes the view on your screen to show all formulas rather than the results of the formula. It's a toggle, so you can hit Ctrl ` to change it back.

Mon, 09/28/2009 - 18:53 — jlenning

Dan -- nice pull!  That ctrl

Dan -- nice pull!  That ctrl ` (backtick) keyboard shortcut is a great one!  Thanks man!

—

Identify formula cells