Excel Page 6
Welcome to Blaisdell's Little Corner of the Web

MsOffice Index 1 | MsOffice Index 1I | Microsoft Mac Office 8.0 | Microsoft PowerPoint 97 | Microsoft Office XP | Microsoft Bug of the Month | Microsoft Macro Virus from MacOffice |      Updated 11/14/05

Page Index

1. Changing some items from Upper case to lower case when using the "Proper" function with Excel
   • Create a Macro to change the Uppercase words "And and Of" to lowercase within the Proper Function.
2. How can I search for an asterisk, question mark, or tilde?
3. How to calculate how many days you have been alive.
4. I've set up the page headings just as I want, but they only appear on one worksheet. Is there a way to copy these settings to other sheets?
5. When I open a file, it asks me if I want to enable or disable a macro. There are no macros in the workbook. What's going on?
6. How do I add times together?
7. I have "lastname, firstname" listed in one cell. How can I place these data in separate cells?
8. I want to lock in my title row so that it remains visible while I scroll down to see the rest of my data. Any ideas?
9. Recently, a file I've been using has begun opening two copies every time I open it. Then, if I close one, they both close. What's going on?
10. When I type a number (like 44), Excel displays it as 0.44, even if the cell is formatted not to show decimals. Can I fix this?
11. Why does Excel say my file has links when I know it doesn't?
12. Is it possible to have a product name enter automatically whenever I enter a product number?
13. Place a web object into word which updates data over the internet
14. Quick Tip: Modify Excel print margins with the mouse

15. Excel Macro to select full data area... when data area varies

16. Excel 2000 files won't open 

17. Excel 2000 will not load

18. CONSOLIDATION

    • Consolidation Clarification

19. Custom Formats For Validation

20. Counting And Highlighting Values
    Works with Excel 97 and above

21. Using the IF Function

22. Using the CountIf Function

    • Count text matches in two different columns using COUNTIF

23. Using the Nested IF Function
    Using the CountIf Function

24. Shortcut To Delete Rows Or Columns

Shortcut To Delete Rows Or Columns
Works in Excel 97 and above

Here's a keyboard shortcut for deleting rows and columns in Excel without having to use the menus.

Select the row(s) or column(s) you want to delete, then press Ctrl + - (Ctrl key plus the minus key). The minus key can be the one on the numeric keypad or on the top row of keys (just above the letters and below the function keys) on standard keyboards.

To select a row or column, click on the grey box at the far left of the row (containing the row number) or the grey box at the top of the column (containing the column letter). Alternatively, you can select a row or column from the keyboard. Simply move to a cell in the row or column you want to select and press Shift + Spacebar (to select the row) or Ctrl + Spacebar (to select the column). If you want to extend the selection of rows or columns, be sure to release the Ctrl key, then press and hold the Shift key and use the arrow keys (up arrow, right arrow, etc.) to select adjacent rows or columns.

To delete the row or column you can also move to a cell in the row or column you want to delete, press Ctrl + - and select "Entire row" or "Entire column" from the Delete pop-up window.

Using the IF Function

The Excel If function is a powerful tool that can be used when the information you want in a cell is conditional. Its particularly handy if you need to specify two or more different responses for a cell based on conditions you specify.

The Excel If function is extremely versatile, however its often seen as quite a confusing function to use. Once you learn how to use Excel's IF function, you'll wonder what you ever did without it!

Microsoft explains the syntax of the Excel IF function:

=IF(logical_test,value_if_true,value_if_false)

What that means, simply put, is this:

=IF("if the condition stated here is true", "then enter this value", "else enter this value")

A practical example would be a spreadsheet containing exam marks for students.

Perhaps you wanted to see at a glance who had failed and who had passed. An IF function would be perfect for this. Let's assume that the pass mark is 50 and the marks are in column B, starting from row 4. Our formula would look like this:

=IF(B4>50, "Pass", "Fail")

So, this formula says if the data in cell B4 is greater than 50, then put "Pass" in the cell this formula is in, otherwise put "Fail" in the cell. You can then simply copy this formula for the entire list of names - Excel will change the cell reference for you.

