Excel – How to quickly determine if an Excel spreadsheet contains any formulas or is just “static” text

microsoft excelmicrosoft-excel-2011

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.

Best Answer

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.

Related Question