How to tell if an Excel cell has a formula or is hardcoded?

Question

Does anyone have a special tip (non-VBA strongly preferred) to tell systematically if a cell has a hardcoded value or is a derived formula?

Am dealing with some data that has messy subtotals embedded in it and am trying to see if I can separate out the raw lines.

Answer 1

Select the cells in question, press Ctrl+G and then select Special to get the following dialog box:

Then specify the type of cells you want and click OK and only those types of cells will remain selected.

Answer 2

Two other methods are to

1. Use a mapping tool such as my Mappit! addin (which can be run as a trusted addin)
2. You can use the very useful XLM / Range Names workaround which offers an real time colouring of hardcoded cells, formulae, linked cells etc. This doesn't require any programming knowledge to deploy unlike VBA

Answer 3

I know you said non-VBA preferred, but if you end up with no other solutions, the Range object has a HasFormula property.

Returns: True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise.

Source: http://msdn.microsoft.com/en-us/library/bb208626%28v=office.12%29.aspx