How can I quickly determine if an Excel spreadsheet contains any formulas or is just "static" text?

Question

I've inherited an Excel spreadsheet with many sheets, all full of text and numbers. As far as I can tell, this is all hardcoded, static text. After a quick look, I can't see any formulas or anything which is updated dynamically based on anything in any cell anywhere. Certainly there are no instructions in the spreadsheet that would guide me to finding them.

But is there an automatic, quick way that I can tell for sure? Or do I have to check the definition of every cell on every sheet by hand?

Update: To clarify, my spreadsheet has ten sheets, each one with thousands of rows and dozens of columns. So I need a solution that will just tell me whether or not a formula exists - anything that requires me to scan hundreds of thousands of cells with my eyes checking for something probably isn't going to help me too much.

I only have Microsoft Excel for Mac 2011 available, if that makes any difference.

Solution 1

A quick VBA check.
It displays a Message Box every time it finds a formula on any sheet and colors the cell yellow.

Sub ColorAllFormulas()
    Application.ScreenUpdating = False
    For Each Sheet In ActiveWorkbook.Sheets
        For Each cell In Sheet.UsedRange
            If cell.HasFormula Then 
              cell.Interior.Color = 65535
              Msgbox "WHOA, I FOUND SOMETHING AT " & cell.address
            Endif
        Next
    Next
    Application.ScreenUpdating = True
End Sub

To run this, choose Tools -> Macro -> Visual Basic Editor from the menu bar. Then in the Project view, right click on the top level "VBAProject (test.xlsx)" -> Insert -> Module. Paste the above code into the "test.xlsx - Module1 (Code)" window and click the Run button.

Solution 2

You can use the Find and Replace command on the ribbon and select Formulas. All formulas on the worksheet you are in will be highlighted. (Alternatively, you can select Constants to find text and hard-coded values.)

Taking @David Yaw's answer in a slightly different direction, you should be able to set a conditional format on the entire data range, with the condition set by a formula like =NOT(ISERROR(FIND("=",A1). This will highlight all cells with an "=" sign.

Solution 3

All formulas start with an equals sign. (e.g., =SUM(A1:A10).) Use the Find/Replace dialog to search for an equals sign. Yes, you will get false-positives on any static text that happens to include an equals sign, but searching in this way will jump to the next cell with an equals sign, and it will be quick to look at the formula bar to determine whether the cell's contents are static or a formula.

I don't have access to a Mac to verify which options on the Find/Replace dialog you want to select, but you can easily check that it's doing what you want by entering =1+1 in a cell, and verifying that searching for = finds that cell.

Comments

[nixda]

Have you checked your sheets with the activated option Show formulas in cells instead of their calculated results ?

[Day]

@nixda Just tried that, and it resizes all of the columns in the sheet so that everything jumps around and then it's very hard to see what's different. But I guess I can now scan the sheet with my eyes instead of having to click on each cell at a time, so that helps a little. Thanks

[chuff]

I believe the show formulas option can be turned on and off with Control-` (backtick).

[nixda]

@chuff Yeah, in office 2003 this was working for me too. But not in 2013? O.o

[chuff]

@nixda Don't have access to 2013. I believe Mac 2011 is supposed be of same "generation" as 2010 (which I have for Windows machine).

[RLH]

Please see my answer. This feature is integrated into Excel. All you have to do is either show or hide your formulas, rather than display their evaluated values.

[RLH]

Oh, I just noticed chuff's original comment. Control + ` (Backtick) is executing the menu command I'm referencing in my answer.

[Day]

I can't see such a thing in my "ribbon". Is that the toolbar thing at the top, with tabs for Home, Layout, Tables etc? Sorry, it's been about 10 years since I last used Excel. Now I'm lost. I have Replace available from the Edit menu but that doesn't have a Formulas option and doesn't seem to highlight anything. Maybe it's a mac thing? And yes, I'm afraid I don't use Mac's either. Only reason I'm using one now is because it's the only machine with Excel installed...

[Day]

My "Find and Replace" dialog looks like this

[Day]

That would be easy if I had a small spreadsheet like yours but imagine doing this for 1000s of rows with dozens of columns on 10 worksheets. It would still take ages for me to check and it's too easy for me to miss one formula that might be hidden half way down sheet 7.

[Day]

That appeals to my programmer brain and looks like I can adapt it to just give a yes/no answer instead of me having to look for a yellow cell hidden somewhere. I'll give it a go and report back thanks.

[RLH]

Day: No, when you go to show the formulas, it should shows all of the formulas for the entire worksheet, not just one cell. Is this not sufficient?

[Day]

RLH: I've updated the question to clarify. My spreadsheet has ten sheets, each one with thousands of rows and dozens of columns. So I need a solution that will just tell me whether or not a formula exists - anything that requires me to scan hundreds of thousands of cells with my eyes checking for something probably isn't going to help me too much. To put it another way, I'd probably miss the = if it's in the 327,591st cell that I look at...

[RLH]

I understand now. I see that you have a solution. Let me see if there is a way that I can craft a conditional format that can highlight cells that have formulas in them.

[RLH]

Actually, chuff's comment to David Yaw's post is what I am looking for. That will work too.

[Day]

Accepted. Have finally got time to test this and it works great thank you. Also edited to add instructions on how to run VBA, as it wasn't obvious (to me anyway).

[JVC]

This is an old question but I'm trying to do exactly this now in Mac Excel 2011 and it just won't work. The not(iserror(find)) suggestion by chuff just returns FALSE for me in all cases, unless the cel in question contains ONLY an equals sign. What am I missing here?

[fixer1234]

Please reread the question. It's about identifying cells with formulas, not differentiating text from numbers.