#NiftyKeyboardShortcuts: Find and Replace (CTRL + H)

Find and Replace is one of those features that gives a high-speed way of updating text, altering formulas and controlling for special cases.

The shortcut to Find is handily intuitive: CTRL + F, neatly alphabetical. The Replace tab sits right there at the top of the dialogue box, and yet it's somehow hidden in plain sight given the Replace field isn't activated by CTRL + F.

CTRL + H is what triggers both fields, Find and Replace, to open. While the variations of Find and Replace aren't within the scope of this blog, it is a mega-useful editing technique, especially where your spreadsheets have used repeated text strings.

For example, when copying a sheet to create a new financial year tab, I've needed to quickly change hundreds of references to 2015-16 to 2016-17. There's something unbelievably satisfying about seeing the following message box:

With the Find and Replace dialogue box open, don't forget to use Alt + A to replace all instances of what you're searching for. The underlined letter A indicates which letter to press following holding down the Alt key, which is a tip that you can generally apply across the Microsoft Office suite.

Remember that you can use the drop down menu to recall previously-used replacement strings so you only need to type them in once. To trigger drop down menus with the keyboard, make sure the focus is on the correct field and then use Alt + ↓ (the down arrow).

You can use this same trick when entering data directly in a spreadsheet column: it stores previous entries in rows above and constructs a drop down menu on the fly, a little like a data validation list.

In fact, unless you're running a really old version of Excel, it will guess what you're intending to type in a cell based on whether any entries above start with the same letter(s). Just hit Enter to accept the autocompletion.

One last piece of Find and Replace advice: I've gone and replaced every instance of a search term in an entire spreadsheet when I only meant to make the replacement in a particular region, column or row.

Whoops... but if that's you as well, stay calm: undo it with Ctrl + Z before selecting your search area first:

- To select an entire column, with a cell in it selected, press Ctrl + spacebar

- To select an entire row, with a cell in it selected, press Shift + spacebar

- To select an entire region (cells containing data bounded by a blank row / column), press CTRL + A

then use CTRL + H to Find and Replace.

There are other region-selection shortcuts, but that's for another time...

#find #replace #region #Alt