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.

Basically, checking is very very important to my line of work. Technically, every cell of every spreadsheet should be checked, but this is not feasible. If I am checking something, I can often tell when the user has build a nice formula and copied to the other cells in a contiguous block. But I can never be 100% sure, and if I find one anomaly, I lose trust and end up checking every cell just to be sure.

I have come up with ways of doing this check quickly. Basically, I copy the value of the original cells somewhere else and then pick one of the cells and copy its formula over all the others and then taking the difference of the new group and the values of the old group. If it is zero, I'm good to go. I'm pretty fast at this, but it is pretty tedious. Are there any add-ins or other methods that would help me do this faster. Like, if I highlighted a group of cells, I could just see they were equivalent down by the status bar next to where it shows sum, average, etc.

share|improve this question

2 Answers 2

If you want to check that all the formulas are equivalent without clicking into each cell, you can press Ctrl+` (on most keyboards it's the little character to the left of the 1/! key).

enter image description here

This will alternate between showing the values and showing the formulas:

Ctrl+`

share|improve this answer
    
This is also a great thing I didn't know. I will definitely use this. Most of the time the block is really large and sometimes the formula is complex, so this still won't tell me if the whole block is or isn't equivalent with 100% certainty, but it will certainly make it easier for me to figure it out now! Thanks. –  oob May 11 '12 at 16:43
1  
<kbd>Ctrl</kbd>+<kbd>`</kbd> is the shortcut for "Show Formulas." In Excel 2010, "Show Formulas" is on the "Formulas" ribbon in the "Formula Auditing" section. –  Dane Jun 13 at 13:07

If you know the relative formula that should applied to a range, you can set up conditional formatting to highlight any cells in the range that deviate (by value) from what is expected. For instance, say you have the following sheet with x values in A2:A12 and sin x values in B2:B12 which should be calculated with the filled down formula:

=SIN(DEGREES(A2))

You can set up a conditional formatting rule for B2:B12 to highlight unexpected values. Select B2:B12 and (in Excel 2007) go to Conditional Formatting >> New Rule >> 'Use a formula to determine which cells to format'. Enter the following formula as the rule here:

=B2<>SIN(DEGREES(A2))

Set the format to something visually striking, like yellow fill. This will highlight any divergent values. Note that this only checks values, not the actual formulas. There is still the possibility that an incorrect formula could return the correct value and not get highlighted.

Example where highlighted cells have formula =SIN(A7) instead of =SIN(DEGREES(A7)):

enter image description here

share|improve this answer
    
This is neat. Although I cannot expect that everybody who gives me a spreadsheet to check will do this, and then our course, I would need to check that they did it correctly...... Still though, I did not know about this and I will probably make use of it in some fashion. –  oob May 9 '12 at 23:52
    
I just realized how silly the formulas in the example are! Sorry to anyone who is offended by nonsense! –  Excellll May 10 '12 at 13:25

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.