Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
Does the "Show Formulas" mode (Ctrl + `) help? –  sblair Nov 22 '11 at 18:56
    
It does, but I wouldn't consider it "systematic" - requires me to hand-blast away the subtotal lines. –  YGA Nov 22 '11 at 19:51

5 Answers 5

up vote 3 down vote accepted

Use Conditional formatting. Choose the "Use a formula to determine which cells to format" option and type " =NOT(Cellhasformula) ". This will format anything that is hardcoded.

share|improve this answer
2  
So this wasn't the full answer but it's actually quite elegant. The full answer is on: j-walk.com/ss/excel/usertips/tip045.htm The key is that you 1st need to create a name (via Formulas -> Name Manager) called "Cellhasformula" that "refers to" the mystical formula =GET.CELL(48,INDIRECT("rc",FALSE)). As the link says, the formula uses GET.CELL, which is in the XLM macro language (VBA's predecessor) and can't be used directly in a worksheet. The "48" arg makes GET.CELL return 'True' if the cell has a formula. The INDIRECT basically creates a ref to each cell in the selected range. –  YGA Oct 10 '13 at 22:07

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

Go To Special dialog box

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

share|improve this answer

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
share|improve this answer

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

share|improve this answer

If you want to store the Excel file in a macro-free workbook format, you should avoid both VBA and macros (i.e. the XL4/XLM approaches suggested in other answers). In this answer, I made the assumption of a macro-free Excel file.

If you use MS-Excel 2013, you can use:

=ISFORMULA(reference)

If you use older MS-Excel versions (e.g. 2010, 2007), there is no true function to determine if a cell contains a formula. However, you can approximate it using:

=IF(OR(ISBLANK(reference);CELL("prefix";reference)<>"");FALSE;IF(CELL("type";reference)="l";TRUE;"MAYBE"))

The function above returns:

  • TRUE for cells containing a formula wich results in a String data.
  • FALSE for cells containing a String literal or is blank.
  • "MAYBE" for cells containing a boolean, a number or a date, no matter if this value is literal or the result of a formula.
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.