10 incredibly useful Excel keyboard tips

Here are 10 ways to make Microsoft Excel work harder for you, without taking your fingers off the keyboard.

Microsoft Excel has a host of keyboard shortcuts that you can use to speed your work.

10 incredibly useful Excel keyboard shortcuts
Peter Sayer/IDG

1. 10 incredibly useful Excel keyboard shortcuts

Excel is a powerful tool, but like many powerful tools, its user interface has become cluttered with layers of menus, ribbons and multi-tabbed dialog boxes. Getting to the feature you want can feel like navigating one of those new urban developments designed to prevent traffic cutting through – tiresome unless you know the hidden shortcut.

Or, in Excel's case, the keyboard shortcut.

Here are 10 of our favorites to help you navigate and edit your spreadsheet at speed, without lifting your fingers from the keyboard.

View all the formulas in an Excel worksheet at once
Peter Sayer/IDG

2. View all the formulas in an Excel worksheet at once

The default display mode in Excel shows you the results of formulas, so a column of sales figures ends in your total sales for the month, not a truncated formula beginning =SUM(D2:...

If you want to check your work, you can always click on a cell to see the formula behind the cell's value; it will be displayed in the formula bar, between the ribbon and the worksheet.

If you have a lot of cells to check, that's going to take you a while. But there is a way to see all the formulas in a worksheet at once: You can toggle the display between formulas and values by hitting Ctrl+` – that's a backtick, the symbol found to the left of the 1 key on U.S. keyboards.

This trick is particularly useful if you're auditing a shared spreadsheet, for example, to see why some calculated cells in one column are not updating when you change the inputs in another. It may be that someone has inadvertently replaced one of the formulas with a value they calculated by hand. You can also find this on the Formulas tab of the Ribbon.

Quickly draw a border around a cell in Excel
Peter Sayer/IDG

3. How to quickly draw a border around a cell in Excel

Sometimes you want to draw a box around a result to call attention to it – but Excel offers so many different border styles, it's easy to become sidetracked. Thick lines? Double lines? Ooh, dotted lines! Or maybe dashes? Dot-dashes? Dot-dot-dashes? What about the color? A standard one? Or a themed set?

And that's without considering whether you want the same border on all sides of your box, or something different across the top or along the bottom. Before you realize it, you can lose half your morning on irrelevant esthetics. Surely there's a simpler, distraction-free way of applying a border.

To draw a quick, simple border around the current cell (or cells) just hit Ctrl+Shift+7. You won't notice the change until you select another cell.

Quickly remove borders around cells in Excel
Peter Sayer/IDG

4. How to quickly remove borders around cells in Excel

Oops: You (or a colleague) succumbed to the temptations of the Format Cells - Border dialog box (see previous slide), with its cornucopia of line styles, colors, orientations and spacings, and your worksheet now looks like a Mondrian-Jackson Pollock mash-up.

No worries: To quickly remove borders around cells in Excel, just select the cells you'd like to revert to their pristine, borderless condition and hit Ctrl+Shift+- (minus).

There. Told you it was quick.

Easily flip a column of data to a row
Peter Sayer/IDG

5. How to easily flip a column of data to a row in Excel

You distractedly type in a whole series of labels, then realize that you meant them to go across a row, and not down a column. Rather than cut and paste them one at a time to their correct position, why not flip the column of data through 90 degrees using the Paste Special/Transpose option?

Start by selecting the column of labels or other data you want to transpose. You can click and drag or, if the column is very long, try this: Select the first cell in the column, hold down the shift key, and double-click on the lower border of the selected cell: Excel will extend the selection downward until it encounters an empty cell. Copy the selection, then select the leftmost of the cells where you would like the transposed labels to appear.

Now, for the magic: Call up the Paste Special dialog with Ctrl+Alt+V (or command+control+V in macOS); hit E to select the Transpose option, bottom right, then Enter. As long as the source and destination areas don't overlap, you should see your cell entries spread across the sheet rather than down it.

Excel will only let you perform this trick using Copy, not Cut, so to delete the data from its original position, click once again in the first cell of the column, hold Shift and double-click the lower border of the cell to extend the selection, then hit Ctrl+Delete (just delete in macOS) to empty the cells.

Note: This trick also works the other way, for transposing a horizontal block of cells into a vertical one.

Quickly select the current row or column in Excel
Peter Sayer/IDG

6. How to quickly select the current row or column in Excel

This is a quick one. Did you know you can extend the selection from one cell to an entire row by hitting Shift+spacebar? Or from one cell to an entire column by hitting Ctrl+spacebar? (This one is control+spacebar in macOS too, as Command+spacebar is the system-wide shortcut for Spotlight search.)

If you have cells in several adjacent rows selected, Shift+spacebar selects the entirety of each of those rows, while Ctrl+spacebar will select all of the columns in which you have adjacent cells selected.

Curious minds will want to know: What happens if you do both simultaneously? Hitting Ctrl+Shift+spacebar will extend the selection to form a rectangle encompassing all contiguous non-empty cells adjacent to the currently selection.

If any of the cells in the current selection are empty, then Ctrl+Shift+spacebar will select the entire sheet. This is a little like clicking on the triangle at the top-left of the sheet, with the difference being that it leaves the current cell selection unchanged, while clicking the triangle makes A1 the current cell.

Format any object in Excel
Peter Sayer/IDG

7. How to format any object in Excel

You selected something in Excel – a cell, a drawing, or a chart – and want to reformat it. But what or where is the command to do that? There are so many different formatting functions in Excel it can be hard to find the right one.

Happily there is a shortcut for this: Whatever you have selected, Ctrl+1 (command+1 in macOS) will find and open the appropriate formatting dialog. It works whatever the selection: cells or groups of cells; drawings, or even parts of charts such as legends or axes.

Get help building a formula in Excel
Peter Sayer/IDG

8. How to get help building a formula in Excel

If there's one thing I learned at college, it's that there's no shame in looking it up. You don't have to know all the answers, but it helps to learn where to find them.

So it is with building formulas in Excel: There are so many functions, with so many parameters, that it's nigh-on impossible to remember them all. Happily, you don't have to.

Excel already provides a modicum of help, listing the parameters to a function when you type the opening parenthesis following its name. But that's not always enough of a clue. At that point, hitting Ctrl+A in Excel for Windows brings up a dialog box, Function Arguments, that will walk you through building the formula, providing a definition for each parameter in turn. On Excel for macOS, the same control+A shortcut will bring up the more expansive Formula Builder sidebar, where you can build the formula and see all the definitions at once.

Let's take the example of one of Excel's powerful yet perplexing financial tools, AMORLINC, which when given five or six parameters returns the prorated linear depreciation of an asset for each accounting period.

As you begin your formula by typing =AMORLINC( Excel responds by filling in placeholder parameter names after the opening parenthesis: cost; date_purchased; first_period; salvage; period; rate; [basis] -- the square brackets indicating an optional parameter. But what do they mean? Hit Ctrl-A and you'll learn that first_period needs to be a date, not a number, and that salvage is the salvage value at the end of life of the asset.

quickly format numbers in Excel
Peter Sayer/IDG

9. How to quickly format numbers in Excel

OK, this isn't a single keyboard shortcut, it's a whole family of them, but they all work on the same principle – and they're all in the row across the top of your keyboard, which makes them really handy if you want to change the presentation of your spreadsheet without picking up your mouse. Say hello to the number formatting shortcuts:

Ctrl+Shift+~ (control+shift+~ on macOS): general format (displays all digits of a number if there is room in the cell, switching to scientific format if not)

Ctrl+Shift+! (control+shift+! on macOS): number format, usually set by default to display two decimal places (ie 10 is displayed as 10.00)

Ctrl+Shift+@ (control+shift+@ on macOS): time format (mnemonic: AT what time does it happen?)

Ctrl+Shift+# (control+shift+# on macOS): date format (mnemonic: looks like a calendar grid)

Ctrl+Shift+$ (control+shift+$ on macOS): currency format

Ctrl+Shift+% (control+shift+% on macOS): percentage format

Ctrl+Shift+^ (control+shift+^ on macOS): scientific format (mnemonic: exponents are small numbers raised from the baseline, often indicated by a ^, eg 10^2 for 10 squared, and scientific notation represents very large or small numbers as a number between 1 and 10, multiplied by a power or exponent of 10.

Make Excel for macOS work like Excel for Windows
Peter Sayer/IDG

10. How to make Excel for macOS work like Excel for Windows

Windows users don't know how lucky they are, being able to navigate all the elements of a dialog box, including buttons and checkboxes, without a mouse by tabbing from one to another. You can't do that on macOS, where tabbing just moves you from one text input field to another.

Or rather, you can't do that on macOS by default. There is a way to turn on that Windows-like behavior – and there's even a keyboard shortcut for it. It's not part of Excel, though: It's part of macOS.

The long way to do it is to go to the Apple menu and select System Preferences... > Keyboard, click on the Shortcuts tab and then, at the bottom of the dialog box under "Full Keyboard Access" click on the button next to "All controls."

The short way is just to hit control+Fn+F7 (or perhaps just control+F7, depending on the way you have function keys set up on your Mac).

Now you can navigate dialog boxes without having to pick up your mouse or touch the trackpad. And if that becomes too confusing, just hit control+Fn+F7 again put things back the way they were.

Summing up shortcuts in Excel
Peter Sayer/IDG

11. Summing up shortcuts in Excel

To sum up this tour of useful keyboard shortcuts, let's conclude with one for what is probably the most commonly used Excel function: summing up a column.

Select a range of cells in a column then hit Alt+= (macOS: command+shift+T), and Excel will insert a formula beneath them summing the range. For example, if you select cells B2 through B20, it will insert =SUM(B2:B20) into cell B21.

If you select a range of cells in a single row -- B2 through F2, say, then the shortcut promptes Excel to sum horizontally, inserting =SUM(B2:F2) into cell G2.

Excel looks down on rectangular selections that extend across multiple rows and columns. Selecting the cells B2 through B20 and C2 through C20 and applying the shortcut will insert =SUM(B2:B20) in cell B21, and =SUM(C2:C20) in cell C21, but no horizontal sums will be calculated.

Copyright © 2017 IDG Communications, Inc.