How To Learn From Your Excel Mistakes

Wed 24th August 2011

It's official - we definitely do not learn from our mistakes. However, we do learn from getting things right. We take a look at how the Excel 2010 Error Checker is here to help you get all your formatting right - again and again.

The old adage that we "learn more from our mistakes" seems to have been proven wrong. New research now shows that our brain only learns from experience when we do something right. Studying monkeys, scientists gave the animals the task of looking at two alternating images on a computer screen. For one picture, the monkey was rewarded when it shifted its gaze to the right; for another it was similarly rewarded for looking the other way. The researchers found that by looking at monkeys' brain activity, it responded more positively to a correct answer. When they failed to get the right image however, there was little or no change in the brain, or any improvement in behaviour. It seems that although brain cells keep track of whether recent behaviours were successful or not, we learn quicker when we get things right.

For many of us who use Excel, it can sometimes feel that we can never get our formulas right. But with Excel 2010, you can now implement certain rules to check for errors in formulas. These rules act like a spelling checker that checks for errors in data in cells. Obviously the checker does not guarantee that your worksheet will be error free, but it will help you find common mistakes that you can than sort out - hopefully forever!

The Error Checker in Excel 2010 is an automated feature that tracks any errors in your worksheet formulas. It follows rules that apply to formula preparation to uncover problems. The Error Checker works in the background while you edit your worksheet.

You can track errors in different ways. Either by highlighting one error at a time, or immediately as they occur on your worksheet when you enter data. A useful warning triangle appears in the top-left corner of the cell when an error is found. You can then resolve the error by using the handy options Excel will display, or you can ignore the error by clicking Ignore Error. If you ignore an error in a particular cell, the error in that cell does not appear in further error checks. However, you can reset all previously ignored errors so that they appear again.

The Error Checker is located in the Formulas tab. Simply click from the drop-down list and the checker will automatically scan your worksheet for errors. If an error is found, you can select the Help on this error or one of the other help options to handle the error. The Options button allows you to change the options related to formula calculations, performance and error handling. You can of course specify error checking options before you use the Error Checker. On the File tab, click Options, then select Formulas and make sure the Enable background error checking box is selected. Now you can select the error checking rule check boxes that are applicable.

Excel 2010's Data Validation is another tool that enables you to ensure that data has been entered in the format you want. The rules you configure to validate data check the information, and you can instruct Excel to highlight the data that violates the rules (for example, invalid data) so that you can make corrections. Simply select the Data Validation option on the data tab. Now select Circle Invalid Data from the drop-down list. Red circles appear around cells that contain invalid data. You can also add validation rules to one type of object in a worksheet.

If you don't see the data entry options you want in the drop-down list you can create your own custom drop-down list. A custom list is useful because it allows you to enter data consistently. Simply type a set of entries in the order you want. Select the range of cells, click the name box, type a name and press Enter. Now select the cell where you want the drop-down list to appear. Click the data tab, then click the Data Validation button. Make sure the cells you want included in the drop-down list don't include any blank cells. On the Settings tab, click the Allow down arrow and then select List. Enter the values you want. Click the Input Message tab, and type a message to be displayed when someone makes an invalid entry. Now you can click the Error Alert tab and select an alert style.

If you want to correct common errors when entering formulas, then there are many resources that can help you identify the most common errors. A few of these errors include: cells containing years represented as two digits; numbers formatted as text or preceded by an apostrophe; formulas inconsistent with other formulas in the region; formulas which omit cells in a region; and formulas referring to empty cells. Now, what was the comment about a bunch of monkeys and my data formatting?

Author is a freelance copywriter. For more information on excel training, please visit https://www.stl-training.co.uk

Original article appears here:
https://www.stl-training.co.uk/article-1925-how-learn-from-your-excel-mistakes.html