5

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

2 Answers 2

1

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

2
  • 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, 2012 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, 2012 at 13:25
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+`

2
  • 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, 2012 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, 2014 at 13:07

You must log in to answer this question.

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