98.8% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsPrevious article Next article Excel articles
How To Learn From Your Excel Mistakes
Wed 24th August 2011
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
London's widest choice in
dates, venues, and prices
Public Schedule:
On-site / Closed company:
TestimonialsUniversal Works Limited
Store Manager (Soho) Luke Humphries Introduction to Management In an overall sense was good for learning people skills and personal development but the course didn’t relate too much with my field of work Open Energi
Projects And Operations Administrator Alex Barnard Excel Introduction Course was perfectly paced and presented. Really helpful and patient instructor. I'd used excel previously but this has improved my overall confidence. I now won't need to ask people how to do things as much! London & Partners
Head Of Finance Amy Chidzey Excel Advanced The course was really good and learnt things I wasn't expecting to/didn't realise I you could do in excel |
PUBLICATION GUIDELINES