You are here: Home / Archives for August 2008

How to automatically save a backup copy of a workbook

August 30, 2008 By admin

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:

drive:\Documents and Settings\user_name\Application Data\Microsoft\Excel
2. The file is saved with an extension of .xar and it has an arbitrary name that looks like this: ~ar7770.xar
3. “Application Data” is a hidden folder, and if you want to see it then you have to modify the folder options to show hidden files and folders.

Applies to: Excel 2003

Filed Under: General Tagged With: Backup, Recovery, Save

How to count blank or empty cells in a range

August 26, 2008 By admin

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.

Filed Under: Data, Formulas, Functions Tagged With: COUNTBLANK, Functions

How to count cells that contain numbers or dates

August 24, 2008 By admin

To count cells, within a given range or list of arguments, that contains only numbers you use the COUNT function.

  • Dates and text that represent numbers are considered numbers and will therefore be counted.
  • Logical values, text, or error values are not counted

Syntax

COUNT(value1,value2,…)

Examples:

COUNT(A1:A20) :counts numbers in a range of cells.
COUNT(A1:A20, 7) :counts numbers in a range of cells plus the second argument (7) which is counted as 1.

In the simple example depicted by the image below, cell B7 contains the function: =COUNT(B1:B6). The result is 4. as you can see the cells that contain the numbers 1, 2 and 17 and the cell that contains the date are counted. The the cells that contain the text “ABC” and “XYZ” are ignored.

Applies to: Excel 2003

Filed Under: Data, Date and Time, Functions Tagged With: Count, Data, Functions

How to change worksheet tab color

August 20, 2008 By admin

Do you really need to change the tab color of a worksheet?

Well, you may have a workbook with a considerable number of worksheets, and you may just want one or two of the worksheets to stand out from the crowd. Anyhow for any reason you may have here is how to do it:

1. Switch to the worksheet for which you want to change the tab color.

2. Click Format, point to sheet then click Tab color.

tabcolor

3. The Format tab color window will open. Select the color you want then click OK.

4. The tab color of the selected sheet will be changed.

Tip:

You can also right-click on the sheet tab and then click Tab Color.

Applies to: Excel 2003

Filed Under: Customization, Formatting Tagged With: Color, Customization, Formatting

How to set a background for a worksheet

August 17, 2008 By admin

To add a background to a worksheet follow these steps:

1. Switch to the worksheet where you want to add a background.

2. Click Format, point to sheet then click Background.

3. The Sheet Background window will open. Select your picture then click Insert.

4. The selected picture will be repeated as a background.

To remove the background:

1. Switch to the worksheet where the background is shown.

2. Click Format, point to sheet then click Delete Background.

Applies to: Excel 2003

Filed Under: Customization, Formatting Tagged With: Background, Customization, Formatting

How to Find cells that have data validation

August 10, 2008 By admin

If you want to find cells in your worksheet that have data validation, then follow these steps:

1. On the Edit menu, click Go To. The Go To window will open.

2. Click Special. The Go To Special window will open.

gotospecialv

3. Click Data validation.
4. At the bottom of the window click on the radio button that says All.

All cells with data validation will be highlighted.

To find cells that match certain data validation settings then you follow similar steps with slight variations:

1. Click the cell that has the data validation settings for which you want to find matches.
2. On the Edit menu, click Go To. The Go To window will open.
3. Click Special. The Go To Special window will open.
4. Click Data validation.
5. At the bottom of the window click on the radio button that says Same.

All cells with matching data validation will be highlighted.

Filed Under: Data, Validation Tagged With: Data, Find, Go To, Validation

How to find cells that have conditional formats

August 3, 2008 By admin

If you want to find cells that have conditional formatting, then you have two choices:

1. Find cells with any conditional formatting.

2. Find cells that have conditional formatting settings identical to the settings of a specific cell.

For option 1 do the following:

1. On the Edit menu, click Go To. The Go To window will open

GoTo

2. Click Special. The Go To Special window will open.

gotospecial

3. Click Conditional formats.

4. At the bottom of the window click on the radio button that says All.

All cells with conditional formatting will be highlighted.

For option 2 do the following:

1. Click the specific cell to compare to.
2. On the Edit menu, click Go To. The Go To window will open
3. Click Special. The Go To Special window will open.
4. Click Conditional formats.

5. At the bottom of the window click on the radio button that says Same.

All cells with conditional formatting that matches the selected cell will be highlighted.

Filed Under: Data, Formatting Tagged With: Conditional Formating, Find, Go To