Row Height Cannot AutoFit

To set the row height to fit its content, usually one would double click on the line between that row and the row below it. However, at times it works and at times it doesn't.

Here is why:
Actually the row height does fit the content neatly. The unfitting row is actually an illusion. It is in fact related to the amount of zoom applied on the worksheet. Set the zoom to 100% and the height should fit just nicely to the content.

Short Cut Keys

For those who love the keyboard and hate the tiresome mouse clicks, here are some keystrokes worth remembering. They'll be a great time saver to your work.

A. To Move to...
1. First cell (top left) of working region: Ctrl+Home
2. Last cell (bottom right) of working region: Ctrl+End
3. First cell in a row: Home
4. First occupied cell in a row: Ctrl+LeftArrow
5. Last occupied cell in a row: Ctrl+RightArrow
6. Next worksheet: Ctrl+PageDown
7. Previous worksheet: Ctrl+PageUp

B. To Select...
1. Current region: Ctrl+* (Ctrl+Shift+8)
2. Entire worksheet: Ctrl+A
3. A contiguous block of cells (cells to select are just next to each other)
- Select the first cell of the block
- Hold down the Shift button and select the last cell of the block
4. Non-contiguous block of cells (cells to select may not be next to each other)
- Select any cells of the block
- Hold down the Ctrl button and select the next cell
- Repeat step ii until all cells are selected
5. A contiguous block of worksheets (worksheets to select are just next to each other)
- Select the first worksheet of the block
- Hold down the Shift button and select the last worksheet of the block
6. Non-contiguous block of worksheets (worksheets to select may not be next to each other)
- Select any worksheet of the block
- Hold down the Ctrl button and select the next worksheet
- Repeat step ii until all worksheets are selected

C. Formatting...
1. Cell: Ctrl+1

D. Editing...
1. Undo last action: Ctrl+Z
2. Redo last action: Ctrl+Y
3. Fill a selected group of cells with the same value/formula: Ctrl+Enter
4. Change the same range of cells on different worksheets to the same value/formula:
- Select all the worksheets to edit (see Section B3 to B6)
- Enter the value/formula
- Press Ctrl+Enter

Stay tune for more short cut keys...

Some Nerdy Tips

>

Evaluation for Conditional Formatting

Evaluating a condition in a cell and applying a format accordingly maybe seem to be rather straight forward. But somethimes thing may prove to be a little tricky.

Suppose column A contains a list of Expiry Dates which you want to keep track, and conditional formatting is applied to the whole column which includes blank cells.

Conditions:
1. If the date today is earlier than the expiry date then the cell is White.
2. If the date today is equal to the expiry date then the cell is Yellow.
3. If the date today is later the expiry date then the cell is Red.

Since these three conditions are mutally exclusive, what you would enter as the evaluation formula will probably look like this:
Condition 1: =A1=TODAY( )
Condition 2: =A1<TODAY( )

Result:
The result that you will get may be a little unexpected. When the date today is earlier, equal or later than the expiry date, the cell will be White, Yellow and Red respectively. However, the rest of the remaining blank cells in the column will turn Red too. This is because blank cells will be evaluated to TRUE with A1<TODAY( ).

There is actually a fourth condition, that is, a cell can be a blank cell.

Therefore:
An extra condition to evaluate whether a cell is empty has to be added.

Condition 1: =A1="", No change to format
Condition 2: =A1=TODAY( )
Condition 3: =A1<TODAY( )

Take note that A1="" must be placed before <TODAY( ) so that empty cells will remain White.