Excel help for the rest of us
To view multiple worksheets at the same time follow these steps:
1. Open your workbook.
2. In the Window menu click New Window.
3. In the Window menu click Arrange and select an option (Tiled, horizontal .. etc). Now you will have two windows showing the same worksheet.
4. Click on any one of the two windows and select a different worksheet. Now each one of your windows will point to a different worksheet, and you can have as many windows as you want.
Applies to Excel 2003
If you look at many of Excel lookup functions you will find out that they either return a value or a position of an item. There is no function that returns the address where a lookup value is found.
Below is a code for a user defined function that takes two arguments: a lookup value and a range, and returns the address where the lookup value is found within the range. If the lookup value is not found, it will return the text “#N/A”.
You can download the function as a text from the Download Page.
If you need more help on how to create a user defined function click here.
Function LookupAddress(LookupVal As String, TheRange As Range)
Dim Cell As Range, Found As Boolean
For Each Cell In TheRange.Cells
If Cell.Value = LookupVal Then
LookupAddress = Cell.Address
Found = True
Exit For
End If
Next Cell
If Not Found Then LookupAddress = “#N/A”
End Function
Applies to Excel 2003
Counts the number of cells that contain numbers within a given range. It can also accept a list of arguments other than cell ranges and counts numbers within that list of arguments. Use COUNT to find out how many cells or arguments that have numeric values.
Syntax
COUNT(value1,value2,…)
Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
Syntax
COUNTA(value1,value2,…)
Counts empty cells in a specified range of cells.
Syntax
COUNTBLANK(range)
Counts the number of cells within a range that meet the given criteria.
The criteria can be a number, text, expression or a cell reference.
Syntax
COUNTIF(range,criteria)
Applies to Excel 2003
To count numbers greater than or less than a given number, you use the COUNTIF function.
As asn example if you have a list of numbers in a range of cells B1:B20 and you want to know how many of those cells have a value greater than 100, then you use COUNTIF as follows:
COUNTIF(B1:B20,”>100″).
On the other hand if you want to know how many of those cells have a negative value , then you use COUNTIF as follows:
COUNTIF(B1:B20,”<0″).
Read more about the COUNTIF function here.
Applies to Excel 2003
The COUNTIF function counts the number of cells within a given range that meet a given criteria.
Syntax
COUNTIF(range,criteria)
Range: The range of cells from which you want to count cells.
Criteria: The criteria upon which you want to base your count. It defines which cells will be counted.
The criteria can b a number, expression, cell reference, or a text.
Examples:
=COUNTIF(B15:B33;”>100″)
=COUNTIF(A1:A8;”ABC”)
The examples below show how to use COUNTIF with numeric values. A1 to A6 is our data range. In rows 7, 8 and 9 you have the text of the formula in column A (for explanation only), the actual formula in column B and column C shows which cells are qualified to be counted.
COUNTIF and wildcards
You can use the wildcard characters question mark (?) and asterisk (*) in the COUNTIF criteria. A question mark matches any single character; an asterisk matches any sequence of characters.
Examples:
The examples below show how to use COUNTIF with wildcards. A1 to A11 is our data range. In rows 12, 13, 14, 15 and 16 you have the text of the formula in column A (for explanation only), the actual formula in column B, and column C shows which cells are qualified to be counted.
Note:
If you want to find an actual question mark or asterisk, type a tilde (~) before the character.
To count cells, within a given range or list of arguments, that contains data, you use the COUNTA function.
Data in this context includes error values, spaces and empty text (“”).
Syntax
COUNTA(value1,value2,…)
Examples:
COUNTA(A1:A20) :counts any nonblank cells in a range of cells.
COUNTA(A1:A20, 7,”ABC”) :counts nonblank in a range of cells plus the second argument (7), and the third argument “ABC”.
In the example below, cell B7 contains the function: =COUNTA(B1:B9). The result is 6. Here the non blank cells are B1, B3, B4, B6, B7 (formula that returns an empty text) and B9 (contains spaces).
Applies to: Excel 2003
How to search for a text in a formula or a comment
It may be interesting to know that the Find option in Excel Edit menu is not limited to finding cell values, but it can also be used to search for text in a formula or a comment.
To search for a text in a formula follow these steps.
1. In the Edit menu click Find.
2. The Find and Replace window will be shown.
3. There are three combo boxes that are labeled “Within”, “Search” and “Look in”. If those combo boxes are not shown then click on the Options command button to expand the “Find and Replace” window.
4. Click on “Look in” combo box and select Formulas.
5. Click on the Find All command button.
6. All cells that have formulas, and that contain the text searched for will be listed at the bottom of the window.
Notes:
1. You can search for text in a comment in a similar way by changing your selection in the “Look in” combo box.
2. You can specify to search within one worksheet or the entire workbook, depending on your selection in the “Within” combo box..
Applies to Excel 2003