Excel : Pesky hyperlinks

I hate the fact that Excel automatically changes a URL to a hyperlink when you click on it or want to cut and paste.

To get round this abysmal behaviour (I have Excel 2003):

Tools / AutoCorrect Options / "AutoFormat as you type" / Uncheck "Internet and network paths with hyperlinks".

Another way is to simply type an apostrophe in front of the URL.

If you already have the damn things, you can right click and "Remove Hyperlink".

For multiple ones (as per the Help file):

Type the number 1 in a blank cell, and right-click the cell.
Click Copy on the shortcut menu.
While pressing CTRL, select each hyperlink you want to deactivate.

No more damn hyperlinks!

Enjoy!

Excel : Putting the row number in the cell

Comparing two versions of a spreadsheet (old versus new), I wanted to have a column of the current row number on the old spreadsheet and the corresponding row number on the new one.

For the old spreadsheet, this would just be the row number e.g. column 3 has number 3.

An easy way to do this is to type two numbers in the first two rows (i.e. 1 and 2) and then highlight them as if to drag them which causes the little box to appear in the bottom right hand corner of the cell. Then drag the box down the rest of the column.

When you do this, Excel works out the relationship between the two numbers (i.e. row x = x; row x+1 = x+1) and then applies this to the rest of the column.

Done.

Enjoy!

Excel : Viewing two windows side by side

I have two spreadsheets; one an earlier version and one that reflects the current state after many alterations and updates and I wanted to document all the changes.

So I tried to put them side by side so I could manually compare them row-by-row.

There are two buttons on the task bar (one for each window) but clicking them loads each spreadsheet in exactly the same window. They effectively overlay each other.

After doing some research, I found you need to click Window / Arrange / Vertical and hey presto it's done.

Enjoy!

Excel: "Pass" / "Fail" in a column and "Fail" in red

There is a pivot table spreadsheet on the openSTA site which allows you to work with timers. I wanted to compare that with a required result and then place "Pass" or "Fail" in the column depending on the result and then have "Fail" in red to highlight it.

You could of course use VBA but that requires a bit of programming skill.

The fist part is easy - just use the formula:

=IF(H1>K1,"Fail","Pass")

where H1 is the openSTA timer result and K1 is the required result.

The second part is done via selecting the "Pass" / "Fail" column and then right-click / Format / Conditional Formatting.

The condition is "Cell value is equal to "Fail"", then click on the "Format" button and select the red colour.

Enjoy!