22.1. Understanding Excel's Safeguards
Excel's data validation and worksheet protection give you a number of different ways to keep your workbook on the right side of the law. Using them, you can:
You may have different reasons to apply these restrictions. Often, you'll use them to make sure people don't tamper with data. For example, perhaps you have a workbook with a carefully compiled list of sales totals, expenses, and profit calculations. You want to let others update the expense information, but they shouldn't be able to fudge the sales records. Or maybe you want to lock down all the data, and allow others to play only with the summary tables and charts . In both these situations, worksheet protection can prevent unauthorized changes, so that the data in your spreadsheet is just as reliable after it passes a round of revisions as it was when it first left your hands.
In other cases, you might use validation protection to prevent errors, particularly if you're sharing your work with a less experienced Excel patron. For example, imagine you need to give a copy of your timesheet workbook to all the employees on your team. At the end of every month, they fill out their own copies and pass the finished workbooks on to a manager. Unfortunately, an ordinary workbook is a small minefield for someone who's new to Excel. An Excel novice can accidentally delete or overwrite a formula just by pressing the wrong key, and it's almost as easy to put the wrong information in a cell (for example, by entering a date incorrectly so that it's interpreted as text). With data validation, you can lock out certain types of errors and guide the people using your workbook to make sure they fill in the right information.
Note: Worksheet protection and data validation also make great additions to Excel templates (Chapter 16). Using these features, you can make sure the people who use your templates put the right information in the right places.
Data validation and worksheet protection are two of the most powerful yet underused Excel features. Once you master them, you'll be able to safeguard your spreadsheets before you share them.