Work Faster in Microsoft Excel: 10 Secret Tricks

Excel puts lots of great tools on your desktop, but what each one of them does isn't always obvious, especially since so many Excel features are hidden.

Would you like to create multiple lines of text within a cell? There's a keystroke for that. What if you want to reuse a chart's formatting on another chart, or print multiple worksheets on a single sheet of paper? Those aren't the only timesaving tools at your fingertips. Here's how to find and use them to work smarter in Excel.

1. Make New Text Lines in a Cell

As you've probably already discovered, when you’re typing text into a cell and you want to start a new line of text, the Enter key doesn't produce the desired result. Pressing Enter merely places the text in the current cell and then selects the next cell.

Instead, to start a new line in Microsoft Excel, as you're typing text, press Alt-Enter. On the Mac, the key combination is Control-Option-Return.

2. Quickly Add Values in Cells

What do you do when you need a quick calculation, such as adding the values of two cells? Rather than typing a formula to do it, click once on the first cell to select it and then Ctrl-click the second cell. By default, the Status Bar--which runs along the foot of the Excel window--will show the result of adding the values in the selected cells (Sum).

You can also view other calculations in the Status Bar by right-clicking the Status Bar and choosing from the calculations listed there; aside from Sum, you'll find Average, Count, Numerical Count (the quantity of cells selected that contain numerical values), Minimum, and Maximum.

3. Create a Reusable Chart Template

Creating a series of charts for a project, such as an annual report, usually means that you need to format all of the charts similarly. Create a chart template for the design, and you can use it to create and format new charts in a flash.

First create one chart and format it as you want all of the charts to look in the future. To save the chart formatting and appearance (but not the data) as a template, choose Chart Tools, go to the Design tab, select Save as Template, and then type a name for your template. The entry in the Save As Type box should read 'Chart Template Files(*.crtx)'; if it doesn't, select that option from the drop-down list. Click Save.

To use this template to format a new chart, begin by selecting the data to chart. From the Ribbon toolbar, choose Insert, Other Charts, and then All Charts Types. Now click the Templates option. From the My Templates group, select the template you saved, and click OK. Your new chart will have the same formatting as the chart template, saving you from having to do the work again yourself.

4. Format and Chart Far-Flung Data

Here's another quick timesaver: When you want to format a series of cells that don't appear side by side, start by selecting the first block of cells and then hold the Ctrl key as you select the second and subsequent blocks of cells. You can then apply a format, such as a font change or fill color, to all the selected cells.

You can use the same technique to chart cells that don’t appear side by side, as well. For example, if you need to create a chart from a table of data using the headings in the first column and the data from the fourth column, first select the headings in the first column. Then hold Ctrl as you select the matching data in the cells in the fourth column. Afterward, create a chart, just as you would any regular chart.

5. Keep an Eye on Data With a Watch Window

When you’re editing a very large worksheet, often you need to view how your changes in one area affect totals and other calculations in cells that currently sit off-screen, either farther down the page or on another worksheet tab. Instead of repeatedly moving back and forth from your working data area to the cells that show the results you're interested in, use a Watch Window to display the values of the cells in the other part of the worksheet as you tinker.

To set up a Watch Window, click the left mouse button within one cell that you want to watch. From the Ribbon toolbar, choose Formulas, Watch Window. When the Watch Window dialog box appears, click Add Watch. Because you've already selected the cell, you simply need to confirm that the reference to it in the dialog box is correct, and then click Add.

Now return to the area of your worksheet that you're editing. As you do so, you'll see that the Watch Window floats atop the worksheet, allowing you to work on your data. While you edit, the Watch Window shows the other cells, reflecting the changes you're making. You can move and resize the Watch Window as you wish, and use the Add Watch button to view additional cells in the Watch Window--even cells on another sheet--so that you can track the results in multiple cells as you work.