Press Enter and Stay in Current Cell

A trick to stay in the cell you are editing is to hold the Ctrl key down when you press Enter.

You Can Undo After You Save

I am amazed how few people know this.

Way back in Office 2007 Microsoft changed the Undo List so that it is NOT cleared whenever you save a file.

You can use Ctrl + z or the Undo icon to undo things you did before you saved the file.

If you close the file that obviously clears the Undo List.

Please let people know this as I find so many people in my training sessions do not know things have changed since Office 2003.

This applies to all MS Office apps.

Clear Borders In Excel

If you need to clear all the borders from a selected range use

Ctrl + Shift + _ (underline)

Instant Format in Excel

You may know the two keyboard shortcuts below for currency and percentage.

But what you may NOT know is a technique that has been around since the early versions of Excel.

The technique allows you to automatically apply these two formats after you type an entry.

It you type $1000 into a cell and press Enter. Excel will automatically apply the $ format to the cell. The $ sign will not display in the Formula Bar – see below.

If you type 2.5% into a cell. Excel will automatically apply the standard % format to the cell. The % sign will display in the Formula Bar – see below.

As I mentioned these are really old skills that have been lost over the years since we no longer have Excel manuals – shows my age.

Range Selection Tip and Trick

You know how when you press Enter you usually select the cell below? You can override that without changing a single setting.

When you select a range Excel can behave differently when you press the Enter key. Not many users know this trick.

Select the range B2:G2 in a blank sheet and press Enter. The cell selected will be the one on the right or back at the start of the range depending which cell was active when the range was selected.

Pressing Enter cycles through all the cells in the range.

This works for two dimensional ranges as well – in that case the cell below is selected until the bottom of the range is reached then the top of the next column within the range is selected.

This is handy for entering data into input ranges.

Try This

Let’s say range A2:D2 has basic links referencing the cell above so cell A2 has =A1 in it.

What if you wanted to change all those relative references to fixed references?

Select the range A2:D2 and then press these three keys in sequence

F2 F4 Enter

Repeat three times – job done!

F2 is the Edit command.

F4 converts a relative reference into a fixed reference.

Enter accepts the change.

You can often achieve very fast changes with keyboard techniques like this.

For example if a cell contains an email address  or a web address but it isn’t recognised as a link, simply select the cell and press F2 then press Enter to convert it into a link.

If there is a column of them, just keeping pressing F2 and Enter to convert them all. You can become quite fast.