Excel is a great tool, and also can yield wrong results. For best outcomes, it’s optimal to incorporate error detection mechanisms in most/all spreadsheets.
These can range from very simple steps to complex ones. One of the simplest steps is to keep an eye out for those green triangles in the upper left corner of cells. When you hover over one of those, there is a pull-down menu to the left of the cell with an exclamation point within a yellow ‘hazard!’ diamond shape. Clicking on that cell gives a range of information and options – what the error is, a way to fix it, and additional information and options.
Also you can go to the ‘Formulas’ tab, and click on ‘Error Checking’ (with the same icon) in the ‘Formula Auditing’ functional group, and it will check the whole spreadsheet for errors. In that ‘Error Checking’ box, in the bottom left corner is a button ‘Options’. Click that, and you have lots of settings to customize the functionality to your use. One of the main options is whether to have background checking enabled (recommended). You can also get to that by typing ‘Error’ in the Features Question box at the far right of the top menu bar (with the light bulb icon).
Another do-it-yourself method of error checking that I try and incorporate often/always is simply to have each main total be validated by a second formula that should yield the same result. If the total is all the values in the column above it, I add a second formula in the next cell that sums all the rows included in that totalling column. Usually that second formula is in smaller font, bold. And then as the spreadsheet continues to be used, the double-check is easy to verify.
Another feature in Excel that may seem intimidating but is actually very usable (and fun) is the conditional formatting feature. With that you can (for a group of cells) define a condition (a value that the cells may contain) that triggers a specific format.
You can find it in the ‘Home’ menu, in the ‘Styles’ box towards the center. ‘Conditional formatting’ is the left-most icon. You can start with a single cell or group of cells. Click on ‘Conditional Formatting’ and then ‘New Rule’. Choose ‘Format only cells that contain’ and then below that, in the ‘Edit the Rule Description’ field, leave the left-most box as ‘Cell Value’, change the ‘between’ to ‘greater than’ and type in a value that is higher than any possible valid value in this set of cells. Click on the ‘format’ button below. Make a simple format change, such as changing the font color to red. Then click ok. Change the value of one of those cells to a number above that criteria number, and you’ll see the font change to red!
Conditional Formatting is a great tool that I’ll write more about in the future, but that is how it can be used for error checking in particular.
Clarity Solutions 