Excel Data and Formula Entry Tips

Speeding up data entry

There are number of shortcuts you can use to speed up your data and formula entry in Excel.

Number keypad

Use the number keypad on the right of the keyboard. This has all the numbers, as well as most of the formula operators (+  * –  /), you need to create formulas. It also has a large Enter key. The numbers are laid out like a calculator and so are easy to use.

Excel Hyperlink on a Logo

Using images for hyperlinks

In some files there is a central sheet that you keep returning to. It might hold the inputs or the controls for the file. You can simplify getting to that sheet by using a logo as a hyperlink.

Using a logo means you can place it anywhere on another sheet. It’s also easy to copy and paste the logo on other sheets once created.

You can add a hyperlink to any image, so it doesn’t have to be a logo.

There are two easy ways to open the Insert Hyperlink dialog.

Either right-click the image and select Hyperlink – see image below.

Right click Menu

Or select the image and press Ctrl + k.

Click the Place in This Document button and select the sheet to go to. You can also link to a named range. They would be listed under Defined Names. Using a range name avoids sheet name changes that can break hyperlinks.

Iisert Hyperlink dialog

Click OK and it’s done.

Useful Shortcut – return from hyperlink

After you follow a hyperlink you can return to where you were when you clicked it by first pressing F5 and then pressing Enter.

Stay tuned for a way to create an almost unbreakable hyperlink in a future blog post.

Excel Formula Dialog Frustration Solved

Make editing easy

In many Excel dialogs when you use the arrow keys to move around the formula you actually insert cell references from the active cell in the sheet below.

This is frustrating as you usually only want to move within the formula and you might have to exit out of the dialog and start again.

Excel Alt key shortcuts

Keyboard shortcuts rule

The ALT key (there’s one on either side of your Spacebar) has some really useful shortcuts. See if they can save you some time and effort. These can be handy if, like me, you sometimes struggle with your laptop’s built-in mouse controller and you don’t have your external mouse handy. And remember most keyboard shortcuts work in all versions of Excel.