Tips and Tricks

15 Excel Tips

Learn how to make quick selections, create text boxes, cut and paste in a single stroke... and more

Excel packs a real punch. But you must know how to make it show its muscle—given below are 15 tips that will help you get the best out of your Excel experience.

1. Text boxes and other shapes

To place a text box in Excel, you need the Drawing toolbar—choose View, Toolbars, Drawing. Now click on the Text Box button in the Drawing toolbar (its icon resembles a printed page). Use the mouse to draw the text box in your worksheet. Once you’ve drawn the text box, you can format its appearance through the right-click shortcut menu. Click on the text box and choose Format, Text Box. When you save the worksheet, the text box and all its parameters are saved with it. When you open the worksheet again, the text box will appear just as you left it.

2. Don’t print everything

You’ve taken to annotating your Excel workbooks with little callout bubbles you created with AutoShapes, from the Draw toolbar. The callout may be a reminder to yourself about how you have cheated on the figures! You don’t want these printed and you don’t want to delete them either. You can tell Excel not to print the AutoShape, as follows:

1. Right-click on the AutoShape and choose Format AutoShape.
2. Click on the Properties tab.
3. Click on the Print Object check box to turn it off.
4. Click on OK.

You can use the same general technique to prevent other Draw objects—lines, shapes, text boxes, Word Art—from printing, too.

3. Headings on every page

When you have a multi-page printout of a large Excel workbook, you end up having the column headings only on the first page and it becomes difficult to identify the columns on subsequent pages. Next time, make reading your printout easier for everyone by including column titles on every page:

1. Choose File, Page Setup.
2. Click the Sheet tab.
3. Click the button next to the "Rows to Repeat at Top" box and select the row on your worksheet containing your column titles
4. Press Enter.
5. Click Print.

The rows you selected appear at the top of every printed page.

4. Act natural

Say you’ve got an Excel worksheet with plain-English column and row titles—such as "Name" or "Marks"—and you want to create a formula that makes a calculation based on one or more of these rows or columns. You could do this by typing in the cell references or by pointing and clicking. But Excel makes the whole process a lot simpler. Just include the name of the column as the argument in your formula. For example, to calculate the average of the values in your "Marks" column, try this:

1. Select the cell in which you want to enter your formula.
2. Type =AVERAGE(Marks).
3. Press Enter.

Actually Excel goes a step further and lets you refer to specific cells by plain English names. Suppose you have the following spreadsheet:

Now you can refer to the cell C3 as Kumble Runs and even use it in a formula such as

=Kumble Runs/Kumble Balls or =IF(Dravid Runs>Tendulkar Runs,"Boo","Yea")

5. Single-stroke selection

You have a huge, huge region of data in an Excel workbook—many columns wide by many, many rows tall—and you need to select it. Selecting with the mouse can be tricky—especially when the screen starts scrolling like crazy when you try to select beyond the current window contents. Try it this way instead:

1. To select the entire current row, press Shift + spacebar.
2. To select the entire current column, press Ctrl + spacebar.
3. To select a region, select ANY cell in the data region and press Ctrl + Shift + *

Excel selects the entire data region and, as an added convenience, doesn’t move you anywhere.

Bonus: Click on the AutoSum button to add up all columns in your selection. In general, this technique instantly sums the columns of your data region. However, if you’ve labeled a row or a column (or both) with the word "Total" or "Totals," it sums that row, or that column, or both.

6. Single-stroke pasting

Copy/Cut and Paste are tasks everybody repeats very frequently while using spreadsheets. Here is a way to paste instantly—without clicking on a toolbar icon, menu command and without using a keyboard shortcut.

1. Select the cells you want to Copy or Move.
2. Choose Edit, Copy/Cut (or use a toolbar button or keystroke equivalent).
3. Move to the cell into which you want to copy or move the selection.
4. Press Enter.