| Home > About PCs > Excel > Advanced tips |
Excel has a protection system, that allows you to prevent changes to defined regions you choose on individual worksheets, and also to prevent deletion or insertion of new worksheet pages in a workbook. Part of the way this system works is a little counter-intuitive, and tends to confuse people at first.
You can also apply passwords to protected worksheets and workbooks, and separately under Save As, Options you can apply passwords for opening workbook files.
MS Office file passwords are not secure and were never intended to be. There are a great many third-party utilities available on the Internet to recover lost Office file passwords. Therefore these password features will never be effective for preventing customers from seeing proprietary or trade secret information, for example, or for serious confidentiality or security concerns inside the workplace.
I recommend not using the Office file password features at all. To control access to information in Office files, you should rely on centralized control of the folder permission settings in your network operating system.
The best use of the Excel protection system is to prevent a data entry person from accidentally damaging formulas or formatting in your spreadsheet file. Passwords aren't needed for this purpose.
There are two different menu locations that together control worksheet protection:
In a new blank worksheet all cells are formatted Locked, but worksheet protection is off (Tools menu) so the locked cell formats have no effect. This is the part that confuses some people; it seems to work backwards to expectations, but the way it works actually makes it less work to use, once you get used to it.
Here's what you do: select all the cells you want the user to be able to edit, either one range at a time or all together using the Control key, and go to Format Cells Protection and uncheck Locked. Then you can go to Tools Protection and turn on sheet protection. The user will only be able to type in the cells you formatted unlocked; if they try to change a locked cell, deliberately or by accident, they'll get an error dialog.
To prevent the user from deleting or inserting worksheet tabs, go to Tools, Protection, Protect Workbook. In both this dialog and the Protect Sheet dialog, most of the time you won't need to change any check box options.
When either sheet or workbook protection is on, the submenu item at Tools Protection (pictured above) will say "Unprotect Sheet" or "Unprotect Workbook" instead of "Protect."
This issue came up a lot in support calls. People will build or import a large Excel list, and then need to count or sum entries (consolidation) that meet certain criteria (conditional consolidation). In the right margin below is a table of sample data meant to simulate an Excel list. Bear in mind that this little table stands for real lists which might have hundreds or thousands of records, and usually more than three columns.
| A | B | C | |
|---|---|---|---|
| 1 | Region | Code | Amount |
| 2 | Eastern | A | $44.01 |
| 3 | Central | B | $199.22 |
| 4 | Western | B | $977.81 |
| 5 | Central | B | $932.25 |
| 6 | Western | B | $629.54 |
| 7 | Eastern | B | $62.29 |
| 8 | Central | A | $70.71 |
| 9 | Western | B | $984.22 |
| 10 | Eastern | A | $519.52 |
| 11 | Central | B | $73.82 |
If you need to count or sum records based on a single test criterion, you can use the simple functions COUNTIF() and SUMIF().
| English | Simple formula | Result |
|---|---|---|
| Count records with region Eastern | =COUNTIF(A2:A11,"Eastern") | 3 |
| Sum records with region Eastern | =SUMIF(A2:A11,"Eastern",C2:C11) | $625.82 |
Of course, for single-criterion sums, you can also sort the list on the criterion column and then generate subtotals. It probably depends on how you want your results formatted.
To test multiple criteria you can use a more advanced technique using array formulas with nested SUM() and IF() functions.
An Excel array formula performs operations on multiple cells, or produces output in multiple cells, or both. An array formula can be recognized in a worksheet by the whole formula being enclosed in a pair of curly braces when viewed.
To create an array formula you must press Control-Shift-Enter—instead of Enter—every time you finish typing or editing the formula. You can't type the curly braces from the keyboard; you have to use Control-Shift-Enter each time to make it an array formula. If you forget and use Enter, you most likely will get an error, or possibly incorrect results.
| English | Array formula | Result |
|---|---|---|
| Count records with region Central and code A | {=SUM(IF((A2:A11="Central")*(B2:B11="A"),1,0))} | 1 |
| Count records with region Central and code B | {=SUM(IF((A2:A11="Central")*(B2:B11="B"),1,0))} | 3 |
| Sum records with region Central and code A | {=SUM(IF((A2:A11="Central")*(B2:B11="A"),C2:C11,0))} | $70.71 |
| Sum records with region Central and code B | {=SUM(IF((A2:A11="Central")*(B2:B11="B"),C2:C11,0))} | $1,205.29 |
You also have the option to use cell references in the tests, instead of quoted strings, if that works better:
{=SUM(IF((A2:A11=E2)*(B2:B11=F2),1,0))}
Cells E2 and F2 would then contain the text of the region and code to be matched. This lets you change the values used in the tests without modifying the array formula.
Array formulas take a lot more processing during recalculation than simple formulas. You may run into fundamental limitations of Excel sooner with array formulas than with simple formulas. I've used the small range references A2:A11 and B2:B11 here for clarity. In real applications you'll have bigger ranges to deal with, but keep your range references limited to the ranges with the actual data. Don't try to use range references covering 20,000 rows if you only have 1,000 or 2,000 rows of data. You'll also need to stick with explicit range addresses like "A2:A2000"; whole-column references like "A:A" won't work at all.
There are some non-array methods for multiple criteria under certain conditions, but the array technique given here is a general extensible one.
The multiplication operator (asterisk) between the two tests above serves as a logical AND in an array formula, because of the way Excel converts between logical and numeric values.
Similarly, the addition operator (+) can serve as a logical OR. These operators can be combined using parentheses for more complex tests, as seen in the formulas below.
| English | Count records with region Central or Western and code B |
|---|---|
| Array formula | {=SUM(IF(((A2:A11="Central")+(A2:A11="Western"))*(B2:B11="B"),1,0))} |
| Result | 6 |
| English | Sum records with region Central or Western and code B |
|---|---|
| Array formula | {=SUM(IF(((A2:A11="Central")+(A2:A11="Western"))*(B2:B11="B"),C2:C11,0))} |
| Result | $3,796.86 |
In these examples, the nesting of the parentheses forces the "Central OR Western" test to be evaluated first, then for records where the result of that is TRUE, the "AND code B" test is evaluated.
You may have noticed we're using the SUM() function for both counting and summing here. In the "count" examples, the second argument of the IF() just adds 1 to the total each time the formula finds a record that meets the test criteria, so the result is a count of such records. In the "sum" examples that second argument is an array reference instead, which results in the actual dollar amounts being summed.
Excel's logical functions AND() and OR() normally do the same thing we are using the multiplication and addition operators for here, but those functions don't work in array formulas. When you combine logical tests in simple non-array formulas, you can use either the functions or the operators.
The truth tables below demonstrate how multiplication and addition operations serve as logical AND and OR tests in these array formulas. The X and Y columns represent the TRUE or FALSE results of two different logical tests, presented as they are interpreted by the numeric operators.
=======AND========== =======OR=========== X Y X*Y RESULT X Y X+Y RESULT ==================== ==================== 1 1 1 TRUE 1 1 2 TRUE 1 0 0 FALSE 1 0 1 TRUE 0 1 0 FALSE 0 1 1 TRUE 0 0 0 FALSE 0 0 0 FALSE
To reproduce these array formulas in your own spreadsheet, copy and paste just the formula text inside the curly braces, then use Control-Shift-Enter to make it an array formula, thereby adding the curly braces.
If you're using the Microsoft Internet Explorer 6 Web browser rather than Firefox or Opera, it probably won't let you select just the formula text inside the curly braces; you'll have to edit the pasted text in Excel to remove the braces, before you press Control-Shift-Enter. Or just get Firefox. I don't know how IE7 behaves. I was presenting the array formulas here without the curly braces, for the convenience of IE6 users, but every so often I'd get email from someone who hadn't read anything in this section but the formula text.
My Excel KB articles page links to articles about array formulas and conditional consolidation.
{=SUM(IF((A2:A11="Central")*(B2:B11="A"),1,0))}
| ||________________| |__________| ||
| |___________________________________||
|_______________________________________|
{=SUM(IF(((A2:A11="Central")+(A2:A11="Western"))*(B2:B11="B"),1,0))}
| |||________________| |________________|| | | ||
| ||_____________________________________| |__________| ||
| |________________________________________________________||
|____________________________________________________________|
The VLOOKUP() function and list-type data validation are powerful techniques worth knowing on their own, and become especially interesting when used in combination.
VLOOKUP() retrieves specified values from different columns of an Excel list, based on a lookup value you specify. The way it works is exactly analogous to the organic process of looking up a phone number in the gray pages of the phone book.
Let's assume the following:
| D | E | F | |
|---|---|---|---|
| 1 | Part# | Description | Unit price |
| 2 | V90mdm | V.90 56 Kbps internal modem | $39.95 |
| 3 | Gmcrd | Video game card | $89.95 |
| 4 | Opmse | Optical mouse | $15.49 |
| 5 | Wlopm | Wireless optical mouse | $36.95 |
| 6 | Ethernt | 10/100 Mbps Ethernet card | $59.95 |
| 7 | CDROM | 52x CD-ROM drive | $19.95 |
| 8 | CDRW | 52x CD-RW drive | $48.95 |
| 9 | DVDRW | DVD±RW drive | $89.95 |
| 10 | Th128 | USB thumb drive, 128 MB | $29.95 |
| 11 | Th256 | USB thumb drive, 256 MB | $44.95 |
Now we have two other columns in the same rows on Sheet1 that need to display the appropriate description and unit price:
| English | Display description for part# in B5 |
|---|---|
| Formula | =VLOOKUP(B5,Table,2,FALSE) |
| Result | V.90 56 Kbps internal modem |
| English | Display unit price for part# in B5 |
|---|---|
| Formula | =VLOOKUP(B5,Table,3,FALSE) |
| Result | $39.95 |
VLOOKUP() accepts four arguments separated by three commas. The first argument (here B5) is a cell reference to the value to be looked up, usually on the same sheet tab. The second argument needs to be either a range name or a range address that points to the lookup table, often on a different sheet tab. I prefer using a range name because it's more readable and easier to maintain. If you add or remove data in the lookup table, you only have to make sure the range name points to the whole table, rather than having to modify all your lookup formulas.
The third argument (here 2 or 3) must be a positive integer that identifies the column in the lookup table to return data from. This positive integer requirement means that a table for VLOOKUP() must always have the lookup values in its leftmost column—which would correspond to the number 1—and VLOOKUP() will always operate from left to right in the lookup table.
The fourth argument needs to be a logical TRUE or FALSE, or defaults to FALSE if omitted. This determines whether VLOOKUP() does an exact match lookup (FALSE or omitted) or an approximate match lookup (TRUE). People commonly omit the fourth argument when they want exact match mode, or use 0 and 1 instead of the actual logical TRUE/FALSE, which is fine; I prefer to always include the explicit logical value for clarity.
If we were really going to do a printable invoice this way, we'd probably want to tweak the lookup formula so that it displays a blank cell rather than an error, for invoice rows with no part number entered yet. Excel jargon for this sort of thing is "error trapping." This can easily be accomplished by adding the IF() and ISBLANK() functions to the lookup formula.
| English | Display description for part# in B5, or a blank cell if B5 is blank |
|---|---|
| Formula | =IF(ISBLANK(B5),"",VLOOKUP(B5,Table,2,FALSE)) |
| Result | V.90 56 Kbps internal modem |
List-type data validation is a specialized cell format you can apply to a range of cells, such that the only valid entries allowed are those present in a specified list elsewhere in the workbook file. When the cell is selected, you get to choose from a drop-down list of valid entries that appears.
List validation by default only lets you specify a list source as a range address on the same worksheet tab, but there is a simple undocumented way to use a list on another sheet tab, if you assign a range name to it first. I prefer this range name method for the same reasons I cited in the VLOOKUP() discussion above.
To apply list validation, select the cells and from the menu do Data, Validation. You'll get a dialog box with three tabs: Settings, Input Message, Error Alert.
On the Settings tab under Allow, pick List. Click in the Source box and do Insert, Name, Paste, and select the range name you previously assigned. Filling in this Settings tab is the minimum requirement to get list validation working.
Also undocumented: instead of supplying a range name or range address, you can also type a comma-separated list of valid text entries right in the Source box of the Settings tab. You then have a list validation format complete in itself, that does not refer to any other location in the workbook file.
If you use the Input Message tab, every time the cell is selected, Excel will temporarily display a box of instructions next to the cell, similar to a worksheet comment or a Tooltip. I find them more distracting than helpful and usually prefer not to use the Input Message tab.
Your entries on the Error Alert tab get displayed in a regular Windows dialog box that pops up whenever the user tries to input an invalid entry. I do like to use this tab. You get to pick the icon displayed, the contents of the Title box is displayed in the title bar of the dialog box, and the contents of the Error message box become text inside the dialog box. This is maybe easier to try than to describe.
To combine list validation with VLOOKUP(), format the cells in your lookup column of part numbers with list-type data validation, so those cells can only contain valid values from the full part number list, then use VLOOKUP() formulas in the other columns to get the other data associated with the part number selected.
site feedback