Alternate Row Shading Using Conditional Formatting

Have you ever had a spreadsheet so large that you are awash in a sea of rows and columns and can easily lose your place?  A way to make your spreadsheet a little more legible is to apply cell shading to every other row in a range.  This is a piece of cake with Excel's Conditional Formatting feature. 

1. Select the range that you want to format
2. Choose FORMAT, CONDITIONAL FORMATTING from the pull-down menus
3. In the CONDITIONAL FORMATTING dialog box, select FORMULA IS from the pull-down list, and enter this formula:             =MOD(ROW(),2)=0*
4. Click on Format button, select the Patterns tab, and specify a color you would like for the shaded rows (light yellow works great for printing in black and white because the color doesn't print out too dark).
5. Click OK twice.

The coolest part is that the row shading is dynamic, so if you insert or delete rows within the original range, the row shading adjusts and you still get the results you wanted.

Shortcut to Inserting the Current Date (MS Excel and MS Access)

To insert the current date into an Excel document, place your cursor where you want the date to appear.  Hold down the CTRL key then click on the semicolon key (;).  This hint also works in Access.  This date will not update automatically, however.  You must use the TODAY function instead if you need the date to update each time you open the document.  To do so:

1. Select the cell where you want to insert the date
2. Choose INSERT, FUNCTION from the pull-down menus
3. In the PASTE FUNCTION dialog box, select ALL from the FUNCTION CATEGORY list and then TODAY from the FUNCTION NAMES list After you make your selection, you may get a warning pop-up box telling you that the formula result is "volatile."  As scary as that sounds, it means that the cell formula is dynamic and will update to the current date each time you open the file.

Shortcut to Change Types of Cell References in your Formulas  (And an Explanation of the Various Types of Cell References in a Nutshell)

To change cell addresses, after you type the cell reference, press the F4 key.  You can also select the cell reference in the formula bar and then press F4 key.  This will toggle you through the four types of cell references listed below.

A1    Relative cell reference (the default) will adjust and change when copied, moved, or used with AutoFill.  Both column and row are relative, meaning they change when the cell address is copied, etc.

$A1   Absolute cell reference that allows the row reference to change (relative), but not the column reference (absolute).  Note the $  (dollar sign) in front of the column, this is your clue that this part of the cell reference is absolute.

A$1   Absolute cell reference that allows the column reference to change (relative), but not the row reference (absolute).  Here is the $ (dollar sign) again letting us know that this part of the cell reference is absolute.

$A$1  Absolute cell reference that allows neither the column nor the row reference to change. Both column and row are absolute, meaning the reference will refer only to that cell.