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
Excel Macro to select full data area... when data area varies
Counting
And Highlighting Values
Works with Excel 97 and above
Using
the Nested IF Function
Using
the CountIf Function
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 popup window.
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.
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 nonblank
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:
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 nonblank 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.
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 dropdown list. In the last box, enter
=$B$1
4. Choose the Format button, select the Patterns tab, and click on the small brightyellow 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 twocolumn 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 dropdown, 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 pulldown 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).
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 (nonoutlined) 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.
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:
Press Windows Key + R to bring up the run dialog
Type:
excel /safe
If Excel starts in safe mode, then you can go through the administrative parts of the program to try and make necessary changes.
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;ENUS;158658
Here are some more things to try:
1) Start Excel in Safe Mode. In this mode, Excel does not open addins 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) Reregister Excel with Windows. This command instructs Excel to rewrite all of its "factory default" information to the Windows system registry and reestablish links among the various components of Excel. To reregister 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 AddIns. If you are able to start Excel in Safe Mode, as described above, you should systematically disable addins to determine if an addin is causing your problem. Go to the Tools menu, and choose AddIns. Make a note of which addins are checked, and then uncheck all addins. Close and restart Excel normally. If Excel starts normally, then the cause of the problem was an addin. To determine which addin caused the problem, go to the Tools menu, choose AddIns, and check one of the addins that you previously unchecked. Close and restart Excel. Repeat this procedure for each addin. When the problem recurs, you will have found the troublesome addin. You can either leave the addin 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".
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 doubleclick 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)
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: What you want the data to be: 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: 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 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. 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:
Now drop the finished product into MsWord. Here is how:
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) 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.
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:
=================================================================== 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 reinventing 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.
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: 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 formulasIn 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.
Copying and pasting the formula cellsIn this step, the formula cells are copied, and pasted as values  overwriting the original data in column A.
At this point, the worksheet looks like this:
Deleting the temporary formulasThe 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:
You're done! Now that you've finished copying your settings, be sure to ungroup the sheets. You can easily do this by rightclicking 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." Rightclick 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 24hour 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 24hour 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:
To have the data change whenever the original data changes:
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 hardtofind 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:
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. 
