Managing Your Excel Spreadsheets: Microsoft Tips and Tricks

Restrict Data Input with Excel’s Data Validation Tool

When creating Excel spreadsheets for others to use, you may wish to compel users to type in specific data. With Microsoft’s Data Validationtool you are able to require data based on limits or formats that you set. For example, you can use the tool to accept only dates within a specific timeframe, or phone numbers with area code included. You can also create a dropdown list for users to choose from.

In the example below, I’m creating a company expense spreadsheet for employees to complete as they incur expenses. In cell K6, I want to restrict users from submitting expenses that occurred prior to the current claim period.

1. 1. Select the cell(s) that will contain data that you wish to validate. (In this example, we’re working on the single cell K6.)
   2. On the Data tab (in the Data Tools group), select Data Validation.
   3. The Allow box (in the Settings tab) provides several formatting options and also allows you to set your own format. (In this example, we’ll select Date.)

1. In the Data field, set your parameters for acceptable results. (I have selected between in the example because I wish to specify a date range.)
2. Specify Start Date and End Date in the appropriate fields.
3. Click on the Input Message tab. By default, this prompt will be displayed once a cell is selected. You can provide a message to notify users of acceptable information that they should input.
4. Click on the Error Alert tab. The error alert is a message that will appear if a user inputs invalid limits to the cell. By default, the STOP notification is applied and will not allow the user to select anything outside of the restrictions placed on the cell(s). You can customize the error message text that you would like to appear when this occurs.

Create a Dropdown List Using Data Validation

In our example below, we’ve moved to a different section of the expense report. In this scenario, I’m altering the look and feel of the line-item expenses.

Currently, the spreadsheet has separate columns to claim various expense types. I would like the format to show expense types under the “Description” column (in cells C12: C21) instead. But I want to limit the user to the same options for expense categories. A good solution is to validate the data in those cells using a dropdown box.

To apply the data validation:

1. Select the cells where you want to apply the restriction (I selected C12 through C21 under the “Description” column heading).
2. On the Data tab (in the Data Tools group), choose Data Validation.
3. This time, we want to choose List for the validation criteria.

1. 1. Type in the options into the Source field, using a comma to separate your items. (In this example I typed Hotel, Transportation, Fuel, Meals, Phone, Entertainment, Misc.)
   2. Apply your Input Message and Error Alert tab settings as needed (see steps 6-7 in the prior tutorial).

Now your worksheet’s users are able to choose from only the values that you input (expense categories, in our example). Excel makes it easy to update the allowed values: Whenever the list needs to be changed, simply select the cells, open the data validation tool, and edit your options.

*Please note: Users can remove, edit, and delete data validation unless you protect your workbook. Please read further to learn how.

Protect Yourself—and Your Cells—in Excel

A wide range of functionality makes Excel a potent business tool. That power can become problematic, though, when mishandled. By default, all users have the access to insert, edit, and delete any information in any workbook. In order to keep your spreadsheets functional and prevent users from making undesired or unauthorized changes to workbooks, you should always enable protection on shared-use files. Excel allows protection of both your sheets and the entire workbook.

There are several protection levels available in Excel. The most basic option is to prevent unauthorized users from viewing information on documents. This will require a password to view any information on a document.

Encrypt the Document

Excel makes it easy to require users to input a password before viewing information in any document:

1. Go the File tab (located in the Info category of Office 2013).
2. Click on the Protect Workbook icon to view options.
3. Select Encrypt with Password to add the feature. This will require all users to input your specified password.
4. After you enter a password of your choice, Excel will prompt you to re-enter your password. Be careful not to lose or forget the password, as in most cases it cannot be recovered.

Protect the Sheets (and Open Specific Cells)

By default every cell in a worksheet is set to lock when you enable protection on the sheet. You must remove the lock property from the cell(s) you want to allow users to edit.

1. 1. Select the cells you want to allow users to edit. In the example below, I’ve added red marks to indicate the cells that I am going to allow users to type into. I will remove the Lock feature from those cells—which can include Data Validation as described previously—but the remainder of the sheet will be locked and uneditable.

1. 1. Right-click in the selected cells and choose Format Cells from the menu that appears.

1. 1. On the Protection tab, ensure the Locked feature is unchecked. Notice the Hidden feature in the dialog box—you’ll want to select this feature to ensure users do not see the cell’s formula.

1. 1. On the Review tab of the main toolbar, select the Protect Sheet option. You will be prompted to enter and confirm a password and edit the restrictions on the worksheet. Excel offers several options for granting permissions. Selecting new options will grant additional control to the user.

1. The worksheet is now protected. Should you ever need to make changes to the sheet, you must remove the password from the selected sheet, save, and reapply the password to restore protection to the sheet.
2. **Please note: Each sheet must be protected separately.

Protect the Workbook

With sheet protection enabled, all cells are locked except the ones you unlocked. Users are still able to work with workbook properties, though: They can rename sheets, delete sheets, insert new sheets, and even move sheets within the workbook. For full protection of your workbooks, you’ll want to protect the workbook.

1. 1. On the Review tab of the main toolbar, select the Protect Workbook option. You will be prompted to enter and confirm a password.

1. The workbook is now protected. Should you ever need to make changes to the structure of the workbook, you must remove the password, save, and reapply the password to restore protection.

Protecting Your Excel Spreadsheets

Many people do not realize how important their data is until it is a little too late. If you get in the habit of encrypting your documents and applying sheet-wide and workbook-wide protection, you’ll be adding an extra layer of security to your company’s sensitive data.