Data Validation, Conditional Formatting and Protecting Your Sheet

A sheet that only you use can be rough. A sheet that other people fill in needs rules. Otherwise someone types Nortth for North, enters a negative quantity, or overwrites a formula, and the totals quietly go wrong. Three features help: data validation to control what can be typed, conditional formatting to make important values stand out, and protection to guard the cells that must not change. These are the features that make a workbook look professional and trustworthy.

Data validation: only allow good entries

Select the cells, then choose Data, Data Validation. In the Settings tab you pick what is allowed.

• Whole number or Decimal between limits. For Qty, whole numbers from 1 to 1000.
• List. The user picks from a drop-down. Type the items separated by commas (North,South,East) or point to a range on another sheet.
• Date within a range, such as not before the start of the year.
• Text length, for example a code that must be 6 characters.
• Custom with a formula. For example =COUNTIF($A$2:$A$100, A2)=1 allows each value only once.

A drop-down list is the best fix for the problem of spelling differences. Select C2:C11, choose Data Validation, List, and type North,South,East. Each cell now shows an arrow and offers only those three values. Anything else is rejected.

Use the other two tabs well. Input Message shows a short help note when the cell is selected, such as Pick a region. Error Alert gives the message and the style when someone breaks the rule. Style Stop blocks the entry. Warning and Information allow the person to continue. Write messages in plain words: Please choose North, South or East from the list.

A list that grows

If the list of allowed values changes over time, put them in a Table (lesson 10) on a Lists sheet. Select the list cells, give them a name in the Name Box (for example RegionList), and type =RegionList as the validation source. A simpler way is to store the list in a column and refer to it with an absolute range that has some empty space at the end.

Conditional formatting: colour by rule

Select the cells and choose Home, Conditional Formatting. Excel changes the look of a cell depending on its value, and the formatting follows when the values change.

• Highlight Cells Rules. Greater Than, Less Than, Between, Equal To, Text that Contains, Duplicate Values. Select Amount, choose Greater Than 2500, and pick a fill colour. The 2800 amounts are highlighted.
• Top/Bottom Rules. Top 3 items, above average, bottom 10%.
• Data Bars. A small bar inside each cell shows the size of the value.
• Colour Scales. A gradient from low to high.
• Icon Sets. Arrows or traffic lights.

Use these sparingly. Too many colours hide the message. A good rule: highlight the exceptions, such as overdue items, low stock and errors, and keep the rest plain.

Formula-based rules

Choose New Rule, Use a formula to determine which cells to format. Write a formula that is true for the first cell in the selected range. Excel applies it to the rest in the same pattern. To shade the whole row when the region is North:

Applies to:  =$A$2:$G$11
Formula:     =$C2="North"

The dollar sign in front of C fixes the column, and the missing dollar sign before 2 lets the row change. This is the mixed reference from lesson 3, and it is the key to row highlighting. More examples:

=$G2>=2500                      big sales
=AND($D2<5, $C2="East")         small East orders
=$A2<TODAY()-30                 more than 30 days old
=COUNTIF($B$2:$B$11,$B2)>1      duplicates
=MOD(ROW(),2)=0                 shade every second row

To remove or edit rules, use Conditional Formatting, Manage Rules and choose to show rules for This Worksheet.

Protect cells and sheets

By default every cell is locked, but locking does nothing until you protect the sheet. The recipe: first unlock the cells that people should be able to change, then protect the sheet.

1. Select the input cells, such as Qty, Price and Staff. Press Ctrl+1, open the Protection tab and untick Locked.
2. Choose Review, Protect Sheet. You can set a password, or leave it blank for protection against accidents only.
3. Tick the actions you still want to allow, such as selecting cells, sorting or using filters.
4. Press OK. Now only the unlocked cells can be edited, and the formulas are safe.

Be honest about what protection is. Sheet protection and workbook passwords are meant to prevent mistakes. They are not strong security, and passwords on sheets can be removed with widely known methods. Never rely on them for confidential data. For that, use proper file permissions, encryption and access controls. If you set a password, keep it in a safe place. If you lose it, you may not be able to edit your own sheet.

Hide and show

• Hide a column or row with right-click, Hide. Unhide by selecting around it, right-click, Unhide.
• Hide a sheet from the tab right-click menu. Hidden items are not secure. Anyone can unhide them.
• Never hide something just to look tidy if someone else needs to check it. Say it is there.

Check your workbook before sharing

1. Review, Check Accessibility and fix the main problems, such as missing headings.
2. File, Info, Check for Issues, Inspect Document to find hidden data, comments and personal information that you may not want to share.
3. Look for errors with Formulas, Error Checking.
4. Have someone else try the workbook with fresh eyes.

Your task

1. Add a drop-down list for Region (North, South, East) with an input message and a stop alert. Try to type another value.
2. Add a rule so Qty must be a whole number between 1 and 1000.
3. Highlight Amount values of 2500 or more, and shade whole rows where Region is North with a formula rule.
4. Unlock only Qty, Price and Staff, then protect the sheet and check you cannot overwrite a formula.

How to check it worked

• A mistyped region is rejected, and the drop-down offers the three valid values.
• Two amounts (2800) are highlighted by the Greater Than 2500 rule.
• Clicking a formula cell on the protected sheet gives a warning that it is protected.