There are numerous keyboard and mouse shortcuts to navigate around your spreadsheet and file.
There are number of shortcuts you can use to speed up your data and formula entry in Excel.
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.
When you copy a formula in Excel, any relative references (those without dollar signs) may change depending on where you paste the formula. If you would like to copy a formula and not have the relative references change you have two options.
Macros can really improve your productivity in Excel. When you record a macro you have the option to define a shortcut key. Did you know you can also define a shortcut key for non-recorded macros?
The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
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.
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.
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.
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.
When creating a drop down selection you usually want to restrict the user to certain entries. There are cases however when you want to allow the user to choose an option or allow them to type in their own entry.
Entering today’s date in Excel can be done a couple of ways. It depends on whether you want the cell to always show the current day’s date or whether you want to capture today’s date so that it doesn’t change.
Here are some more of my favourite keyboard shortcuts that use the Ctrl key. Many people prefer to use the keyboard rather than the mouse. These shortcuts can reduce your dependence on the mouse.
Keyboard shortcuts can really speed up your work in Excel. Here are some of my favourites that use the Ctrl key. I’ll share some more in later blog posts.
Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden.
Ever tried using your right mouse button to click, hold and drag things around in Excel? You might be pleasantly surprised with the results.
Excel has a feature that can save you a lot of typing, it’s called AutoCorrect. Yes it’s the same system that fixes your spelling mistakes. Well did you know it has two handy features?
Right clicking in Excel offers many productivity benefits. If you don’t already right click try it, it’s fun to see all the options that are just a click away.
Excel’s Go To feature provides a quick way to select certain types of cells. For example, if you wanted to apply the same fill colour to all formula cells on a sheet, you can do that in five easy steps using Go To.
Using the SHIFT key in Excel can speed up range selection and make other tasks easier. Some of these tips will also work in Word and PowerPoint.
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.
The F4 function key in Excel has a dual personality.
When used normally it will repeat your last action. This is useful for formatting, deleting rows and other operations that require a few keystrokes. Pressing F4 can reduce the keystrokes. F4 can also repeat running a macro.
The shortcut is
Alt + Enter
To insert a line break within a cell hold down the Alt key (next to your space bar) and press Enter. This inserts a fixed line break within the cell.