Section 22.1. Understanding Excel s Safeguards

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:

  • Prevent people from changing a worksheet's structure (inserting or deleting cells , columns , or rows).

  • Prevent people from changing a worksheet's formatting (including the number format or other formatting details like column width and cell color ).

  • Prevent people from editing certain cells.

  • Prevent people from entering data in a cell unless it meets certain criteria.

  • Provide additional information about a cell in a pop-up tip box.

  • Prevent people from editingor even seeingthe spreadsheet's formulas.

  • Prevent people from moving to cells they don't need to edit or inspect.

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.

Excel 2007[c] The Missing Manual
Excel 2007[c] The Missing Manual
ISBN: 596527594
Year: 2007
Pages: 173 © 2008-2017.
If you may any questions please contact us: