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.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *