Section 15.4. Fine-Tuning Templates
15.4. Fine-Tuning Templates
By now, you probably realize that templates aren't just a way to eliminate repetitive work when you need to create similar spreadsheets. They're also a way to let ordinary peoplethose, like you, who aren't familiar with Excel's dark artsto record information, fill out forms, and analyze data. These people need a little guidance, and templates are there to help them.
Unfortunately, Excel isn't always particularly forgiving . Even if you craft the perfect template, an Excel novice can accidentally delete or overwrite a formula just by pressing the wrong key. And it's almost as easy to put the wrong information in a cell (for example, by entering a date incorrectly so that it's interpreted as text). Furthermore, a template is no help at all when the person using it doesn't know where to start typing or what the different headings really mean. All these problems can occur (and regularly do occur) even if you think your template is a small miracle of straightforward design and organization.
The solution to all of these problems is to take your template to the next level, making it bulletproof, in effect. Excel provides a slew of useful features that can help, including data validation and worksheet protection .
15.4.1. Data Validation
Data validation is a remarkably easy way to prevent people from entering the wrong data in a cell (or at least warn them when they do). Data validation also helps make Excel a little kinder and gentler for a novice by letting you create custom error messages and add helpful pop-up tips.
To apply data validation, move to the appropriate cell, and choose Data 
Validation. A Data Validation dialog box appears (Figure 15-10), with three tabs: Settings, Input Message, and Error Alert.
Tip: You can apply validation to a number of cells at once. Just select all the cells before you choose Data
Validation. Once you've chosen the data type, you still need to set the data range . You do so by choosing a comparison from the Data list box, and then specifying the values that you want to use for your comparison. For example, if you want to restrict input to a whole number from 0 to 5, choose "between" and set a minimum value of 0 and a maximum value of 5. Other comparisons you can use include less than, greater than, greater than or equal to, and so on. You set the data range for all data types, including dates, times, and text (in which case you set limits on how many characters are allowed in the text).
|
Note: Data validation begins policing a cell only after it's been applied. That means if you apply a validation rule and the cell already contains invalid information, Excel doesn't complain.
15.4.1.2 Input Message
Once you've added data type rules, it's a nice touch to give the person using your template fair warning. You can do so by adding a pop-up message that appears as soon as somebody using the template moves to the corresponding cell. A typical input message has two components : a title (displayed in bold), and a more detailed message. In the Data Validation Dialog box (Data Validation), on the Input Message tab (shown in Figure 15-11), you can enter both of these pieces of information in the Title and Input message boxes. (Also, while you're there, make sure the "Show Input message when cell is selected" checkbox is turned on. Otherwise, Excel won't show your message.)
Input messages should contain more than just data validation settings. Ideally, your input message explains a little bit about the data that the cell is looking for. In fact, depending on who'll be using your template, you can include information about the type and format of information, what restrictions they need to be aware of, and even how they should enter the information.
For example, for an invoice date cell, you might want a message that says "This is the date your invoice was submitted to your manager. When you enter a date, use the format day-month-year (as in 29-1-2004 for January 29, 2004), and make sure you don't enter a date earlier than 1-1-2003." For a payment code, you might include a message like "This is the code from the top-right corner of your pay stub. All pay codes start with the letters AZ."
|
15.4.1.3 Error Alert
Despite your best attempts, someone, somewhere will probably still type the wrong information in a cell, defying your input messages. In this case, you need to respond by politely explaining the problem. Unfortunately, Excel's standard error message leaves a lot to be desiredit's unnecessarily harsh and confusingly vague. A better approach is to use the Error Alert tab to define your own error message. Begin by turning on the "Show error alert after invalid data is entered" checkbox. This setting tells Excel to monitor the cell for invalid information. If you enter the wrong data, a dialog box appears with an error message. The buttons that Excel uses in that dialog box depend on the style of error message you choose.
To choose an error message style, select an option from the Style list. Excel novices will appreciate it if you use the friendly Information icon, as shown in Figure 15-12, instead of the red X alert icon. But keep in mind that different icons have different effects on whether Excel tolerates invalid input. Here are your choices:
-
Stop . Excel displays the error message along with a Retry and Cancel button. The person using the template must click Cancel to reverse the change (which returns the cell to its last value) or Retry to put the cell back into edit mode and try to fix the problem. The Stop option is the only Style choice that completely prevents the person using the template from entering invalid data.
-
Warning . In this case, the error message includes Yes and No buttons that let the person entering the data decide whether or not to go ahead with the input. Clicking Yes makes Excel accept the data entered into the cell, even though it's broken the validation rules.
-
Information . The error message comes with Cancel and OK buttons. Clicking OK enters the new (invalid) data in the cell; Cancel leaves the cell unchanged.
|
After you've set the icon, specify a title, and type a descriptive message indicating the error message you want to appear. Remember, you won't know exactly what went wrong, so it's best to reiterate the data type rules you've applied, or use a phrase that begins with something like "You've probably..." to identify a common problem.
Note: If you set a data validation rule but turn off the "Show error alert after invalid data is entered" checkbox, the person entering data won't see any error messages and won't have any idea if she's entered the wrong type of information. The only reason you would ever switch off the "Show error alert after invalid data is entered" checkbox is if you want to temporarily disable your validation rules but not remove them, so that you can apply them again later.
| GEM IN THE ROUGH Quickly Spotting Every Error |
| Auditing circles (found on the Formula Auditing toolbar and first introduced in Chapter 12) are an often-overlooked Excel troubleshooting tool. When you click Circle Invalid Data, Excel draws a red circle around every cell that's broken a validation rule on the entire worksheet. You can click Clear Validation Circles to remove this information. The auditing circles aren't required when you're using strict data type validation (in other words, when you're rejecting all errors with an Alert message), because you'll never end up with invalid data. However, it comes in very handy when you're allowing errors and only showing a Warning or Information message. You might choose to apply these invalid data circles if you want to let one person fill out a template, but let another person review it. In this scenario, the auditing circles can help the second person in the chain automatically find the mistakes that need to be corrected. |
15.4.2. Data Validation with Formulas and Cell References
You can create more advanced cell restrictions by using formulas and cell references. For example, imagine you want people to enter the current date on a form they're filling out. You want to make sure that the date is no earlier than the current date (so the person filling out the template can't claim he has already submitted the form). In this scenario, you can't use a literal date in your data validation rule because the date limit needs to change depending on when someone uses the template.
However, you can use the TODAY( ) function to help you out. To do so, select Data Validation, select the Date data type, and require values to be greater than or equal to =TODAY( ) . In other words, when someone types information into that cell, Excel runs the TODAY( ) function and compares the result against the cell value. This example is one way you can embed a function within a data validation rule.
Another option is to use a formula that contains a cell reference. For example, you might want to make sure that an expense cell always contains a value that's equal to or less than a corresponding budget cell. In this case, you can't put the budget limit directly into the data validation rule, because you don't know what the budget will be until the person using the template fills it in. The solution is to create a data validation rule that uses a formula that references the value of the budget cell. For example, you could specify that the value in the expense cell must be less than or equal to the formula =C3 , assuming the budget value is in cell C3. (Remember, empty cells count as a zero value, so Excel won't allow any expense if cell C3 is empty.)
For an even more powerful approach, choose the Custom data type from the Allow list box (see Figure 15-13). When you use Custom, you must supply a conditional formula in the Formula box. A conditional formula is simply one that responds with a value of either true or false . (Chapter 12 tells you all about how to create conditional functions.) If the result is true, Excel allows the cell entry. If it's false, Excel displays your custom error dialog box (assuming the "Show error alert after invalid data is entered" checkbox in the Error Alert tab is switched on). The neat thing about conditional formulas is that you can combine as many unrelated conditions as you need, using the conditional functions AND( ) and OR( ).
|
Conditional validation rules are also useful if you need to compare the current cell against the value returned by a function. For example, the following fairly intimidating looking formula prevents the person using the template from entering a date that falls on a weekend . B3 is the cell containing the validation rule, and the WEEKDAY( ) functions verify that B3 doesn't represent a Saturday (a value of 1) or a Sunday (a value of 7). This formula forces B3 to adhere to both these restrictions by using the AND( ) function:
=AND(WEEKDAY(B3)<>1, WEEKDAY(B3)<>7)
Another useful example of how you might use a conditional formula in a data validation rule is when you need to monitor a group of cells and make sure their total value doesn't exceed a total that you specify. For example, if you don't want the series of expense items in cells B2 to B7 to total more than $5,000 dollars, you would select all these cells, choose Data Validation, choose the Custom data type, and then supply the following formula:
=(SUM($B:$B)<=5000)
Figure 15-13 shows an example that introduces an improvement on this formula. Instead of using an exact budget limit in the data validation rule, the budget limit is supplied in another cell.
Note: You might notice that the SUM( ) formula uses absolute cell references (for example, $B$2) rather than ordinary references (like B2). That lets you apply the exact same SUM( ) formula to multiple cells at once. If you don't take this step, Excel modifies the cell references in each subsequent cell, which isn't what you want. See Section 7.3.1 for a refresher on the difference between absolute and ordinary cell references.
Another reason you might use the Custom data type and write a conditional formula is to prevent duplicates in a range of cells. For example, the next formula checks to see that there's no other instance of B3 in the range of cells from B2 to B7. This validation rule goes into cell B3.
=COUNTIF(B2:B7,B3)<=1
This formula isn't quite as convenient as the summing formula. That's because the SUM( ) formula applies to all the cells in a range. The COUNTIF( ) formula needs to be tweaked for each cell. The formula shown above is what you'd use to validate the contents of cell B3, but in order to perform the same check for duplicates in the other cells in the specified range (B2 to B7), you'd need to modify the formula (replacing the reference B3 with whatever cell you wanted to check).
15.4.3. Data Validation with Lists
The only other data type choice you have (when filling out the Allow field in the Settings tab, as shown in Figure 15-10) is the List option. The List choice is interesting because it doesn't just restrict invalid values, it also lets you add a handy drop-down list box that appears when anyone using your spreadsheet moves into that cell. The person who's entering data can use the list to quickly insert an allowed value, without needing to type it in. You can also type values in by hand, but Excel assumes that if the value you enter doesn't match one of the entries in the list, your entry is invalid (and shows an error message depending on your Error Alert settings).
To create this list, choose the Settings tab in the Data Validation dialog box, and choose List from the Allow text box. You have two choices for supplying a list in the Source box: you can type in a list of comma-separated values (like 1,2,3 or blue, black, red ), or you can use a cell range that contains a list. If you want the person using the list to be able to choose the entry from a drop-down list of options in the cell (which is a slick and convenient touch), make sure you keep the "In-cell dropdown" checkbox turned on.
Figure 15-14 shows an example that modifies the lookup worksheet used in Chapter 11. It lets you create an invoice by choosing products from a drop-down list.
|
Note: List validation can't use information that's contained in other worksheets. The only solution is to put your list source in the same worksheet where you use the list validation. This isn't a perfect solution because it can lead to an overcrowded worksheet. Often, template gurus handle these issues by adding extra data at the bottom of the worksheet, and then hiding the rows that contain the data with the Format
Row Hide command. worksheet protection features.
To understand how worksheet protection works, you need to know that each cell can have one of two special settings:
-
Locked . When a cell is locked, you can't edit it.
-
Hidden . When a cell is hidden, its contents don't appear in the Formula bar. If the cell uses a formula, Excel hides it.
You can use these settings individually or together. For example, a cell that is both locked and hidden can't be edited or viewed in the Formula bar. On the other hand, if a cell is hidden but not locked, people can edit the cell but they can never tell whether the cell uses a formula because Excel keeps that information secret.
Tip: Although Excel's worksheet protection really shines with templates, you can use it in any workbook file. For example, worksheet protection is just the ticket if you need to send someone a document, but you don't want them to edit it at all .
The most important thing you need to understand about locked and hidden cells is that these settings come into effect only when the worksheet is protected . If the worksheet isn't protected (and every worksheet begins its life without protection), Excel doesn't use these settings at all. In other words, there are two steps to building a bulletproof worksheet. First, you start by specifying which cells are locked and hidden, and then you finish up by protecting the sheet so your settings take effect.
Interestingly, every cell in your worksheet starts off in an unhidden and locked state. That means that if you switch on worksheet protection without making any changes, your whole worksheet becomes read-only. There's a reason that Excel uses this approach. Typically, you'll use worksheet protection to make sure the person using your template can edit only a few select cells. It's much easier to designate the few cells that are editable than try to select every single cell that needs to be locked. (Remember, every Excel worksheet boasts an impressive 16,777,216 cells, even if most of them are empty.)
Note: Once you've protected a worksheet, you can't change the protection settings of any cells. Of course, you can unprotect a worksheet, as explained below, and then change the cell's protection settings.
Here are the steps you need to follow to protect your worksheet:
-
First, unlock all the cells you want people to type information into.
You can perform this procedure one cell at a time, or you can select an entire range of cells. Once you've made your selection, choose Format
Cells. The Format Cells dialog box appears. -
Click the Protection tab. Then, turn off the Locked checkbox, and click OK.
Next, you need to hide formulas that you don't want the template user to see.
-
Select the cell or cells with the formulas you want to hide, and choose Format
Cells again. This time, click the Protection tab, turn on the Hidden checkbox, and click OK. If you want, you can change both the Hidden and Locked settings for a cell or group of cells at the same time. Once you've finished unlocking and hiding to your heart's content, it's time to protect the sheet.
-
Select Tools
Protection Protect Sheet. In the Protect Sheet dialog box that appears, turn on the checkbox labeled "Protect worksheet and contents of locked cells." In addition to protecting the contents of unlocked cells, this dialog box lets you toggle on or off a list of Excel actions that you want to allow users of your worksheet to perform, as described in the next step.
-
From the "Allow all users of this worksheet to" list, turn on the things that you want users of your worksheet to be able to do.
Excel's standard approach is to restrict everything except for cell selection (the first two options). Here's a setting-by-setting breakdown of your choices:
-
Select locked cells . Turn off this checkbox if you want to prevent people from moving to cells that are locked. Although many templates use this setting, keep in mind that if you can't select a locked cell, there's also no way to copy and paste the information in the cell to another worksheet or program.
-
Select unlocked cells . Turn off this checkbox if you want to prevent people from moving to cells that are unlocked. You won't use this setting very often, but you might use it in conjunction with the "Select locked cells" setting to lock someone out of the worksheet entirely.
-
Format cells, Format columns , and Format rows . Turn on these checkboxes if people need to be able to format individual cells or entire columns and rows. If you allow row and column formatting, Excel also permits people to hide rows and columns. However, Excel never lets anyone change the locked and hidden settings of a cell while it's protected.
-
Insert columns and Insert rows . Turn on these checkboxes if you want to let people insert new rows or columns.
-
Insert hyperlinks . Turn on this checkbox if you want to let people insert hyperlinks in unlocked cells. This setting can be dangerous because a hyperlink can point to anything from another worksheet to a malicious Web page. See Chapter 24 for more information about hyperlinks.
-
Delete columns and Delete rows . Turn on these checkboxes to bestow the ability to remove columns or rows. Use this setting at your peril because it lets people decimate your worksheetfor example, removing entire ranges of data even if they contain locked cells.
-
Sort . Turn on this checkbox to let people sort unlocked cells, while keeping locked cells impervious to sorting.
-
Use AutoFilter . Turn on this checkbox to let people use filtering on any data lists in the worksheet. See Chapter 13 for more information about data lists.
-
Use PivotTable reports . Turn on this checkbox to let people manipulate any pivot tables in the worksheet. See Chapter 20 for more information about pivot tables.
-
Edit objects . Turn on this checkbox to let people edit or delete embedded objects in the worksheet. These objects can include data from other programs or, more commonly, pictures (see Chapter 18) or charts (see Chapter 16).
-
Edit scenarios . Turn on this checkbox to let people edit or delete what-if scenarios. See Chapter 19 for more information about scenarios.
-
-
If you want to stop other users from unprotecting the worksheet, specify a password in the "Password to unprotect sheet" text box (see Figure 15-15).
Once you protect a worksheet, anyone can unprotect it. All a person needs to do is select Tools
Protection Unprotect Sheet. This behavior makes sense if you're just using protection to prevent people from making casual mistakes. However, if you're worried about deliberate tampering, or if you want to create a truly invulnerable worksheet, it's a good idea to set a password. If you do, no one can unprotect the sheet without supplying the exact same password. -
Click OK.
The protected worksheet won't look any different, but if you try to edit a locked cell, you'll get an error message explaining that the cell is locked (but explaining that you can unlock the worksheet by selecting Tools
Protection Unprotect Sheet).
Figure 15-15. The Protect Sheet dialog box lets you set a password that will prevent anyone else from tampering with your worksheet.
15.4.5. Protecting the Entire Workbook
There is one more level of protection that you can use: Excel gives you the power to protect an entire workbook. When a workbook is protected, Excel prevents people from inserting, moving, or removing worksheets ( tasks that were covered in Chapter 5).
Workbook protection works hand-in-hand with worksheet protection. For example, if you use workbook protection but don't use worksheet protection, people can still edit all the cells in your worksheets. However, they can't delete the worksheets or add new ones. On the other hand, if you use workbook protection and worksheet protection, people won't be able to tamper with your data or the structure of your workbook.
To enable workbook protection, select Tools Protection Protect Workbook. The Protect Workbook dialog box appears, as shown in Figure 15-16.
The Protect Workbook dialog box provides two checkboxes:
-
Structure . When you turn this option on, the people using your worksheet can't insert or delete worksheets. They also can't rename an existing workbook, hide it, or move it from one place to another.
-
Windows . When you turn this option on, Excel doesn't let anyone change the size or position of windows in the workbook. Because modern-day Excel uses a single-document interface, windows normally aren't something you need to worry about. Instead, workspaces provide similar features with more predictable behavior. See Chapter 6 for more information about workspaces that let you work with multiple windows at the same time.
|
You can also supply a password to prevent people from unprotecting your workbook. You can use the same password that you used to protect a worksheet, or you can choose a new password.
Once you're finished, click OK to apply your protection settings. You can remove workbook protection by choosing Tools Protection Unprotect Workbook, although if you used a password, you'll need to have it handy.
| Word to the Wise Protecting Yourself from Password Protection |
| Do some careful thinking before you start using passwords. If you don't plan in advance, you might wind up having to keep track of (and pass around) dozens of different passwords, one for each worksheet in a workbook, and another for each workbook you create. Managing all this confidential information can become quite a chore, and losing access to your data because you've forgotten a password is sometimes worse than having that same information fall into the wrong hands! There are commercial services and programs that try to guess lost Excel passwords by brute force. Unofficial estimates suggest that even when this approach works, it takes about 30 days of continuous cranking on a fairly powerful Pentium 4 computer. So it's definitely not the recipe for getting back to your data in a hurry. This goes double when you're using passwords to encrypt spreadsheet files (as described on Section 1.4.2 in Chapter 1). |
EAN: 2147483647
Pages: 185