Excel Digest

If you have a column of data that contains duplicates, then you can use the advanced filter option to identify and copy the unique values to a different location. You can then easily count those values using COUNTA function or the ROWS function.

Perform the following steps to achieve this goal:

1. First make sure that your column has a header row.

2. Select your data range (including the header row).

3. On the Data menu point to Filter, then click Advanced Filter. The Advanced Filter window will open.

4. Click Copy to another location.

5. The List range text box should have your data range. If not then click on it, go to your worksheet and select the range.

6. Click on the Copy to text box. Go to your sheet and click on a blank column (or just a single cell) where you want your unique vales to be copied.

7. Select the Unique records only check box and click OK.

8. To count the unique values just copied, use the COUNTA function or the ROWS function. E.g if the unique values are in the range E1:E6 then in a separate cell enter one of the following formulas:

1. “=COUNTA(E2:E6)”

2. “=ROWS(E2:E6)”

Applies to Excel 2003

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

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

COUNT

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,…)

Read more …

COUNTA

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,…)

Read more …

COUNTBLANK

Counts empty cells in a specified range of cells.

Syntax

COUNTBLANK(range)

Read more …

COUNTIF

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)

Read more …

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

Excel has a feature called AutoRecover that allows you to save a workbook automatically at a preset time interval. Every time a file is saved, a backup copy will first be saved using the previously saved copy of the workbook, and the current information will be saved to the original workbook. Follow these steps to enable AutoRecover.

1. On the Tools menu, click Options.
2. Click on the Save tab.
3. Select the Save AutoRecover info every check box.
4. In the minutes box, type or select a number to specify the interval for how often you want to save files.

This way if there is a system failure, or Excel stops responding, the recovery file will be available the next time you restart Excel.

When you start Excel after a previous failure, you will see a document recovery pane at the left side of the screen and your original and recovered files will be listed there. You will then have the choice to select which one you want to keep.

Notes:

1. The default location for the recovery file is as follows:

Applies to: Excel 2003

To count blank or empty cells within a given range you use the COUNTBLANK function.

• Cells with formulas that return “” (empty text) are counted.
• Cells with zero values are not counted.
• Cells with spaces are not counted.

Syntax

COUNT(range)

Examples:

COUNTBLANK(A1:A20)

In the example below, cell B12 contains the function: =COUNTBLANK(B1:B11). The result is 5. This is a result of counting cells B2, B5, B6, B8 (contains a formula that returns an empty text) and B10.

Cell B11 is not counted because it contains spaces.