8 Tips for Microsoft Excel

Navigate, sort, and edit your data with ease

Move around a worksheet quickly

The standard shortcut for getting quickly around the worksheet to the first or last entry in a filled row or column involves using Ctrl and the relevant arrow key. For instance, hold down Ctrl and press the down arrow to go to the last entry in the current column. Similarly, Ctrl + left arrow will take you to the first entry in a block in the current row.

There's an equally effective and less well known trick: double-click on the border of any cell or cell range that you have selected, and you'll move to the last filled cell in that direction.

Sorting a range

Excel's sort facility is used most often to sort the rows in a whole worksheet, but you can sort a defined range within a worksheet - very handy if you use Excel to keep an address list that needs to be refreshed every so often, for instance.

Simply select the cells you want to sort, select Data | Sort, specify the options you want, and click OK.

Incidentally, the Data | Sort procedure normally sorts by columns. But you can also sort a selection on the basis of the contents in individual rows. The results arrange the cells in each row in sequence. Select some data spanning multiple columns, go to Data | Sort, click Options, and choose Sort Left To Right.

Inserting identical sheets

If you need to insert another worksheet with contents or formatting that are identical to an existing sheet, you can do it by hand. Take a copy of the data you want (Ctrl + C or Edit | Copy), go to Insert | Worksheet, paste the data on to the new sheet (Ctrl + V or Edit | Paste).

But there is a quick alternative. Click on the tab for the sheet you want to copy; hold down Ctrl and drag the tab into place in the sheet sequence. A triangle pointer appears directly above the sheet tabs to indicate the position of the new sheet; your mouse pointer will display a page with a plus sign to indicate that it's copying rather than moving the sheet.

Release the mouse button to drop the sheet in at the triangle position. Change the sheet name and clear any data you don't want. And you'll have a whole new sheet with a copy of content and formatting from the original.

Each new workbook starts with three worksheets by default. If you don't generally use that many, or if you find yourself habitually adding more, go to Tools | Options | General and alter the setting for Sheets In New Workbook.

Forced returns

If you're entering a long formula, you might want to insert returns to make things more legible for anyone who has to edit the worksheet. You can do that by pressing Alt + Enter while typing the text in the formula bar or directly in the cell; the calculation will acquire a new line, and you can carry on entering more of the formula.

This has no effect on the result of the formula as it appears in the worksheet. But if you want multiple lines of text in one cell, you can use the same technique - just press Alt + Enter wherever you want a line break.

Clearing cells

When you delete a cell, both the cell and its contents disappear from the worksheet and the other cells shift to fill the gap. Right-click on the cells to delete, then select Delete from the pop-up menu.

Clearing a cell isn't the same thing at all. When you clear a cell, the cell remains in the worksheet - only its contents are removed. To do this, select the cell and press Delete.

You can do the same with a range of cells - select the cells, press Delete. And to delete multiple cells that aren't next to each other, select them (holding Ctrl while you click on individual cells) and then hit Delete.

The very best way to erase everything from a cell - including formatting and comments - is to select it and choose Edit | Clear | All.

Display comments

Comments are a neat tool - not only for explaining the contents of a particular cell, but also for giving instructions to users. To view the comment (which you create using Insert | Comment ) you normally have to hover the mouse pointer over the triangle on the corner of a commented cell. Excel can be configured to display comments automatically - go to Tools | Options | View and select Comment and Indicator . If a comment obscures a cell you need to see, it can be dragged away into a new position and it will still point to the commented cell.

Converting drag-and-drop data

If you hold down the right mouse button while dragging cell data from one area to another, Excel will display some handy conversion options when you drop the data. They include the particularly useful options to Copy Here As Formats Only (so the contents of the original cells aren't copied, only the formats) and Copy Here As Values Only (won't copy any formula from the original cell, only the result).

Change the title on comments

When you insert a comment, the text is started automatically with your user name. But you may be using the comment box for something other than personal comments. In that case you won't want your name preceding the note.

Go to Tools | Options | General and change the User Name textbox to read whatever you want - "Definition:" or "Explanation:", for instance. Next time you use Insert | Comment, your comment will start with the new text.

	��Like what you've read? Try our free newsletters.

What next?