• Technology
  • CFO.com | US

Tickling the Keyboard: 10 Spreadsheet Tips

Basic shortcuts to smooth the use of Excel.

It may not be one of the seven wonders of the digital world, but for CFOs, nothing compares with Microsoft’s Excel for performing calculations, tracking a variety of business items, and making forecasts of what the future might hold in store. The problem is that the program is so complicated that few busy finance chiefs can ever use more than a handful of Excel’s many capabilities.

When things get complicated, it may be most helpful to start simply—and when it comes to computing, you can’t get much simpler than the keyboard. Following are 10 essential keyboard shortcuts that have the power to streamline how to use Excel. While the focus is on using Excel 2003, most of the shortcuts apply to older and newer versions as well. One thing is for certain: the bigger the spreadsheet, the more time the shortcuts can save.

• Saving, Printing, Opening. If you spend a lot of time opening, closing, and printing spreadsheets – and who doesn’t – the F12 key could become your best friend. On its own, the F12 key will open up the “Save As” dialog box. But add the Shift key to it, and Excel will automatically save the current sheet. Type “Control” and F12 together and Excel will provide a fresh spreadsheet.

• Formulas, the Easy Way. It may not sound like much help. But whenever you type an equal sign in an Excel cell, it will start a new calculation in the formula bar. If you create or modify a lot of spreadsheets, it’s a real time-saver. If you’re having second thoughts about the formula, just hit the ESC button or “Control” and “Z” to start over.

• Adding It All Up. Think of the F9 key as a power tool for Excel jockeys whose spreadsheets are chock full of interlocking formulas. On its own, the F9 will execute every calculation in the file. Adding the shift key will restrict the calculations to just the open sheet.

• Hide-and-Seek Cells. One technique for making a complicated sheet easier to handle is to hide non-critical cells, rows, and columns. They’re still there and work, they just don’t get in the way. It can be tedious to manually make them disappear and reappear individually. Use “Control” and the number 9 key to hide something. Don’t worry about losing what you’ve hidden: to bring it back, type “Control” and 9, followed by “(.”

• Moving between Workbooks. With nested Workbooks, although Excel can perform incredibly complicated tasks while keeping a firm’s financial data organized, it can be a confusing chore to get to the right sheet. Any time you want to go to another open page, just hit “Control” and “Tab” to cycle through the open pages. If you’ve gone too far and missed the sheet you want, try typing “Control” and “Shift” and then “Tab” to go back a page.

• Next Cell, No Waiting. Need to copy a cell’s contents to a new cell? It can be time-consuming and awkward to highlight the cell, go to the “Edit” menu at the top of the screen, select “Copy,” point the cursor at the new position, and use the “Edit” menu to paste it in place. Fortunately, Excel can quickly copy content. After highlighting the cell you want to copy, carefully move the pointer to the small black square at the cell’s lower right-hand corner. It’s called the “fill handle,” and you can drag it anywhere. The cell’s contents will go with it.

• Creating Charts. While it’s easy to use Excel’s charting wizard to format, produce, and customize a variety of graphs, its step-by-step process is tedious. This is particularly the case if you create the same charts over and over again. By hitting F11, you can instantly turn any highlighted numerical sequence into a chart.

• Cut, Copy, and Paste. The “Edit” menu selection in Excel can be used to copy, cut, and paste any cell on a sheet. Better yet, there’s a series of keys that can do it without touching the mouse. Control-C copies the cell’s contents, while Control-X cuts it. After figuring out where it should go, type Control-V and the cell’s contents are automatically pasted into the new spot. The best part is that these keyboard shortcuts work in any Microsoft Office application and are the most used.

• Selecting Cells. To grab a bunch of Excel cells, you can run the mouse over an entire region to highlight them. But that takes time, and with large spreadsheets, it’s hard to do. Let the program do the heavy work by highlighting the first cell, typing Control-Shift, and then typing the down arrow to fill in the column, or the right or left arrows to fill in rows.

• Formatting for Fun and Profit. Like other Microsoft Office products, Excel lets you specify how each character will look in minute detail. The quickest and easiest way to do this is to type Control-1 to open the formatting box or Alt-’ to get to the style box. You’re sure to find what you’re looking for in one of them. Another cool trick is to use a border to add emphasis to or highlight a section of a spreadsheet by hitting Control-Shift and “&”; “Control-Shift _” will get rid of a border. A big favorite for financial types is typing Control-Shift and “$” to automatically turn a cell or highlighted row or column into a dollars-and-cents format.

Discuss

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