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.

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.

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

4 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
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 '13 at 19:37
2  
Change cell.Interior.Color = 65535 to Msgbox "WHOA, I FOUND SOMETHING AT " & cell.address –  nixda Jun 27 '13 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 '13 at 13:37
add comment

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.

share|improve this answer
    
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 '13 at 17:48
    
My "Find and Replace" dialog looks like this –  Day Jun 27 '13 at 17:54
add comment

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.

share|improve this answer
    
+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 '13 at 18:46
    
Thanks I'll give that a go too. –  Day Jun 27 '13 at 19:38
add comment

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.

share|improve this answer
    
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 '13 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 '13 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 '13 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 '13 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 '13 at 20:02
add comment

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.