How to avoid errors while working in Microsoft Excel
Most people start working in Excel without a good formal training because Microsoft Office Suite is installed on almost all computers. They learn the application through hit and trial. Also the rigour of validating the worksheet functions, formulas and data entry is often missing. Now such a situation can lead to devastating errors. We can use cell and worksheet protection along with getting our worksheet validated or checked by at least 3-5 colleagues before letting everyone in the organization use the spreadsheet.
We take the following steps to reduce errors in Excel worksheet:
1. Use data validation to enter, for example, the names of the students. We have used a named range.
2. The entry of the marks is also done using data validation. We allow, for example, to enter marks only with values between 0 and 100. The minimum marks are 0 and the maximum marks that a student can get in any subject is 100.
3. Next we allow the user to enter only the name of the student and the individual marks.
4. The total marks, average marks and the grade calculation is done using formulas and functions hidden and protected from the user. To make changes to the total marks, average marks and grades, a user needs a password.
Watch the video below (20 MB) to see how you can avoid serious errors while working in Microsoft Excel:
Excel Formula Errors – Understand and Debug Them