5

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.

Day
  • 880
  • 2
  • 8
  • 19

6 Answers6

3

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.

chuff
  • 3,534
3

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.

Day
  • 880
  • 2
  • 8
  • 19
nixda
  • 27,634
2

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.

David Yaw
  • 807
  • 2
  • 9
  • 13
0

There is an easy way to test for equations - do a ctrl-g, select special and then pick formulas If you have one, it will go there. If not, then a no cell found message appears

This has to be done per sheet.

bvaughn
  • 751
0

There is a much easier way than using macros. Open your excel document. You should see a form similar to mine.

enter image description here

Click on the Formulas menu option (on the green bar.) Next, click the Show button (it's the fifth button from the left,) and select Show Formulas. This will change your spreadsheet to show the formulas, rather than the evaluated values. See my screenshot below.

enter image description here

In this case, column A contains static, numeric information. Column B contains simple formulas. FYI, this option has been around in Excel for as long as I've used it (going back to Excel 97.) Way's of showing/hiding the formulas has been different, but this feature has been around since probably the beginning of Excel.

RLH
  • 4,665
-1

I have found an innovative but very simple way, just try it. I had the same problem and tried all solutions on internet but did not help. Multiply any cell by 1 in some other convenient cell and drag result for all the cells. Cells with text will show result as #VALUE! and cells with numbers will show numbers.

Dr Hemant Junnarkar
  • 1