Prevent Bad Data in Excel 2010/2013 by Controlling User-Entered Data

When you simply send an Excel workbook to someone to edit or add additional data, you essentially trust them to enter the right type of data in the right places, without touching your existing data.

This often works smoothly, but sometimes there are issues with sharing a workbook. For instance, someone you send it to could accidentally (or intentionally) change the wrong cell values or formulas. And someone could enter invalid data without realizing it.

In this post, we provide a couple ways to make sure your workbook doesn’t get ruined while collaborating on an Excel doc.

Password Protect Excel Cells

To restrict certain cell areas:

  1. Choose REVIEW>ALLOW USERS TO EDIT RANGES
  2. To create a new, password-protected section, click NEW.
  3. Enter a section name, a cell range, then a password to give access to this section. (To only allow certain groups and/or users to make changes, choose PERMISSIONS, then follow the instructions provided.)
  4. Click OK.
  5. Repeat steps 2 through 4 until you have password-protected all the sections you want.
  6. Click OK.

Now, sections of your Excel file will only be editable by those who know the password.

Catch Data Problems with Validation

It many not always be obvious to someone when they should enter a date, a number, or a line of text. Data Validation can help make sure people enter data in the right format, which can be very important when you’re, for instance, sorting a list by date. You can even apply validation to text entries to make sure they’re within a certain number of characters.

To create a Data Validation rule:

  1. Select the cell or cells you want to have a dropdown list.
  2. Choose DATA>DATA VALIDATION .
  3. In the ALLOW drop-down list, choose a parameter such as WHOLE NUMBER, DECIMAL, DATE, TIME, or TEXT LENGTH. Fill in the appropriate settings.
  4. Click OK.

By default, if a user entry is an invalid, they will get an alert:

This will warn them that they’re entering the wrong data.

Ensure Valid Data is Entered with a Drop-Down List

Data validation restricts what can be entered into a cell. You can create a list of acceptable values, and a drop-down for the individual cell to ensure that the data is entered correctly. To create a drop-down list of specific values:

  1. Create a row or column of valid data. For this example, we chose the last names of US Presidents.
  2. Select the cell where you want to make the drop-down list available.
  3. Choose DATA>DATA VALIDATION .
  4. In the ALLOW drop-down list, choose LIST.
  5. In the SOURCE box click , select your list of valid cell entries, then click .
  6. Click OK.

This creates a drop-down list of valid entries that document users can select by clicking the arrow next to the cell .

Not only does it limit the cell to acceptable values, but (if you entered the original values correctly) it also eliminates the possibility of spelling mistakes and typos.

For more on using data validation, visit Microsoft’s support page with specific data validation steps and examples.

 

Do you control certain aspects of the workbooks you share with clients and colleagues? In the comments, please let us know what you do to stop user errors before they even start.

If you want to learn more about using Excel, check out our Excel 2010 Advanced & Macros Quick Reference Card, and stay tuned for the Excel 2013 resources we’re working on!

Share!