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
  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

  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

  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

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:

I am trying to figure out a formula to count how many matches of text there
are between two different columns.  For example, how many times does text
"N" occur in column B when text "X" also occurs in column J?  Seems like it
should be simple, buI can't come up with anything.
Thanks!!
Dale 

 

If the text you are looking for remains the same, than may I suggest a COUNTIF function. In this example I am using the word TEXT as the search criteria and looking in cells A2 TO B10. The formulae will return a value based on the type of text you plug into the formulae. If the text changes, may I suggest that you combine the COUNTIF formulae with a VLOOKUP  function so that text can be easily changed in the lookup table. Anyway, here is the formulae for the above described example.

Formulae{=COUNTIF(A2:B10,"TEXT")}

Now, this next formula requires that you do a little tinkering to match your own criteria. It will return a true, false value which the CountIf function will then take advantage of. However, at least one of the two columns you are looking at has to have something in it or the value will return true when it should be false. Is that a problem?

Here is the formula to plug in that asks the logical question where text is the appropriate answer to return a true value:

=IF(ISBLANK(A1),0,LEN(TRIM(SUBSTITUTE(A1,CHAR(10)," ")))-LEN(SUBSTITUTE(SUBSTITUTE(TRIM(A1),CHAR(10)," "),"text",""))+1)=IF(ISBLANK(B1),0,LEN(TRIM(SUBSTITUTE(B1,CHAR(10),"text")))-
LEN(SUBSTITUTE(SUBSTITUTE(TRIM(B1),CHAR(10),""),"text",""))+1)

The formula will need to advance down the column along with the data. Then the CountIf will be displayed in the part of the spreadsheet that is visible to prevent scrolling. Confused yet?

The CountIf formula is:

=COUNTIF(C1:C100,TRUE)

Again, plug in your criteria for the formular


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

Ken offers this solution:

The Problem:
Suppose you have 2 worksheets in your workbook. Worksheet 1 consists of a pivot table and Worksheet 2 is about the data source of Worksheet 1's pivot table. Since the range of data source in Worksheet 2 keeps changing regularly, you need to update Worksheet 1's pivot table manually by re-selecting the correct updated range in Worksheet 2.

What you want the data to be:
Using Excel's Macro automation method, meaning that Worksheet 1's pivot table will be automatically updated and select the correct updated range in Worksheet 2 when you open your workbook.

The Problem in a Nutshell:

You want to use a macro to import data into a spreadsheet and then convert it to a pivot table.

The data can occupy 300 rows one time, and 500 the next, and so on. The number of columns only varies.

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _ "RPR_backlog_f!R1C1:R16860C85"

The problem is from that point on the macro selects R1C1:R16860C85 every time, regardless of the number of records.