The Excel IF function is an important one to understand. It can be used to great effect by itself, as a nested IF function or in conjunction with other functions such as ISERROR. The Excel IF function can be nested up to 7 times to make quite complex formulas.

Using the CountIf Function

• COUNTIF(range,criteria)
• Countif Basics
• Countif Criteria
• Word Values And Countif
• Using Wildcards With The Countif Function
• Using Countif Statements In With Mathematical Operations
  • If you use the formula:
• Vote Counting
• When True Isn't True

The CountIf function is a conditional formula, like the If function. CountIf is used when you only want to count cells in a row or column that will satisfy a particular condition.

The COUNTIF worksheet function in Microsoft Excel has a simple format that provides a quick and easy way to count the number of times a certain condition is met in a data set.

That sounds imposing but it isn't.  Most likely you already use COUNT to count the number of cells that have numbers or COUNTA to count the non-blank cells in a worksheet.  COUNTIF simply adds the ability to put additional conditions on what is counted in the cell range, which is easier and faster than trying to create a filter on results then count them.

While COUNTIF is quite simple, it can be used to create more complex statements that provide you a great deal of control over what is returned in your dataset.

For example, a spreadsheet that shows how many members of staff are absent on each day of the month. You may need to know on how many days there were no absenses. The CountIf function is the perfect solution for this problem. This is the official microsoft definition of the CountIf function:

=COUNTIF(range,criteria)

So, from the example, what we are looking for is all the cells in a range where "0" occurs.

If the values are in the range A1 to A31, our function would look like this:

=COUNTIF(A1:A31, "0")

This basically says count all the cells in this range only if data in the cells equals 0. The criteria doesn't have to be numerical, you can also use words or phrases. 

Countif Basics
The basic syntax of the COUNTIF function is =COUNTIF(range, criteria).  Your criteria, if text or conditional - both examples we'll look at a bit later - must be in double quotes.  The COUNTIF worksheet function is easy to understand from an example so let's take a look at a simple example using numerical entries.

       A
   1   5
   2   7.5
   3   5
   4   5
   5   7.5

To find out the number of times that 5 is used in this range of data, you would use the formula =COUNTIF(A1:A5,5).  The formula will return a value of 3.  Likewise, using =COUNTIF(A1:A5,7.5) you will get a value of 2.  There's no need to put an equals sign in to make a valid condition.

Countif Criteria
The "criteria" part of the COUNTIF syntax allows you to count items that meet a condition such as greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), or not equal to (<>).  Using the example above, the formula =COUNTIF(A1:A5,">5") will return a value of 2.  Notice that the criteria in this case is in double quotes.

TIP:  Double quotes must be used for any criteria other than a single number.

The COUNTIF function can also be used to count text entries when text entries are enclosed in quotes.  Let's consider another example.

       A
   1   cat
   2   cow
   3   dog
   4   cat
   5   cat

To total the number of instances that cat appears in the range from A1 to A5, use the formula =COUNTIF(A1:A5,"cat").  This formula returns a value of 3.

Word Values And Countif
The COUNTIF function does not need to be used to count whole words.  Let's say for instance that you have a long listing of names and you want to find out how many names start with something from B on down to Z.  In this case, you will use a comparison operator like those described earlier for numbers.  Again, an example makes this concept easiest to see.  Let's take a look at this example:

       A
   1   a
   2   ab
   3   abc
   4   b
   5   bc
   6   bcd
   7   c
   8   cd
   9   cde
  10  e

Using the formula =COUNTIF(A1:A10,">b") would return a value of 6 since lines 5 through 10 all contain values that are 'greater than' the letter b.  The comparison selection does not need to be an item in your list.  For instance, you could use the formula =COUNTIF(A1:A10,"<g") to return a value of 10 since all items in the list are 'less than' g.

