The Setup
We have a spreadsheet where the user is required to fill in information only in each yellow cell.
We want to make this data entry process as streamlined as possible. We want the user to be able to use the TAB key to move from one cell to the next or use Shift-TAB to reverse navigate if necessary. Regardless of the distance from one yellow cell to the next, we want to move instantaneously from one cell to the next with each press of the TAB key.
Protecting and Unprotecting Worksheets – The Basics
To protect an entire sheet and prevent cell modification, we can use the protect feature by selecting Review (tab) -> Protect (group) -> Protect Sheet.
The Protect Sheet dialog box allows us to provide a password so unauthorized users cannot remove the protection feature. To use this feature without providing the password will still allow the feature to work, but it can be reversed with a single click and does not provide much protection beyond a stern warning.
Note: You will need to type the password twice for comparative purposes. This is to catch any accidental typos that you may not be aware of have been made.
Once protection is engaged, if you attempt to alter a cell’s content, you are presented with the following message.
If you know the password, you can unprotect the sheet by selecting Review (tab) -> Protect (group) -> Unprotect Sheet.
Poking Holes in the Wall
You can think of sheet protection as building a giant brick wall in front of the spreadsheet. Suppose we need to exclude certain cells from the sheet protection to allow for data entry.
We just need to remove a few strategically located bricks to allow our changes to pass through.
One way to accomplish this is to use the Allow Edit Ranges feature located to the right of the Protect Sheet feature.
This provides some useful features, such as assigning different passwords to different regions of the spreadsheet.
We will approach this from a simpler angle. We simply wish to omit certain cells from the Protect Sheet feature. We will begin by selecting the cells of which we want to allow editing.
With the cells selected, right-click on one of the selected cells and select Format Cells… (or press CTRL-1 on the keyboard.)
Select the Protection tab and uncheck the feature labeled Locked.
NOTE: Checking or unchecking the Locked option in the Protection sheet applies NO CHANGE to the document. The data is free to be changed. The protection doesn’t occur until we activate the Protect Sheet feature.
Once we activate the sheet protection feature, we can test the feature by attempting to alter the contents of a cell outside the prior cell selection. We are denied access to the cell. If we attempt to alter a cell in the previously selected range, we are allowed access.
The state of the Locked feature on the Protection tab in the Format Cells dialog box dictates the behavior of the cell when applying sheet protection.
Checkbox States
When applying or removing a feature that uses checkboxes, you may observe the checkbox in one of three visual states.
- Checked means the feature is on.
- Empty means the feature is off.
- Filled (or gray) means mixed state. In other words, some of the current selection is using the feature while other parts of the selection are not using the feature.
Complex (or Strategic) Removal of Cell Protection
Our objective is to protect all the cells except the yellow cells. These will be our data input cells.
Normally when we enter data into a cell and press the TAB key, the cursor moves one cell to the right. When we apply sheet protection to all the cells except the yellow cells, pressing the TAB key will cause the cursor to move to the next unprotected cell. In this case, the next yellow cell.
This is a simple matter of unchecking the Protect feature for each yellow cell.
Some users would select each and remove the feature one at a time. This not only tedious but also time-consuming in cases where there are dozens or hundreds of cells to adjust.
It doesn’t take long to realize that you can select the first cell, hold down the CTRL key and then select the remaining cells. This is faster, but still tedious when working with many cells.
We want a FAST way to select ALL cells that are of a specific color.
The first step is to highlight the range of cells that contain cells to be omitted from sheet protection.
With the range selected, press CTRL-F to activate the Find feature. On the Find tab of the Find and Replace dialog box, press the small down arrow button to the right of the Format button and select “Choose Format From Cell…”
You will be presented with a standard cell selection cursor, but one with an additional image of an eyedropper to the right of the thick plus symbol.
Place this cursor over any of the yellow cells and click. The preview feature of the Find tool will display the cosmetic attributes of the selected cell.
Be aware that when selecting a reference cell with this technique we are not only capturing the color of the cell but ALL cosmetic setting s of the cell; font color, font style, font size, number formatting, bold, italic, underline, etc.
Click the Find All button at the bottom of the Find dialog box. This will display a list of cells that possess the same cosmetic features as the reference cell.
To select all the discovered cells, press CTRL-A in the Find and Replace dialog box. This will select all the discovered items displayed in the lower panel of the dialog box.
With the cells selected, press CTRL-1 to return to the Format Cells dialog box. Return to the Protection sheet and remove the check from the Locked feature.
Click anywhere on the sheet to remove the sell selection.
Apply sheet protection in the same manner as before: Review (tab) -> Protect (group) -> Protect Sheet.
To test the new behavior, select the first yellow cell and press the TAB key. The cursor will jump immediately to the next yellow (unprotected) cell.
If you press the TAB key too many times and skip a cell, you can hold the Shift key and press TAB to perform reverse navigation.
Final Note
The order that TAB discovers which cell to jump to is based on the same left-to-right, top-to-bottom approach we use when reading a document. The TAB “reads” the protection state of the cell and either selects the cell, if unprotected or moves to the next cell in the sheet. This process repeats until the next unprotected cell is discovered.
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