What commands shoul you use to select the entire data area each time (even when the row count increases or decreases?

Solution:
Use the concept of Dynamic Range in an Excel Macro.

ActiveWorkbook.Names.Add Name:="OnHold", RefersToR1C1:= _

"=OFFSET(RPR_backlog_f!R1C1,0,0,COUNTA(RPR_backlog_f!C1),85)"

Ken did it for the number of columns which varies. In fact, Ken writes, you can change the OFFSET syntax if the number of rows varies also.


Quick Tip: Modify Excel print margins with the mouse
Do you teach or support users who love using the mouse and would rather click than type whenever possible? If so, here's a timesaving tip for situations that Excel users face on an almost daily basis.

The problem? Your users have more data than will fit within Excel's default margin settings. When they go to File | Print Preview, their suspicions are confirmed: Only five out of the six columns are visible.

To correct this by mouse, go to File | Print Preview. If you don't see dotted lines representing margin settings, click the Margins button to display them. Mouse over any of the margin lines. When the cursor changes to a bidirectional arrow, click and drag to change the margins visually.

In many cases, you can pull that last column back onto the page by shrinking the right and left margins. Save even more paper by shrinking the top and bottom margins, cutting a three-page print range down to two.


Place a web object into word which updates data over the Internet

Question: My wife works in Protocol and Public Affairs for the US Navy. When distinguished visitors plan trips to her base, she is responsible for putting together an official itinerary for the trip. One section of the itinerary, written in Word, deals with the local weather forecast. As the itinerary goes through numerous revisions, she is constantly cutting and pasting information from sites such as weather.com for the local forecast. Is there any way to make this information some type of updatable link, so that every time the document is opened and/or saved, it gets the latest weather forecast for that specific date range? - Bob

Answer: Try doing it in MS Excel. It requires no VBA programming. Once done, paste it into MsWord. Click here for the Word instructions. Here are the steps:

  1. Open Excel.
  2. Design the document as you wish it to appear.
  3. Select the cell where you want the weather information to begin.
  4. Click Data | Get External Data | New Web Query.
  5. In the first field ("Enter the address..."), enter http://www.weather.com/outlook/travel/print/33755, replacing the 33755 with the ZIP code for the area for which you desire a forecast.
  6. In the second field ("Choose the part of the web page..."), select the option button for "Only the tables."
  7. In the third field ("Choose how much formatting..."), select the option button for "None." Click OK.
  8. In the "Returning External Data to Microsoft Excel" dialog box, confirm the location where you want the information to start appearing. Click "Properties." Maintain the selections for "Save query definition" and "Enable background refresh," and select "Refresh data on file open." Deselect "Adjust column width" and "Preserve cell formatting." Finally, select the option for "Overwrite existing cells with new data, clear unused cells." Click on OK.
  9. In the "Returning External Data to Microsoft Excel" dialog box, confirm the cell where you wish to begin inputting the information. Click OK.
  10. Once the information has been imported, you'll need to delete just a few cells containing unnecessary data.
  11. Save the spreadsheet and you're good to go. The next time the file is opened, the most current information from weather.com for the indicated ZIP code will be placed where you indicated.

Now drop the finished product into MsWord. Here is how:

  1. Highlight the section you just created in Excel
  2. Press Alt+C to copy the Excel cells
  3. Open MsWord.
  4. Place the cursor, in Word, where you want to paste the Excel Worksheet object
  5. Click Edit
  6. Click Paste Special
  7. Choose Paste Link
    • Excel Worksheet Object
    • If you want the object to float over text choose that section as seen in image to the right
  8. Click Okay

The Worksheet will update every time you open the MsWord. file, provided of course, you have an Internet connection running.

Many people do not realize that Excel can be used for all types of data. It can be reconfigured so that it looks and acts very much like a word document when printed out. I suggest that your wife use Excel to make her itinerary thus negating the MsWord. step. We are all about ease of use here and making things simple and easy. I like easy.


How to calculate how many days you have been alive.

Diane writes:

I would like to know how I can find out how many days I have been on earth if my Birthday is June 30 1985? My teacher said there is a way but I have no idea how to do it. I would appreciate it if you could let me know thank you

Well Diana, we should probably talk a little on what you need to take into consideration when making these types of calculation. So, if you are 17, I am assuming that, you are a Senior in high school.

Before you delve into Excel’s date functions, however, it helps to understand a bit about the serial numbers the program uses for this type of calculation. Excel for Windows uses the 1900 Date System, in which serial numbers range from 1 to 65,380, corresponding to Jan. 1, 1900, through Dec. 31, 2078. In this system, Jan. 1, 1900, has a serial date of 1, while Dec. 31, 2078, has a serial date of 65,380 to represent the number of days after Jan. 1, 1900. Because the program keeps track of a date or time function’s underlying serial number, it can easily perform calculations using it. For example, you can calculate how many days have transpired since you were born. I'll give you the formula further down in this email.

The Earth spins once every 24 hours which gives us the length of the day. It also orbits around the Sun once every year, which is about 365.25 days. What can trip you up? Leap year! A leap year is a year with one extra day inserted into February, the leap year is 366 days with 29 days in February as opposed to the normal 28 days. (There are a few past exceptions to this) Every year divisible by 4 is a leap year. So, 2000 (2000 divided by 4 = 500) was a leap year, 2004 (2,004 / 4 =501) will be the next leap year. In the old Julian Calendar, there was only one rule: Every year divisible by 4 is a leap year. This calendar was used before the Gregorian calendar was adopted.

Yeah, I know, that was way more information than you really wanted to know. It is important, with any calculation, to take into account certain non constant variables or even constant variables.

Based on these knowns, we can surmise the following:

You were born on: Sunday, June 30, 1985 (00:00:00 you did not specify the hour and minutes of your birth)
The calculation is based on this date: Thursday, February 6, 2003 (03:44:23 from  00 Hours.00 minutes.00 seconds)
It is 6,430 days, 3 hours, 44 minutes and 23 seconds between those dates (No hour, minutes, or seconds were specified)

Or 555565463 seconds or 9259424 minutes or 154323 hours

So Diane, you have been alive, as of Thursday, February 6, 2003 some 6,430 days.

Now for the formula:

You can use this formula (With some variances) to determine how many years, months, weeks or days old something or someone is.

  1. Open a new Excel spreadsheet
  2. Enter your birth date into cell A1
    • Example:

      Enter 06/30/1985 (With the Slashes)
  3. Now in cell A2 enter:

    =Datedif(a1,today(),"y")

    All with no spaces
  4. How many months have you been alive? Replace the "Y" with an "M".

As for days? Yep, you guessed it, replace the "Y" with a "D".


Changing some items from Upper case to lower case when using the "Proper" function with Excel

On October 24, 2002, reader Jacqueline wrote:

I sure could use your help.............I use the 'PROPER' function in Excel
to turn all caps to capital first letter only, i.e.:  'UNIVERSITY OF
MARYLAND' to 'University Of Maryland'.  How do I NOT capitalize 'and','
the', 'of'?  When I'm working on a 1200+ row list I wouldn't have to retype
the entry.

===================================================================

Create a Macro to change the Uppercase words "And and Of" to lowercase within the Proper Function.

While we can muck about with a bunch of formulas to accomplish this, they will have varied results and in some cases won't produce the results you would expect. Personally? I like simple.

Instead of using the formula session to produce the desired results leave your current "Proper Function" in place...no sense re-inventing the wheel. I suggest that you create a macro to establish this seemingly simple function using the Find and Replace feature of the edit menu tree. Below is a step by step how to record it just as though you are doing it for yourself. Alternatively, if you have several different types of characters that need changing in varying workbooks then you may want to simply use the find and replace command manually instead of creating a restrictive macro.

  1. Open you spread sheet and make a copy of it, just in the event something goes wrong.
  2. Now, click Tools | Macro | Record New Macro
  3. Give the macro an assignment, in this case I am using Test
    1. Assign your macro a shortcut key if you wish to
    2. Give your macro a distinctive name that you can remember
    3. Apply the macro to what ever location you use to store macros. You have three choices:
      1. Personal Macro Workbook for all of your macros
      2. New Workbook if you are creating a workbook to use either as a universal storage area or are creating a new workbook
      3. This workbook the macro is applied to this workbook....we will have options to store this macro as a button and menu further down in this exercise.
    4. See Figure 1 below:

      Figure 1



    5. Once finished, click okay
  4. The record macro button appears
  5. Click the upper left hand corner of the cell, to the left of A
  6. This highlights the entire sheet
  7. Now, click Edit | Replace
  8. In the dialog that appears, do the following:
    1. Type And in the Find What box
    2. In the Replace line type, and
    3. Click the Replace All button
  9. Follow step 7 above
  10. In the dialog that appears, do the following:
    1. Type Of in the Find What box
    2. Type of in the Replace With box
    3. Click the Replace All button
    4. If you are creating a macro with different words, substitute them for the ones above.
  11. Click on any cell in the spread sheet or press Ctrl+End to go to the last cell in the worksheet if this is the one you will be working on after this operation is complete. This will deselect the entire worksheet so that it is ready for you to begin inputting data.
  12. Click the stop button on the macro recorder
  13. Click Tools | Customize
  14. In the dialog which comes up click on the Commands tab
  15. In the left hand column, click on New Menu and select new Menu in the right hand pane then drag it to the top of your menu bar in your spreadsheet. Probably directly after the Help menu item is best for a customized menu item with macros.
    1. Give the new menu a name by right click ing on it and in the dialog which comes up give it a name, something like My Macros.
    2. Place the & symbol next to the letter of the new name you will want to use for a key combination. For example, if you place the & symbol next to the M in My, the M in My will look like this
      M
      y Macros,
      you will be able to press Alt+M to get to that menu item from your keyboard.
  16. Now, with the dialog still open, click on New Menu Item from the list in the right hand pane
    1. In the left hand pane click on Macros in the right hand pane select Custom Menu Item and drag it up to the new My Macros menu you have just created.
    2. Right click on the newly place item and select in the name dialog: &Replace Of and And
    3. Placing the & symbol before the Replace will allow you to choose Alt+M+R to select this command from your keyboard. See Image below:



  17. Now, with the dialog still open:
    1. Right Click on the menu item you just created, &Replace Of and And.
    2. Choose, Assign Macro from the list (See below image)



    3. The Assign Macro dialog box appears
    4. Here is where you must remember the name you gave this macro. In this example, it is Test remember?
    5. Highlight it so that it appears in the Macro Name box then click okay
  18. You are done.

From now on, to change these words from upper case which is accomplished by the Proper function, you can either click on the My Macros menu and then the Replace Of and And item and viola! All capital forms of And and Of are now lower case.

The Proper Funtion:

This tip describes a technique that should be in the arsenal of every Excel user. It describes how to use formulas to transform data.

The figure below shows a simple example. The text in column A consists of lower case letters. The goal is to transform these cells so they display "proper" case. This will be done by creating formulas that use Excel's PROPER function.

The steps below are specific to this example. But they can easily be adapted to other types of data transformations.

Creating the formulas

In this case, the formulas will go in column D. As you'll see, this is just a temporary location. The formula results will eventually replace the names in column A.

  1. Enter the following formula in cell D2:

    =PROPER(A2)
  2. Copy the formula down the column to accommodate the data. In this case, the formula is copied down to cell D11. The worksheet now looks like this (the formula cells are selected, so they appear highlighted).

Copying and pasting the formula cells

In this step, the formula cells are copied, and pasted as values -- overwriting the original data in column A.

  1. Select the formula cells. In this case, D2:D11.
  2. Choose Edit - Copy
  3. Select the first cell in the original data column (in this case, cell A2).
  4. Choose Edit - Paste Special. This displays the Paste Special dialog box.
  5. In the Paste Special dialog box, click the Value option button. This step is critical. It pastes the results of the formulas -- not the formulas.
  6. Click OK.

At this point, the worksheet looks like this:

Deleting the temporary formulas

The formulas in column D are no longer necessary, so you can delete them.


Q. How can I search for an asterisk, question mark, or tilde?

A. Since these characters have special meanings in Excel, you will need to precede them with a tilde to be able to search for them successfully. So, to find an asterisk, first click Find on the Edit menu, and then type "˜*" in the Find and Replace dialog box; to find a tilde, type "˜˜"; a question mark, "˜?".


Q. 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?

A. Yes. Here's how you do it:

  • First, select all the sheets you want to copy the settings to. To do this, either press CTRL and click each sheet tab, or right-click a sheet tab and then click Select All Sheets on the shortcut menu. At this point, you should see the [Group] in the title of the workbook.
  • Then, make sure the active sheet is the one with the correct settings, and click Page Setup on the File menu. Then click OK.

You're done! Now that you've finished copying your settings, be sure to ungroup the sheets. You can easily do this by right-clicking the sheet tab and selecting Ungroup Sheets from the shortcut menu.


Q. 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?

A. If a workbook has a module sheet created in Microsoft Visual Basic® for Applications (VBA)—even if there is nothing written on it—Excel thinks there are macros present. To find this module, open VBA by pressing ALT+F11, and then press CTRL+R to see the Project window. At this point, you should see a module, likely named "Module1." Right-click that and select Remove Module1.


Q. How do I add times together?

A. The same way you add any other value: =A1+A2. For example, if you have a time of 1:45 in cell A1 and 2:15 in A2, you'll see 4:00 as the total when you add the times together.

However, if the values add up to more than 24 hours, you won't see what you expect. If you added 8:00, 9:00, and 10:00, you'd see 3:00. Why? Well, because Excel is showing you the result in 24-hour notation (8 + 9 + 10 = 27 hours). To see a result in total hours, all you need to do is reformat the result. Instead of the h:mm format, use [h]:mm. The brackets around the "h" prevent the result from displaying in 24-hour notation. You can apply brackets to "m" as well. For example, 27 hours formatted as [m] will show a result of 1,620, the number of minutes in 27 hours (27 × 60 = 1,620).


Q. I have "lastname, firstname" listed in one cell. How can I place these data in separate cells?

A. There are two basic ways: one that won't change if the original data changes and one that will change whenever the original data changes. Here's how:

To preserve the original data:

  • Select the data, and click Text to Columns on the Data menu.
  • When the Convert Text to Columns Wizard appears, select Delimited and click Next.
  • In step two of the wizard, select the Comma check box and click Next.
  • In step three, in the Destination box, specify where you want the results to start.
  • Click Finish.

To have the data change whenever the original data changes:

  1. Click a different cell from the one where "lastname, firstname" appears, and enter
    =LEFT(A1,FIND(",",A1)-1). This will return the last name.
  2. In another cell, enter =MID(A1,FIND(",",A1)+2,255). This will return the first name.

Q. Is it possible to have a product name enter automatically whenever I enter a product number?

A. Yes. You can use the VLOOKUP function to do this. Suppose the product number is on Sheet2, cells D2:D50, and the product name appears in E2:E50. Assuming the cell where you've entered the product number is A25, enter this: =VLOOKUP(A25,Sheet2!$D$2:$E$50,2,FALSE). The function searches the cell range D2:E50 in the first column. The "2" in the formula means the function will return the value from the second column. "FALSE" specifies that the function return the value only if it finds an exact match of whatever is in cell A25.


Q. Why does Excel say my file has links when I know it doesn't?

A. Excel has links that can be contained in hard-to-find places: in a defined name, on a button, on an object, inside a formula, and so on. To see examples, visit this Excel User Tips page. For technical details, read the Banish Phantom Links article. Or, if you want to download a utility to fix it, try one created by a Microsoft MVP on the Excel MVP site.


Q. 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?

A. Yes. On the Tools menu, click Options. When the Options dialog box opens, click the Edit tab and then clear the Fixed decimal check box.


Q. 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?

A. Somewhere along the line, you clicked New Window on the Window menu. You can merge the files back into one by clicking the × in the upper right corner of one of the workbooks. Or, just press CTRL+F4.

There are benefits to using the New Window feature. It enables you to see different views of the same document at the same time: one workbook with gridlines, one without; two remote ranges (such as A1:D4 and G500:J515); and so on. You can use the New Window feature to view three, four, or more windows of the same document.


Q. 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?

A. If the row you want to keep in view is row 1:

  1. Select all of row 2, either by clicking the row number or by clicking anywhere in row 2 and pressing SHIFT+SPACEBAR.
  2. On the Window menu, click Freeze Panes.

If your titles take up two rows, select row 3 and follow the same steps. You can also lock columns by clicking a column letter, and then clicking Freeze Panes on the Window menu. To freeze both row and column, click the cell just below and to the right of the row and column you want to freeze; then click Freeze Panes on the Window menu.

Reversing these changes is simple. Just click Unfreeze Panes on the Window menu.

 

Entertainment Government Technology

About

Email Me