Comparing words or other combinations of letters is simply an alphabetical comparison.  For example if comparing the words cat and cow, cow is greater than cat because cow would come after cat alphabetically.  In this same way, the word cats is greater than cat since it has an additional letter.  It's also important to note that upper and lowercase letters are treated exactly the same so the COUNTIF function will view the words cat, CAT and Cat as being exactly the same.

Criteria can also be made up of other functions such as the TODAY() function which returns the current date.  Functions must be concatenated, or added to, the comparison operator using the ampersand (&) character.  For example, if you have a list of dates and want to find out how many come after the current date, you would use =COUNTIF(A1:A10,">" & TODAY())

TIP:  Functions have to be outside the quotes. Use the & sign to join them into a condition.

Using Wildcards With The Countif Function
The COUNTIF function can be used with two different wildcard characters - the asterisk (*) and the question mark (?).  The asterisk is used to search for anything regardless of the number of characters in the response.  For example, if you wanted to search the list shown below for any cell containing a letter b, you would use the function = COUNTIF(A1:A10,"*b*").  The use of asterisk both before and after the letter b tells Excel to search for the letter b and to ignore what is both in front of it and behind it.  You should get a result of 5 from this formula, since 5 cells contain the letter b.  You might also consider searching for only those entries that begin with the letters ab using the formula =COUNTIF(A1:A10,"ab*").  This returns a result of 2 since both the values in cell A2 and A3 begin with ab.

       A
   1   a
   2   ab
   3   abc
   4   b
   5   bc
   6   bcd
   7   c
   8   cd
   9   cde
  10   e

The question mark (?) provides a much more specific search.  The ? substitutes for one character.  A search criteria can include multiple question marks to indicate multiple missing letters.  For example, given the list of names below, you could use the formula =COUNTIF(A1:A4,"wil???") to return the value of 2 for both Willis and Wilson (each has six characters and begin with wil.  This contrasts with the use of the asterisks after the criteria "wil" which would give a result of 4 since all 4 values begin with the letters wil.  The question mark provides a great deal of control when using the COUNTIF function.

       A
   1   Wills
   2   Willis
   3   Wilson
   4   Wilsons

Using Countif Statements In With Mathematical Operations
You can use a COUNTIF statement as part of a mathematical expression.  Consider the following set of numbers:

       A
   1   1
   2   1
   3   2
   4   3
   5   5
   6   8
   7   13
   8   21

If you use the formula:
=COUNTIF(A1:A8,">2")+COUNTIF(A1:A8,"<13"), you are adding together the number of values greater than 2 (5) with the number of values less than 13 (6) which will return a total result of 11.  Again, any type of criteria - words, letters, numbers, dates, etc. - can be used in these statements.

Vote Counting
One example in the Excel help is a good use of COUNTIF.  You can use it to calculate the percentage results in a simple poll.  Assuming the results are in an Excel worksheet in a range of B2:B99 then the formula to count the 'Aye' votes  is  COUNTIF(B2:B99, "Aye") and the 'Nay' votes COUNTIF(B2:B99, "Nay").

To turn those raw numbers into a percentage just divide by the total number of votes and make the cell formatting a percentage.   COUNTIF(B2:B99, "Aye") / COUNTA(B2:B99).

Microsoft uses a more convoluted option to count the non-blank cells ROWS(B2:B99) - COUNTIF("<>"&"*") which only shows there are more than one way to skin an Excel cat.

When True Isn't True
The strange thing about COUNTIF is that it doesn't seem to obey the normal logic rules that would allow integration of a wider range of functions.

For example  COUNTIF(A1:A8, "TRUE") should return 8 since the condition is always met but you get 0 instead.  COUNTIF(A1:A8, TRUE) doesn't work either.

That's the way it is but a pity because COUNTIF could have had greater potential.

Using the Nested IF Function

Nested IF functions are a simple way to do a complex conditional formula. Nested IF functions allow you to use up to seven IF functions to impose conditions.

Essentially, the way a nested if statement would work is like this:

=IF("if this condition stated here is true", then enter "this value, else if("if this condition stated here is true", then enter "this value, else enter"this value"))

It looks quite long and imposing, but its really quite simple once you understand the concept. To illustrate, let's say you have a spreadsheet that you use to keep track of your sales force. The rate of commission each sales person receives is based on the amount of sales they have generated for that month. For example:

From $1 to $100 earns 10% commission From $11 to $100 earns 15% commission Anything over $100 earns 20% commission

Assuming the amount of sales is in column B, starting at row 4, and that the column containing the commission is formated for percentages, this is what the nested IF function would look like:

=IF(B4<=10,"10", if(b4<=100, "15", "20"))

This nested IF function says that if the cell B4 is less than or equal to 10, then put "10" in this cell (the commission), if the cell B4 is greater than 10, but less than or equal to 100, then put 15 in this cell.

If the number in cell B4 is greater than 100, then put 20 in this cell.

This simplifies data entry for the spreadsheet as you now only need enter the amount of sales and the commission percentage is worked out for you. It also means there is less chance of making errors on the commission when entering the data.

Counting And Highlighting Values
Works with Excel 97 and above

We have been getting a large number of requests for this type of function. Sense it seems to be an important issue for many of our readers, we provide it here. As always, change the functions to suit your own particular needs.

If you have a long list of values and want to know if a particular value exists in the list, you can use the Edit/Find command to search through the cells.

If your list contains a smaller set of known values, you may want to take a different approach. For example, suppose you have a list of sales for your company. In the first column is the sales agent's last name. You want to know how many times that agent's name appears in the list, if at all. While the Edit/Find command can tell you if the value (agent's name) exists (appears in the list), it doesn't count the number of occurrences. Furthermore, it does nothing to spotlight all the occurrences.

We'll use an array formula to count the occurrences, then use a conditional format to highlight the matching values.

Counting Occurrences:

Lets assume we have a list of agents in cells A4 through A14

Adams
Baker
Carson
Adams
Dover
Emerson
Baker
Carson
Dunhill
Carson
Dundee

Here's how to count the occurrences:

1. In cell A1 enter:

   Agent to find:

2. In cell A2 enter:

   Number of times found:

3. Widen column A so the text is fully contained in the cell.

4. You'll enter the agent value to find in cell B1.  

5. Move to cell B2 to enter the formula shown below. After entering the formula, press Shift + Ctrl + Enter so Excel will recognize the formula as an array formula. (If you don't, Excel returns an error.) Here's the formula:

=SUM(IF(B1=A4:A14,1,0))

6. To test your formula, enter Baker (capitalization doesn't matter -- you can enter the name in all lowercase) in cell B1. Press Enter or click on another cell. Cell B2 should now show the value 2.

Note: If you know that column A will contain nothing but Agent names, and you expect to have fewer than, say, 1000 entries, you can enter a larger range, such as

=SUM(IF(B1=A4:A1003,1,0))

In this way, as you extend the range (add lines of data), you don't have to adjust the array formula in cell B2 as the data range grows.

Highlighting The Matches:

We've looked at how to use conditional formatting in past issues (Premium Edition subscribers, check our Back Issue index of Excel articles for "Conditional Formatting"); we're using the same technique we've used in the past to make the background yellow for all agents matching the value entered in cell B1.

Here's how:

1. Select the cells that will contain the agent names. In our small example, select A4 through A14.

2. Use the Format/Conditional Formatting command from the main menu.

3. For Condition 1, choose the cell condition "Cell value is" and pick "equal to" in the next drop-down list. In the last box, enter

=$B$1

4. Choose the Format button, select the Patterns tab, and click on the small bright-yellow box in the Color section.

5. Click on OK, then on OK again.

Enter "Baker" (without the quotes) in cell B1 and press Enter (or move to another cell); cells containing Baker in the range A4:A14 will now be displayed with a yellow background. 

Minimizing Input:

If you know that by entering the first few characters of an agent's name you'll have entered enough to find a unique match, you can shorten the number of keystrokes you have to enter in cell B1.

For example, suppose you know that entering Bak is enough to make for a unique match (Baker, in our sample data).

Change the array formula (in Step 5 of "Counting Occurrences" above) to check for two conditions -- the agent's name must be greater than or equal to the text entered, but cannot be greater than the text plus a string of Z's added to the end.

=SUM(IF(B1<=A4:A14,IF(B1&"ZZZZZZ">=A4:A14,1,0),0))

That is, if you enter "bak" (without the quotation marks) in cell B1, we want to count the agent names that are greater than or equal to bak but less than or equal to bakZZZZZZ.

Note: the number of Z's isn't important. You can just as safely use only two or three. I like to use several so they stand out.

For the conditional formatting, choose to format the cell based on a value BETWEEN two values. For the first value, enter =$B$1; the in second value, enter

=$B$1&"ZZZZZZ"

To further test the formulas, enter the letter "d" in cell B1. Three cells should be highlighted: Dover, Dunhill, and Dundee.

Custom Formats For Validation
Works with Excel 2000 and above

Typically, custom formats are broken down into three parts: a format for positive numbers, a format for negative numbers, and a format for cells that evaluate to zero. The format, such as:

   #,##0.00;[Red](#,##0.00);"--"

tells Excel to display a value using commas and two decimal digits for positive values, display a value in red (and with added parentheses) for negative values, and display two dashes if the value in the cell is zero.

However, we can override the positive/negative/zero breakdown. For example, suppose you have a data range where cell values must be between 0 and 10. If the value is less than 0, you want the cell to display "TOO SMALL". If the value is greater than 10, you want to see "TOO BIG".

To create this custom format:

1. Select the range of cells to format.
2. Use the Format/Cells command from the main menu.
3. In the Format Cells dialog box, choose the Number tab.  In the Categories list choose Custom.
4. Click inside the Type field and enter the custom format.  In our example, enter:

   [>10]"TOO BIG";[<0]"TOO SMALL"

5. Click on OK.

Now, cells in the range assigned this custom format will display
"TOO BIG" if they contain a value over 10.
 
You can add more conditions to the custom format. For example, to display TOO BIG or TOO SMALL in red, change the custom format string in step 4 to:

   [red][>10]"TOO BIG";[red][<0]"TOO SMALL"

QUICK SUMMARIES WITH CONSOLIDATION
Works with Excel 97 and above

Many times you have to work with lists of data, wishing that you could somehow summarize the data quickly. For example,  if had a two-column list: the first column contained a salesperson's name and the second column contained the amount of the sale. and you had a simple question: how much did each salesperson (agent) sell? then your ripe for consolidation.

Your first thought might be to sort the list by salesperson, then use the subtotal feature. That works, though it is a little tedious. It also means that you have to collapse outlines (adding subtotals adds outlines), when your really only looking for a total, not individual details. It isn't difficult to collapse the outlines, of course, but it is just more work. What you really wanted was a simple list with the salesperson name in the first column and total amount sold in the second column.

Try using the Consolidation feature. Using the Consolidate by Category steps I'll explain below, you'll be able to come up with just such a summary.

For this discussion, let's assume the following worksheet (cell range is A1 through B9):

Agent   Sales
  Adams    100
  Baker    200
  Carson   300
  Delta    400
  Ellison  100
  Adams    200
  Baker    100
  Ferman   500

To summarize the total sales by agent:

1. Move to the cell address where you want the summary to appear. In our example, move to D1.
2. Use the Data/Consolidate command from the main menu. Excel displays the Consolidate dialog box (see Figure 1 in our online edition).
3. In the Function drop-down, select Sum.
4. In the Reference box, enter the cell range, including heading rows and columns. In our example, enter A1:B9.
5. In the "Use labels in" box, be sure both "Top row" and "Left column" are checked.
6. Click on OK.

Your new consolidated cell range (beginning in cell D1) now displays the results (see Figure 2 online if the following list is difficult to read):

Sales
  Adams   300
  Baker   300
  Carson  300
  Delta   400
  Ellison 100
  Ferman  500

VARIATIONS

There are several variations to this technique. For example, you can count the number of entries rather than sum the values by choosing Count in the Function pull-down menu (step 3 above).

You can consolidate data from several different cell ranges. For example, your data could be in A1:B9 on Sheet1 and more data could be in cells C3 through D100 of Sheet2. In place of step 4 above, enter the first cell range (including sheet name in this example) in the Reference box, then click on Add. Excel puts the reference into the "All references" section. Enter the next cell range, click on Add, and repeat until all references are entered. Then proceed to step 5 above.

You can also consolidate data from cell ranges in different workbooks. Again, the key is to enter the complete cell range(s) in the Reference field. Also, be sure the files are open when you perform the consolidation -- Excel won't open the files for you automatically.

TIP: If you repeatedly summarize data with the consolidation feature, you may find it helpful to assign range names to the areas you want to consolidate. Then, instead of entering cell addresses in Step 4 above, simply enter range names, once at a time, until all are selected.

Excel's behavior is worth noting. Once consolidation is complete, changing a source values does NOT update the calculations for the consolidated values. You must repeat the consolidation steps to recalculate the summary values (such as the total sale amount).

Consolidation Clarification:

Excel's behavior is worth noting. Once consolidation is complete, changing a source values does NOT update the calculations for the consolidated values. You must repeat the consolidation steps to recalculate the summary values (such as the total sale amount).

However; There is an option to check "create links to source data" and the consolidation will update. 

The option (a checkbox) is part of the Consolidation dialog box. We should have explained, however, that the consolidated cell range cannot be placed on the same worksheet as the original data, which may or may not be an important limitation for you. Otherwise, it works as advertised: change a value in the original data range and the consolidated range sum (or count or whatever function you chose) is updated.

But beware -- Excel's behavior is also slightly different when you use this option. Excel creates an Outline; in our example (with the option off, we placed the consolidated range on the same page) the result is a regular (non-outlined) range of cells.

Furthermore, with the option checked you can't insert a row within the original data range -- doing so will not include the new value in the consolidated results.

You can delete a row in the original source range, but the cell value (for instance, the agent name in last week's example) will still appear in the consolidated results. Try creating a consolidated range on Sheet2 using last week's data, then delete agent Delta's entire row. You'll see the consolidated range still has a row devoted to Delta, though the summary value is now zero.

Excel 2000 will not load

Reader Mark writes:
I have removed Office 2000 and reinstaalled it. I have also attempted the repair feature but noting that I do will get my Excel program back. Is there something that I am missing?

Most likely one or more of the Excel files were corrupted. Removing Office 2000, in most cases, doesn't remove all files associated with O2K and very few of the Registry Keys. Microsoft has acknowledged that this is a problem but they have also placed a removal tool on their website which, they claim, will completely remove the remaining bits of Office left behind.

Before removing the installation, first try the safe mode switch for excel. Here is how:

1. Press Windows Key + R to bring up the run dialog

2. Type:
   excel /safe

3. If Excel starts in safe mode, then you can go through the administrative parts of the program to try and make necessary changes.

4. Once done, exit Excel and reboot the system then try running Excel 2000 normally.

Here is the tool for O2K:
OFF2000: Utility to Completely Remove Remaining Office CD1 Files and Registry Entries http://support.microsoft.com/?kbid=239938

Here is the tool for 97:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;158658

Here are some more things to try:

1) Start Excel in Safe Mode.  In this mode, Excel does not open add-ins or startup files, or command bar and customizations. To start Excel in Safe Mode, go to the Windows Start menu, choose Run, and enter the following:

          Excel.exe /Safe

If you get a message indicating that Excel.exe cannot be found, you need to enter the entire folder directory path to Excel, enclosed in quotes.  Enter something like the following. Your actual path may vary depending on your installation.

          "C:\Program Files\Microsoft Office\Office\Excel.exe" /Safe

If Excel starts in Safe Mode, you can continue with the steps below to further diagnose and fix the problems.

2) Re-register Excel with Windows.  This command instructs Excel to rewrite all of its "factory default" information to the Windows system registry and re-establish links among the various components of Excel. To re-register Excel, go to the Windows Start menu, choose Run, and enter the following:

          Excel.exe  /RegServer

If you get a message indicating that Excel.exe cannot be found, you need to enter the entire folder directory path to Excel, enclosed in quotes.  Enter something like the following. Your actual path may vary depending on your installation.

          "C:\Program Files\Microsoft Office\Office\Excel.exe"  /RegServer

Excel will start, write its keys to the registry, and then quit.  Now try starting Excel normally.

3) Start Excel without customized toolbars and menus.  A frequent cause of problems is a bloated or corrupt customization file.  Search your hard drive for *.xlb to locate the XLB files used by Excel. Be sure to change your search options to look for hidden files and folders.  XLB files are used by Excel to store your customized menus and toolbars. Their names and locations will vary depending on your version of Excel and Windows.  When you search for and find these files, move them to another location, such as the Desktop.  Now start Excel normally.

4) Start Excel without Add-Ins.  If you are able to start Excel in Safe Mode, as described above, you should systematically disable add-ins to determine if an add-in is causing your problem.  Go to the Tools menu, and choose Add-Ins. Make a note of which add-ins are checked, and then uncheck all add-ins. Close and restart Excel normally. If Excel starts normally, then the cause of the problem was an add-in.  To determine which add-in caused the problem, go to the Tools menu, choose Add-Ins, and check one of the add-ins that you previously unchecked. Close and restart Excel. Repeat this procedure for each add-in.  When the problem recurs, you will have found the troublesome add-in.  You can either leave the add-in unchecked and not use it, or you can contact the supplier for further advice or an updated version.

