O'Reilly logo

Excel 2013: The Missing Manual by Matthew MacDonald

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required