4

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.

7
  • 1
    Have you checked your sheets with the activated option Show formulas in cells instead of their calculated results ?
    – nixda
    Jun 27, 2013 at 17:34
  • @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
    – Day
    Jun 27, 2013 at 17:42
  • 1
    I believe the show formulas option can be turned on and off with Control-` (backtick).
    – chuff
    Jun 27, 2013 at 18:04
  • @chuff Yeah, in office 2003 this was working for me too. But not in 2013? O.o
    – nixda
    Jun 27, 2013 at 18:36
  • @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).
    – chuff
    Jun 27, 2013 at 18:40

6 Answers 6

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.

3
  • 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.
    – Day
    Jun 27, 2013 at 19:37
  • 2
    Change cell.Interior.Color = 65535 to Msgbox "WHOA, I FOUND SOMETHING AT " & cell.address
    – nixda
    Jun 27, 2013 at 19:59
  • 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).
    – Day
    Jul 1, 2013 at 13:37
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.

2
  • 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
    Jun 27, 2013 at 17:48
  • My "Find and Replace" dialog looks like this
    – Day
    Jun 27, 2013 at 17:54
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.

2
  • +1 Good thinking. It should also be possible to use conditional formatting with something like =NOT(ISERROR(FIND("=",A1))) set for each sheet's data range.
    – chuff
    Jun 27, 2013 at 18:46
  • 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?
    – JVC
    Oct 7, 2014 at 18:32
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.

5
  • 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
    Jun 27, 2013 at 19:34
  • 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?
    – RLH
    Jun 27, 2013 at 19:37
  • 2
    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...
    – Day
    Jun 27, 2013 at 19:51
  • 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
    Jun 27, 2013 at 19:57
  • Actually, chuff's comment to David Yaw's post is what I am looking for. That will work too.
    – RLH
    Jun 27, 2013 at 20:02
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.

-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.

1
  • Please reread the question. It's about identifying cells with formulas, not differentiating text from numbers.
    – fixer1234
    Nov 20, 2015 at 5:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .