Excel's Conditional Formatting feature has many uses. Suppose you need to compare two lists, and identify the items that are different. The figure below shows an example. These lists happen to contain text, but this technique also works with numeric data.

The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with.

As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting.

How to do it

1. Start by selecting the OldList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(NewList,A2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example).
6. Click OK

The cells in the NewList range will use a similar conditional formatting formula.

1. Select the NewList range.
2. Choose Format - Conditional Formatting
3. In the Conditional Formatting dialog box, use the drop-down list to choose Formula is.
4. Enter this formula:

   =COUNTIF(OldList,D2)=0

5. Click the Format button and specify the formatting to apply when the condition is true (a green background in this example).
6. Click OK

Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed.

The cell reference in the COUNTIF function should always be the upper left cell of the selected range.

A companion file is available: Click here to download

I got lots of Excel workbooks via email. A significant number of them have some downright ugly color choices. Beauty is in the eye of the beholder, but there's no excuse for making color choices that result in illegible text.

The World Wide Web Consortium (W3C) has created some formulas that can help you determine if your foreground and background colors are legible: Ensure that foreground and background color combinations provide sufficient contrast when viewed by someone having color deficits or when viewed on a black and white screen.

The W3C presents two formulas, each of which returns a value:

• Color Brightness Difference: returns a value between 0 and 255
• Color Difference: Returns a value between 0 and 765

I converted their formulas into VBA functions, and formulas that use these functions are shown in Columns B and C:

To be an acceptable color combination, the Color Difference score should be 500 or greater, and the Brightness Difference score should be 125 or greater. I used conditional formatting to highlight values that exceed these minimums.

Column D has a simple formula that determines if both score meet the minimum requirement.

These formulas seem to work quite well. The color combination deemed Acceptable are all very legible. Bottom line: You can't go wrong with black text on a white background. Reserve the fancy colors for column headers, or for special areas of a worksheet that you want to be noticed.

A companion file is available: Click here to download

Many users are surprised to discover that Excel cannot work with dates prior to the year 1900. I create an add-in that addresses this deficiency. The Extended Date Functions add-in (XDate) allows you to work with dates in the years 0100 through 9999.

When the XDate add-in is installed, you can use any of the following new worksheet functions in your formulas:

• XDATE(y,m,d,fmt): Returns a date for a given year, month, and day. As an option, you can provide a date formatting string.
• XDATEADD(xdate1,days,fmt): Adds a specified number of days to a date. As an option, you can provide a date formatting string.
• XDATEDIF(xdate1,xdate2): Returns the number of days between two dates.
• XDATEYEARDIF(xdate1,xdate2): Returns the number of full years between two dates (useful for calculating ages).
• XDATEYEAR(xdate1): Returns the year of a date.
• XDATEMONTH(xdate1): Returns the  month of a date.
• XDATEDAY(xdate1): returns the day of a date.
• XDATEDOW(xdate1): Returns the day of the week of a date (as an integer between 1 and 7).

These are all VBA functions.

Applications:

The XDate add-in is particularly useful for genealogists and others who need to perform simple calculations using pre-1900 dates. The figure below, for example, shows the XDATEYEARDIF function being used to calculate ages.

Requirements:

The XDate add-in requires Excel 97 or later.

Limitations:

Be careful if you use dates prior to 1752. Differences between the historical American, British, Gregorian, and Julian calendars can result in inaccurate computations.

Note:

My Power Utility Pak also includes the XDATE functions. However, they are not packaged in an add-in. Rather, you can add the functions directly to the VBA project for your workbook. As a result, you can distribute the workbook without a dependent add-in.

Documentation:

Complete context-sensitive online help is included.

Installation:

Installation is a two-step process:

1. Extract the files
   Download and execute the xdate.zip file. Extract the files into any directory.
2. Install the add-in
   Start Excel and select the Tools - Add-Ins command. In the Add-Ins dialog box, click the Browse button and locate xdate.xla (the file you extracted in Step #2). Click OK.

You can type the functions manually, or use Excel's Paste Function dialog box. To access the Paste Function dialog, click the Paste Function button, or select Insert - Function.  The XDate Functions are listed in the 'Date & Time' Category. When a function is selected in the Paste Function dialog, press F1 to read the online help.

The topic of "protecting" a spreadsheet receives a great deal of attention in the Excel newsgroups. This document provides answers to some common questions.

Excel provides three primary ways to protect information in a spreadsheet:

• Worksheet Protection
• Workbook Protection
• VB Project Protection

NOTE: This document was written prior to the release of Excel 2007.

Worksheet Protection

Questions in this section deal with protecting the contents of cells and objects on a worksheet.

How do I protect a worksheet?

Activate the worksheet to be protected, then choose Tools - Protection - Protect Sheet. You will be asked to provide a password (optional). If you do provide a password, that password will be required to unprotect the worksheet.

I tried the procedure outlined above, and it doesn't let me change any cells! I only want to protect some of the cells, not all of them.

Every cell has two key attributes: Locked and Hidden. By default, all cells are locked, but they are not hidden. Furthermore, the Locked and Hidden attributes come into play only when the worksheet is protected. In order to allow a particular cell to be changed when the worksheet is protected, you must unlock that cell.

How do I unlock a cell?

1. Select the cell or cells that you want to unlock.
2. Choose Format - Cells
3. In the Format Cells dialog box, click the Protection tab
4. Remove the checkmark from the Locked checkbox.

Remember: Locking or unlocking cells has no effect unless the worksheet is protected.

How do I hide a cell?

1. Select the cell or cells that you want to unlock.
2. Choose Format - Cells
3. In the Format Cells dialog box, click the Protection tab
4. Add a checkmark to the Hidden checkbox.

Remember: Changing the Hidden attribute of a cell has no effect unless the worksheet is protected.

I made some cells hidden and then protected the worksheet. But I can still see them. What's wrong?

When a cell's Hidden attribute is set, the cell is still visible. However, it's contents do not appear in the Formula bar. Making a cell Hidden is usually done for cells that contain formulas. When a formula cell is Hidden and the worksheet is protected, the user cannot view the formula.

I protected my worksheet, but now I can't even do simple things like sorting a range. What's wrong?

Nothing is wrong. That's the way worksheet protection works. Unless you use Excel 2002 or later.

How is worksheet protection different in Excel 2002 and later?

Excel 2002 and later provides you with a great deal more flexibility when protecting worksheets. When you protect a worksheet using Excel 2002 or later, you are given a number of options that let you specify what the user can do when the worksheet is protected:

Select locked cells	Delete columns
Select unlocked cells	Delete rows
Format cells	Sort
Format columns	Use AutoFilter
Format rows	Use PivotTable reports
Insert columns	Edit objects
Insert rows	Edit scenarios
Insert hyperlinks

Why aren't these options available in earlier versions of Excel?

Good question. Only Microsoft knows for sure. The limitations of protected worksheets have been known (and complained about) for a long time. For some reason, Microsoft never got around to addressing this problem until Excel 2002.

Can I lock cells such that only specific users can modify them?

Yes, but it requires Excel 2002 or later.

How can I find out more about the protection options available in Excel 2002 or later?

Start with Excel's Help system. If you're a VBA programmer, you may be interested in this MSDN article that discusses the Protection object.

Can I set things up so my VBA macro can make changes to Locked cells on a protected sheet?

Yes, you can write a macro that protects the worksheet, but still allows changes via macro code. The trick is to protect the sheet with the UserInterfaceOnly parameter. Here's an example:

ActiveSheet.Protect UserInterfaceOnly:=True

After this statement is executed, the worksheet is protected -- but your VBA code will still be able to make changes to locked cells and perform other operation that are not possible on a protected worksheet.

If I protect my worksheet with a password, is it really secure?

No. Don't confuse protection with security. Worksheet protection is not a security feature. Fact is, Excel uses a very simple encryption system for worksheet protection. When you protect a worksheet with a password, that password -- as well as many others -- can be used to unprotect the worksheet. Consequently, it's very easy to "break" a password-protected worksheet.

Worksheet protection is not really intended to prevent people from accessing data in a worksheet. If someone really wants to get your data, they can. If you really need to keep your data secure, Excel is not the best platform to use.

So are you saying that protecting a worksheet is pointless?

Not at all. Protecting a worksheet is useful for preventing accidental erasure of formulas. A common example is a template that contains input cells and formulas that calculate a result. Typically, the formula cells would be Locked (and maybe Hidden) the input cells would be Unlocked, and the worksheet would be protected. This helps ensure that a novice user will not accidentally delete a formula.

Are there any other reasons to protect a worksheet?

Protecting a worksheet can also facilitate data entry. When a worksheet is locked, you can use the Tab key to move among the Unlocked cells. Pressing Tab moves to the next Unlocked cell. Locked cells are skipped over.

OK, I protected my worksheet with a password. Now I can't remember the password I used.

First, keep in mind that password are case-sensitive. If you entered the password as xyzzy, it won't be unprotected if you enter XYZZY.

Here's a link to a VBA procedure that may be able to derive a password to unprotect the worksheet. This procedure has been around for a long time, and is widely available -- so I don't have any qualms about reproducing it here. The original author is not known.

If that fails, you can try one of the commercial password-breaking programs. I haven't tried any of them, so I have no recommendations.

How can I hide a worksheet so it can't be unhidden?

You can designate a sheet as "very hidden." This will keep the average user from viewing the sheet. To make a sheet very hidden, use a VBA statement such as:

Sheets("Sheet1").Visible = xlVeryHidden

A "very hidden" sheet will not appear in the list of hidden sheets, which appears when the user selects Format - Sheet - Unhide. Unhiding this sheet, however, is a trivial task for anyone who knows VBA.

Can I prevent someone from copying the cells in my worksheet and pasting them to a new worksheet?

Probably not. If someone really wants to copy data from your worksheet, they can find a way.

Workbook Protection

Questions in this section deal with protecting workbooks.

What types of workbook protection are available?

Excel provides three ways to protect a workbook:

• Require a password to open the workbook
• Prevent users from adding sheets, deleting sheets, hiding sheets, and unhiding sheets
• Prevent users from changing the size or position of windows

How can I save a workbook so a password is required to open it?

Choose File - Save As. In the Save As dialog box, click the Tools button and choose General Options to display the Save Options dialog box, in which you can specify a password to open the file. If you're using Excel 2002, you can click the Advanced button to specify encryption options (for additional security). Note: The exact procedure varies slightly if you're using an older version of Excel. Consult Excel's Help for more information.

The Save Options dialog box (described above) also has a "Password to modify" field. What's that for?

If you enter a password in this field, the user must enter the password in order to overwrite the file after making changes to it. If the password is not provided, the user can save the file, but he/she must provide a different file name.

If I require a password to open my workbook, is it secure?

It depends on the version of Excel. Password-cracking products exist. These products typically work very well with versions prior to Excel 97. But for Excel 97 and later, they typically rely on "brute force" methods. Therefore, you can improve the security of your file by using a long string of random characters as your password.

How can I prevent a user for adding or deleting sheets?

You need to protect the workbook's structure. Select Tools - Protection - Protect Workbook. In the Protect Workbook dialog box, make sure that the Structure checkbox is checked. If you specify a password, that password will be required to unprotect the workbook.

When a workbook's structure is protected, the user may not:

• Add a sheet
• Delete a sheet
• Hide a sheet
• Unhide a sheet
• Rename a sheet
• Move a sheet

How can I distribute a workbook such that it can't be copied?

You can't.

VB Project Protection

How can I prevent others from viewing or changing my VBA code?

If you use Excel 97 or later... Activate the VB Editor and select your project in the Projects window. Then choose Tools - xxxx Properties (where xxxx corresponds to your Project name). In the Project Properties dialog box, click the Protection tab. Place a checkmark next to Lock project for viewing, and enter a password (twice). Click OK, then save your file. When the file is closed and then re-opened, a password will be required to view or modify the code.

Is my add-in secure?

The type of VB Project protection used in Excel 97 and later is much more secure than in previous versions. However, several commercial password-cracking programs are available. These products seem to use "brute force" methods that rely on dictionaries of common passwords. Therefore, you can improve the security of your file by using a long string of random characters as your password.

Can I write VBA code to protect or unprotect my VB Project?

No. The VBE object model has no provisions for this -- presumably an attempt to thwart password-cracking programs. It may be possible to use the SendKeys statement, but it's not completely reliable.

A companion file is available: Click here to download

I created an add-in that displays a new CommandBar that functions as a simple calculator. It may not be the most useful tool (it's limited to only the four basic mathematical operations), and I'll be the first to admit that it's fairly ugly. But it does demonstrate some useful VBA programming techniques.

The code is too lengthy to list here, but the VBA project for the add-in file is not protected so you can examine it.

NOTE: This add-in does not work with Excel 2007.

Installation:

1. Download the Toolbar Calculator add-in
2. Start Excel and select the Tools - Add-Ins command. 
3. In the Add-Ins dialog box, click the Browse button and locate calctoolbar.xla.
4. Click OK
5. The toolbar will appear
6. Select Tools - Toolbar Calculator to toggle the toolbar on and off.

Many users are surprised to discover that Windows does not provide a direct way to get a list of file names contained in a directory. This tip describes how to create such a list in a text file, which can then be imported into Excel.

To generate a list of file names, you'll need to use a DOS command typed in a DOS command window. To open a DOS command window:

1. Click the Windows Start button
2. Click Run
3. Type "cmd" (no quotes) and press Enter. if "cmd" doesn't work, use "command".

You'll get a window like the one shown below.

Next, you need to type a DOS command to generate the file list. For example, if you would like to generate a list of all files in the root directory of drive D, type the following at the command prompt and press Enter:

dir d:\

To list the files in a particular directory, add the directory name after the drive:

dir d:\my files\

The file names will be listed in the window. Usually, you'll want these files to be sent to a file. To redirect the output to a file, use the > character and specify a file name. For example, to send the file names to a text file named filelist.txt in the root directory of drive C, use this command:

dir d:\ >c:\filelist.txt

If you would like the file list to include the files in all subdirectories of drive D, use the /s switch:

dir d:\ /s >c:\filelist.txt

The directory listing will contain lots of additional information. To get the file names only (bare format), use the /b switch:

dir d:\ /s /b >c:\filelist.txt

To find out about other options available for the DIR command (such as including file dates and times), type this command:

dir /?

After the text file is generated, you can import it into Excel by using the File - Open command.

NOTE: If you need do this on a regular basis, you may be interested in this article from Microsoft, which describes how to create a batch file that lists file names in Notepad.

This document describes the solution to several common problems involving Excel setup.

Excel crashes when it starts

When Excel is started, it opens an *.xlb file, which contains your menu and toolbar customizations. If this file is damaged, it may cause Excel to crash when it it started. Also, this file may (for some reason) be very large. In such a case, this may also cause Excel to crash. Typically, your *.xlb file should be 500K or smaller.

If Excel crashes when it is started, try deleting your *.xlb file. To do so:

1. Close Excel
2. Search your hard drive for *.xlb. The filename and location will vary.
3. Create a backup copy of this file and then delete the file.
4. Re-start Excel. Hopefully, Excel will now start up normally.

NOTE: Deleting your *.xlb file will also delete any toolbar or menu customizations.

Many documents open automatically

If Excel automatically opens lots of files at start-up, here are two things to check:

1. Your XLStart directory. Files stored in your XLStart directory are opened automatically when Excel starts. Move the files in this folder to a different folder.
2. Your Alternate startup directory. Select Tools - Options. In the Options dialog box, click the General tab. Locate the setting called At startup, open all files in. If this field is not empty, delete its contents.

Excel's menus are messed up

If your menus change, or if there is a delay before all menu items are not listed, you need to make a change.

1. Select View - Toolbars - Customize.
2. In the Customize dialog box, click the Options tab.
3. Place a checkmark next to the item labeled Always show full menus.

NOTE: This is, without a doubt, the dumbest option ever created! Why would anyone want their menus to change?

Commands are missing from the menu

If the steps in the preceding section don't solve the problem, you can reset Excel's menu bar:

1. Select View - Toolbars - Customize.
2. In the Customize dialog box, click the Toolbars tab.
3. Scroll down the Toolbars list and select Worksheet Menu Bar.
4. Click the Reset button

NOTE: This will return the menu to its default state, and destroy any menu customizations that may have been done.

Excel displays extraneous menu commands

Another common problem is extraneous menu items. For example, you may have used an add-in that added a new menu item to the Tools menu. And, for whatever reason, the add-in did not remove that menu item. To remove the menu item:

1. Select View - Toolbars - Customize
2. When the Customize dialog box is displayed, access the extraneous menu item and "drag it away." That will delete it.
3. Click OK to close the Customize dialog box.

Double-clicking an Excel file does not work

Normally, double-clicking an XLS file starts Excel and opens that file. If this doesn't work for you, you'll need to re-register Excel. Do do so:

1. Close Excel
2. Click the Windows Start button
3. Click Run, to display the Run dialog box
4. Type the following, and click OK:
   

   excel / regserver

5. You'll see a message box that display the progress. When the message box closes, Excel should be back to normal.

You get a macro warning when no macros exist

When you open a workbook, you may be prompted to enable or disable macros --even though no macros exist in the workbook. Press Alt+F11 to activate the Visual Basic Editor. Locate your workbook in the projects window:

1. If the workbook contains any VBA modules (for example, Module1), delete the module. Even an empty VBA module may trigger the macro warning.
2. Examine the code modules to ThisWorkbook, and the code module for each Sheet (for example, Sheet1). Make sure that these modules do not contain any macro code. You cannot delete these code module, but they must be empty to avoid the macro warning dialog box.

You get an erroneous "file is being edited by" message

When you open a file that is in use, you'll get a message that tells you the file must be opened in read-only mode. In some cases, you may get this message even though the file is definitely not in use. This can be caused by an Excel crash, in which the file was not released. The only way around it is to re-start Windows.

Numbers are entered with the wrong number of decimal places

For example, entering 154 appears as 1.54 in the cell. Somehow Excel's fixed-decimal mode was turned on. To return to normal:

1. Select Tools - Options to display the Options dialog box.
2. Click the Edit tab
3. Remove the checkmark from the Fixed decimal option.

Of course, this feature can be useful when entering some types of data, but most of the time, you'll want to keep the fixed-decimal mode turned off.

Numbers, not letters appear in the column header

Normally, Excel columns are labeled with letters. If they actually appear as numbers, you can change it back to the default:

1. Select Tools - Options to display the Options dialog box.
2. Click the General tab
3. Remove the checkmark from the R1C1 reference style option.