Microsoft

Six been-around-the-block tips for working more efficiently in Excel

Just because a tip has been used for a long time doesn't mean it isn't new to you. Learn a few of Susan Harkins' old but reliable tips for working more efficiently in Excel.

I use a lot of older tricks almost every day. Because they've been around for a while, I assume everyone knows them. This article shares a few older tips and tricks; they're shortcuts that are second-nature to me because I've been using them for so long. I'm hopeful that some of you will learn something new, even if they are old tips to me!

1. Quick functions on the fly

Being asked for unanticipated information in the middle of a meeting is unpleasant. If you're lucky enough to have your notebook with you, and your spreadsheet open, you can avoid the deer-in-headlights response by checking the status bar. For instance, let's suppose your boss asks the total number of calls from Client 2. You could enter a SUM()function, or you could select the values. The status bar, shown in Figure A, displays the results of several basic functions evaluating the selected values. This trick is limited to the functions shown, but in the right circumstances, these functions can come in handy. The more data you have, the more impressive this feature is.

Figure A

Figure A

The status bar displays the results of several functions.

It also works with a non-contiguous selection. Suppose the boss asks for the total number of calls during the first three months for Clients 1 and 3. Figure B shows the results of selecting the January through March values for those two clients.

Figure B

Figure B

This tip works with non-contiguous selections.

To display these functions on the status bar, right-click the status bar and check the functions shown in Figure C. Uncheck those functions you don't want to display on the status bar.

Figure C

Figure C

Right-click the status bar and check the functions.

2. Limit the work area

Most spreadsheets are simple fare with the majority of the data in a few columns and rows, leaving lots of empty cells. You can limit access to those empty cells by hiding unused columns and rows. Users with the know-how can always unhide them. However, if the empty cells aren't visible, most users are inclined to forget they exist at all, so they'll be less inclined to alter the sheet. Follow these steps to hide unused columns and rows:

  1. To hide unused columns to the right, select the first unused column (Figure D)
    Figure D
    Figure D
  2. Press [Ctrl]+[Shift]+[Right Arrow] to extend the selection to include all the columns to the right
  3. Right-click the selection
  4. Choose Hide from the resulting context menu

Similarly, to hide rows, select the first blank row and press [Ctrl]+[Shift]+[Down Arrow]. Figure E shows the sheet with unused columns and rows hidden. The hidden columns and rows are gray.

Figure E

Figure E

Hide unused columns and rows.

To unhide columns and rows, click the Select All cell (the square at the top-left corner of the sheet) — alternatively, you can press [Ctrl]+[A]. On the Home tab, click the Format drop-down in the Cells group. Choose Hide & Unhide, and then select an Unhide option from the resulting sub-menu.

3. Quick range name

Excel offers a number of ways to assign a range name. After selecting the range, you probably click the Formulas tab and then click the most appropriate option from the Defined Names group. But here's a quick method that you might not know about. Enter the name manually in the Name Box control — that's the small white cell to the left of the Formula bar. Just click in the control and enter the name as shown in Figure F. This method isn't superior to any of the others, but it saves a few clicks.

Figure F

Figure F

Use the Name Box control to quickly create a new range name.

4. Move the formula tip control

When you enter a function, Excel displays the function's syntax just below the active cell. It's a big help for those of us who don't have every argument of every function memorized (I know that I don't). As helpful as it is, it can obscure data that you might need to see. Fortunately, you can move it! Simply move the mouse over the tip until Excel displays the four-arrow pointer. Then, click and drag the tip anywhere you want it (the move is temporary).

5. Double-click split

You probably know that you can split a sheet by dragging the split tool from either scroll bar. The split tool at the top of the vertical scroll bar will split the sheet between rows. Similarly, the split tool at the right of the horizontal scroll bar will split the sheet between columns. Just drag the tool and drop the split bar where you need it. To remove the split, drag it off the sheet. Figure G shows the two split tools.

Figure G

Figure G

Split a sheet by rows and columns.

There's another way to accomplish the same thing. Click the cell where you want to make the split and double-click the tool instead!

6. Hide tabs

If you think the ribbon takes up too much space, hide the groups. Simply double-click any tab to achieve the results shown in Figure H. To gain access to the groups temporarily, click a tab. To unhide all of the groups permanently, double-click any tab. Even if you never use this, it's good to know, because you might open a workbook that you're unfamiliar with and find all the groups missing. In addition, this double-click works in all ribbon-version applications.

Figure H

Figure H

Hide the groups.

Your old favorites

Please share your favorite older Excel tips in the Comments section. Don't assume everyone knows them already. It's my experience that every old trick is still new to someone — and new users open Excel for the first time every day.

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible. For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox