This month, learn how to protect Excel's Data Validation feature from violators and get quick access to your favorite folders.
This month's Q&A column starts with a bit of a conundrum and ends with one of the simplest solutions ever. First, Mac has a staffing problem. He has a user who changes the data validation settings, so he can enter invalid data. The technical solution is easy, but the staffing problem is going to take a lot more work. Next, Becky wants quick access to folders and Windows, not Office.
I'm using Office 365 desktop on a Windows 10 64-bit system. There's no demonstration file for this article; you won't need one.
SEE: Cost comparison calculator: G Suite vs. Office 365 (Tech Pro Research)
Mac has a problem that's more serious than I can completely solve. He has a data entry person who circumvents the data validation settings. This person has enough knowledge that he changes the settings, so he can enter data that doesn't conform to Mac's settings. This set off a lot of alarms, but first things first—there is a way to thwart this violator.
The simplest way to protect data validation from ill intent is to protect the sheet. Once protection is enabled, users can't access the data validation feature. It's a simple process but requires a bit of planning. We can illustrate this with a simple example. First, let's set a validation rule for cell D4 in a blank worksheet:
- Select D4.
- Click the Data tab and then click Data Validation in the Data Tools group.
- In the resulting dialog, choose Decimal from the Allow dropdown.
- Choose between from the Data dropdown.
- Enter .5 in the Minimum control.
- Enter .9 in the Maximum control (Figure A).
- Click OK.
These settings will limit the values you can enter into D4.
Test it a few times to see how well it works. For instance, if you enter .6, Excel accepts the value. If you enter .4, it displays the error message shown in Figure B.
Excel rejects values that don't satisfy the validation rule.
Now you're ready to protect the sheet. This step is the last thing you'll do before distributing it. First, unlock input cells, such as D4 so users can still enter data. To do so, right-click D4 (and all input cells) and choose Format Cells from the resulting submenu. Click the Protection tab and uncheck the Locked option (Figure C) and click OK. Locked is the default, but it doesn't mean anything until you enable protection.
Unlock the cell.
To protect the sheet, click the Review tab and click Protect Sheet in the Protect group. Enter a password, such as pw and click OK. Retaining the default options will allow users to select all the cells, but they won't be able to modify locked cells. Remember though, you unlocked the cell with data validation so users can modify its contents. Re-enter the password pw to confirm it and click OK.
Select D4 and enter any value; the validation still works, and your users can input data. Now, click the data tab and try to click the Data Validation option in the Data Tools group. You can't because it's disabled. You can tell it's disabled because it's dimmed, as shown in Figure D.
The Data Validation option is disabled in a protected sheet.
By protecting the sheet, Mac protects the validity of the data. Users must enter a value between .5 and .9 or leave the cell blank. A user must know the password in order to unlock the sheet and gain access to the Data Validation option.
That might be the end of Mac's problem, or not. There's more to consider than Mac's data. There are a few reasons a user might change settings to enter data you don't want them to, and they're not all bad.
The first thing I'd check into if I were facing Mac's situation, is the validation settings themselves. Are they wrong? It's possible that the user is changing the settings because he has valid data. I don't think that's really what's going on with Mac's user, but it's worth double-checking before you confront a user.
If the settings are correct, I'd check into the user's training. Obviously, he has the skills to change the validation settings, but perhaps he doesn't understand the importance of respecting those settings. Perhaps the user doesn't realize he shouldn't do what he's doing. It's possible, and with proper training, trust can be restored.
If neither of the above has an impact, I'd use someone else to input data. Even though the sheet is protected, that only goes so far. A user who refuses to respect your rules is probably cheeky enough to try to break your password; the trust is lost.
Please feel free to share your thoughts in the comments section. I'd like to hear how others would handle this situation.
SEE: Employee termination checklist (Tech Pro Research)
Window's Quick Access
You work in unison with Windows when using Office; it's behind the scenes making everything possible. Becky relies heavily on Windows Quick access feature in File Explorer. This area stores recently used files and frequently used folders. It's convenient, but Becky also finds it frustrating because the files and folders change as she works—it's the nature of the feature.
She wants some folders to always be available in Quick access. She was relieved to hear how easy that is. You simply pin a folder to it.
To pin a folder to Quick access, open File Explorer and find the folder. Right-click it and choose Pin to Quick access from the resulting submenu shown in Figure E. The folder will be available in the navigation pane in the Quick access hierarchy (at the top) and in the Frequent folders area until you unpin it. That's it.
Pin folders to Quick access in File Explorer.
Send me your question about Office
I answer readers' questions when I can, but there's no guarantee. Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at email@example.com.
- How to add a traditional glossary to a Microsoft Word document (TechRepublic)
- 4 tips for refreshing Excel PivotTable objects (TechRepublic)
- Office Q&A: Excel referencing, Word field codes, and a table trick (TechRepublic)
- 2 ways to annotate a Word table of contents (TechRepublic)
- Use Excel data validation to prevent duplicate values in a column (TechRepublic)
- Microsoft to add new geography, stocks data types to Excel (ZDNet)
- Microsoft Office 365 for business: Everything you need to know (ZDNet)