99.1% Of all customers recommend us, we're so confident about our results we publish all reviews and statsView Live Stats View Reviews
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?
Original article appears here:
London's widest choice in
dates, venues, and prices
On-site / Closed company:
Found the course most helpful. The subject was very well presented and the trainer very pleasant. I would say the best up to now
TY Danjuma Family Office Limited
Assistant Family Officer
Wendy was great at covering the course content at a pace that was suitable for everyone and answering any questions we had even if they were more advanced. The syllabus is very help and all of the keyboard shortcuts will make a big difference in the future.
I found the course to be very helpful and easily understandable for the knowledge I have gained. This will definitely result in improving my efficiency with my use of excel. I would highly recommend.