Excel 2013: The Missing Manual

Chapter 21. Protecting Your Workbooks

So far, you’ve created spreadsheets that are a bit of a free-for-all. You (or anyone else) can open them and change absolutely anything, from the most minor formatting detail to the most critical formula. For everyday Excel use, this freedom makes perfect sense. However, if you’re thinking of sharing your carefully crafted work with other people—like colleagues who need to review your numbers—some caution is in order.

In this chapter, you’ll consider two tools that can help you build bulletproof spreadsheets: data validation and worksheet protection. Data validation catches incorrect values. Worksheet protection locks down your worksheets so they accept only certain types of changes in certain areas. Using these features, you can make your workbooks impervious to error (and deliberate fudging).

Understanding Excel’s Safeguards

Excel’s data validation and worksheet protection give you a number of 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 and 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 editing—or even seeing—the spreadsheet’s ...