If words like "grids," "cells," "data," and "formula" crawl under your skin like a bad case of the heebie-jeebies, chances are you've been a victim of Microsoft Excel. A bad experience with Excel can haunt you in the same way that the smell of a certain alcohol can turn your stomach years after the last time you overindulged.
Microsoft Excel, one of the foundational programs in Microsoft Office, is a wickedly powerful program – and it’s "wicked" in that it can do dozens upon dozens of time-saving stunts that most people, unfortunately, never learn. What's more, when you do learn a new trick in Excel, you can forget it within days if you don't practice it. It's a matter of use-it-or-lose-it.
So following on from our Microsoft Word 2010 tips yesterday, we’ve got some handy tricks for those of you using Excel 2010. You might want to bookmark this page so you can refer back to the instructions and helpful screenshots on a future day when your skills are getting rusty.
These tips cover a range of essential topics for those less familiar with the program, and also some will help refresh the memory of more experienced Excel users, too.
Some examples of what's covered in this list of tips are: How to customise Excel's default workbook, how to toggle between results and the formula that create them, and how to alphabetise or sort (i.e. "filter") a spreadsheet based on a single column's data.
Customise Excel's default workbook
If you like to keep your Excel workspace clean and efficient, you're probably mildly bothered that Excel always creates new workbooks with three sheets, when you probably only need one. Change Excel's default settings by going to the File menu, then General, and then adjust the settings under "When creating new workbooks." You can specify the default font and size, whether to open in normal or print layout view, and the number of sheets to create.
Give a name to a range of data in Excel
Just as it's easier to remember your parents' names rather than their National Insurance numbers, it's easier to work with a range of data by name than by its cell addresses. Select a block of cells. On the Formulas tab in the Ruler, click on Define Name. Enter a name – or use the one that Word suggests – and take a moment to enter an explanatory comment about the range so that you'll be able to remember why it's there. Now, whenever you want to select a range of data, you can find its name in the dropdown list available from the Name box at the left of the formula bar. Note that any spaces you include in a named range get converted automatically to an underscore character. Read on for one use of named ranges…
Apply the same formatting and data to multiple sheets at the same time
You've probably wanted to format more than one sheet in a worksheet exactly the same way. Excel's grouped worksheets feature makes this easy. Ctrl-click the tabs of the sheets that you want to group together, and the grouped tabs will all turn white. (Normally, only the current sheet has a white tab, while the others are grey.) While sheets are grouped, anything you enter in one sheet also gets entered into the others. In the example shown above, I've applied blue background fill to a row in one sheet, and the same colour gets applied to the same row in the other sheets in the group. After formatting grouped sheets, and perhaps entering the same headings in each, remember to click on the tabs to ungroup them, so that you don't accidentally insert or remove data in multiple sheets when you insert or remove data in one.
Open multiple worksheets with one double click
Speaking of multiple windows, sometimes you work on two or more separate worksheets at the same time, each in a separate window, and you know you'll want to work on the same set of worksheets again tomorrow. So, before you shut down Excel, go to the View menu and click Save Workspace near the right side of the Ribbon. In the Save Workspace dialog, enter a name and location for your saved view settings, and a Saved Workspace icon gets created in the location you choose (perhaps your desktop). When you want to open the same set of worksheets, simply double click the Saved Workspace icon.
Filter Excel tables from table headings
When you create a table in Excel, you can click on the down arrow in the header cell and use the menu positioned to the upper left of it to filter the display – in other words, you can display only rows that contain subtotals and totals, or one or more specific rows that you select by checking a box in a list. You can display only the rows that match a specific background colour, or use the checkboxes to show or hide individual rows.
Recover unsaved documents
With Office 2010, Word, Excel, and PowerPoint all saw the introduction of a feature which saves documents that you’ve accidentally failed to save yourself before closing them – including documents that you haven't even bothered to save with a name more specific than "Document1." To open the last automated backup that Office 2010 has preserved, click File, Recent, Recover Unsaved Documents, and then choose the document from a standard File/Open dialog box.
Display formulas instead of results
A single keystroke lets you toggle between Excel's normal display, which shows the results of the formulas in the spreadsheet, and a display mode that shows the actual formulas. The keystroke is Ctrl-tilde (tilde is this key: ~) – press it once, and Excel displays formulas instead of results. Press it again, and the results appear again. This single keystroke is a lot quicker to use than the alternate method of displaying formulas, which is to open the File menu, go to Options, then Advanced; then scroll down to Display Options for this Worksheet and check the box next to "Show formulas in cells instead of their calculated results." Uncheck the box to display results again.
Bonus tip: When you use this tip to display formulas in cells, select a cell with a formula, and Excel outlines the cells that are referenced in the formula.
The finer points of “Select All”
Experienced Windows users know that Ctrl-A is the shortcut key that selects everything in a window or document; the A in Ctrl-A stands for All. Ctrl-A works this way in every application you can find – except Excel. When you press Ctrl-A in a worksheet with data in it, you select the current region (that is, all connected cells), not the whole worksheet. But wait – if you immediately press Ctrl-A a second time, you select the entire worksheet – unless the worksheet contains a table, in which case your second press of Ctrl-A will select the current region and the summary rows (typically the headers) of the current table. And if your second Ctrl-A selects the current table and its summary rows, then you'll need to press Ctrl-A a third time to select the entire worksheet. In the screen shot above, I pressed Ctrl-A once, and only the table got selected.
Bonus tip: The one-step way to select the entire worksheet is to the click on the grey box at the upper left corner of the worksheet – the one at the corner of the lettered columns and numbered rows.
Use the Ctrl key for quick navigation
When you want to move quickly to the left, right, top, or bottom cell in a data set, just press Ctrl and one of the arrow keys. Let's say you want to select the cells in the current row of the data set, but only the cells with numbers, not the labels – for example, sales figures for January through December. If the current cell is in the middle of the row (for example, the cell with the sales figure for May) press Ctrl-Left to go to the first data cell in this set (the cell with the sales figure for January), then hold down the Shift key and press Ctrl-Right to select all the sales figures for January through December. In the screen shot above, I started in cell G12, pressed Ctrl-left, then Shift-Ctrl-right to select twelve months of data.
The quickest way to see a sum or average
Type a few numbers in some adjacent cells, or highlight some numbers in existing cells. Now look down at the status bar at the foot of the window. Excel displays the average of the numbers, a count of the cells, and the sum. You can also use this trick with non-adjacent cells. Here's how: Click on one cell, then hold down the Ctrl key and click on a cell with a number that you want to add to the number in the first cell. Continue to add numbers by Ctrl-clicking in additional cells. The average, count, and sum in the status bar get updated each time you click another cell. By the way, when you use this method, Excel ignores any cells you click that contain text or graphics instead of numbers.