5) Start Excel without Startup Files.  Search your hard drive for folders named XLStart. When Excel starts, it opens all the files in these folders.  Find these folders (you may have more than one, depending on your version of Excel and Windows), and move the files in them to another location, such as the Desktop. Start Excel normally.  If Excel starts, one of the files in the XLStart folders is the problem.  Add the files back to the folder, one at a time, starting and closing Excel between each file, until the problem recurs. When the problem recurs, you have found the file that is causing the problem. Remove this file from the XLStart folder.

5) Run Detect And Repair.  Excel 2000 and later version have a Detect And Repair tool that can help restore Excel to a working version.  This will replace missing or damaged files that Excel needs to operate properly.  From the Help menu, choose Detect And Repair.  You may need your Office installation CD.

6) Delete Files From Temp Folder.  When the Windows Temp directory becomes cluttered with too many files, Excel may have problems starting up, or may start very slowly.  Open the C:\Windows\Temp folder and delete the contents. Also delete files from the Temp folder in "C:\Documents And Settings\Your User Name\Local Settings\Temp".

Excel 2000 files won't open 

Morgan writes:

My Excel files will not open when double clicking on them, and I can not open Excel 2000 at all. I have tried a repair of Office 2000 but to no avail

Answer: How to reinstate Excel's ability to open files with spaces in their name/path by double clicking:

1. Have Excel correct it

1. Choose start (Start button), Run, type:
"C:\Program Files\Microsoft Office\Office\EXCEL.EXE" /Regserver
(the quotes are mandatory!)
2. Click OK

Do it yourself

1. Open Windows Explorer.
2. Choose from its menu: View, Options. or View | Folder 
Options in Windows 98 or Tools | Folder Options in XP
3. Select the File Types tab
4. Scroll to Microsoft Excel Worksheet 
5. Click Edit
6. Select the OPEN entry (it should be highlighted (bolded)
7. Click Edit and check that the fields are set as follows:

a) Application used: should be EXCEL.EXE, loaded from the correct directory
b) Use DDE should be checked
c) The DDE message should be (exactly): [Open("%1")]
d) Application should be: excel
e) DDE Application not running should be empty
f) Topic should be: system
8. Click OK
9. Click Close

10. If you wish to double-click other Excel file types, e.g. template (xlt) or workspace (xlw), select the appropriate type in 
the File Types tab and repeat from step (5)

Reader Dale writes: