Work Faster in Microsoft Excel: 10 Secret Tricks
Uncover hidden features, new uses for old tools, and handy workarounds to speed up your everyday work in the powerful spreadsheet application.
By Helen Bradley Jan 25, 2012 6:00 PM
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
When you need to start a new line as you're typing text in a cell, use Alt-Enter.
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
Need a quick tally of values in selected cells? Read it in the Status Bar.
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
Once you create a chart template, you can use it to format any future chart quickly.
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
The first step in charting and formatting sets of data that are far from one another is to select the data you need.
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
Using a Watch Window, you can view the values in cells not visible on your current screen.
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.
6. Make Formulas Easier to Understand
Using named ranges makes writing and troubleshooting formulas easier in Excel worksheets.
Sometimes formulas are not as clear as they could be. A formula that looks like '=C2*C5', for instance, is much harder to understand at a glance than one that reads, say, '=TaxRate*C5'. You can create formulas that use names (like 'TaxRate') instead of cell references (like 'C2') if you give common names to the cell or cells containing data you use repeatedly in calculations. To name a range, click the cell or select the range of cells to name. From the Ribbon toolbar, choose
Formulas, Define Name, and then type a name in the Name box. The name should start with a character, not a number, and although you can't use spaces, you can use an underscore character if you wish. Click
OK.
For example, if you were to name a cell 'TaxRate', later on you could use that name in a calculation, such as in the formula '=TaxRate*C5', which multiplies the value in cell C5 by the value of the cell you've named 'TaxRate'.
You can easily access named cells or ranges later.
When you have named cells on a worksheet, you can find any of those cells or ranges by clicking in the Name box, which appears at the far left of the Formula Bar under the Ribbon toolbar. Click the drop-down arrow in the Name box to view a list of all named ranges, and click one to go immediately to that area on the worksheet.
7. Format Large Values as Rounded Thousands and Millions
Use a custom format to show large numbers as rounded, simplified values.
Large numbers, such as thousands and millions, are often easier to work with as a rounded number than the exact value. You can set the cells containing large values to the nearest number of thousands using a custom Excel format. First select the cell or range to round off. Next, right-click the selection and choose
Format Cells, Number. From the Category list in the Format Cells dialog box, select
Custom. In the Type box, type
###, "k" and click
OK. This format will round up the number to a whole number of thousands and display it with the letter
k after it. For example, the number 21,678.22 will display as '22k'. Likewise, to format a number in the millions, create a custom formula that reads
###,,"m".
In addition to using this number format in your worksheets, you can apply it when formatting the axes of your charts. To format a chart axis this way, right-click the axis and choose
Format Axis. Then choose the
Number group and set up your custom format by typing it in the
Format Code box.
This format controls only how the numbers appear on your worksheet or chart axis, and does not affect the cell's value. Therefore, Excel will use the actual value in a cell when the cell is referred to in a calculation.
8. Print Multiple Sheets on One Page
One thing that’s impossible to do in Excel using any built-in command or option is to print data from multiple sheets in a workbook onto a single sheet of paper. Instead, Excel always starts a new sheet of paper for each sheet in the workbook.
You can assemble snapshots of data from multiple worksheets into one worksheet for printing on a single sheet of paper.
You have a workaround, however, that involves using the Camera tool. To start, you need to add the Camera icon to a toolbar; the easiest one to use is the Quick Access Toolbar. Click the down-pointing arrow to the right of the QAT, and choose
More Commands. From the drop-down list in the right panel of the Excel Options dialog box, choose
Commands Not in the Ribbon. In the panel below, select the
Camera icon and click
Add to put it in the QAT. Click
Close. Then, select the first range to print, and take a snapshot of it by clicking the
Camera icon. Move to a new worksheet, and click in the cell where the top-left corner of the snapshot should appear. Immediately, once you click there, the snapshot image will appear.
Now go to the second area that you want to print, select it, take a snapshot of it, and repeat the process of adding that snapshot to the same sheet as before. Continue to assemble all the data you need to print. Because the snapshots are all located on a single worksheet, you can now print them on one sheet of paper. Note, too, that the snapshots are linked to the original data, so if you make changes to the original data, the data in the snapshots will update automatically.
9. Borrow Table Formats for Your Excel Data
You can remove some table formatting while preserving the table itself.
Using the 'Format as Table' command on the Home tab of the Ribbon toolbar applies a neat format to a list of data in Excel. Unfortunately, it also adds filters to your column headings and changes the behavior of columns and rows adjacent to the list. If you want the convenience and simplicity of applying table formats with a single click, but don't want the other features applied at the same time, you can easily remove the extras. To see this at work, select your list, including the heading row. Apply a table format to it by selecting the
Format as Table option on the Home tab of the Ribbon. Click a table style to apply it to your list. When the Format As Table dialog box appears, select the
My table has headers checkbox, then click
OK--your data will be neatly formatted.
To remove the other features that Excel adds automatically to your list, keep the list selected. Choose
Table Tools and then the
Design tab, and select the
Convert to Range option. When Excel asks, 'Do you want to convert the table to a normal range?' click
Yes. This action removes the filters from the column headings, and removes other table-specific behavior, but leaves the formatting in place.
10. Create a Custom Data-Entry List
Use a Data Validation List to make selecting values to enter data into cells easy.
It's always quicker and more accurate to select an entry from a prepared list than to type the item yourself. So when you have a worksheet requiring common entries that could exist in a list, set them up that way. To create such a list, in an empty sheet in the workbook type the list of items to choose from, in one column. Return to the sheet where you will use these items, and select the range into which they will be entered. Choose
Data, Data Validation, Data Validation, and then click the
Settings tab. In the Allow drop-down menu, select
List. Click in the Source area, navigate to the sheet that contains the data, and select the cells containing the items you just typed. Click
OK to close the dialog box.
Now, when you select one of the cells to which you just added the Data Validation option, you'll see a drop-down arrow appear. From the list that appears when you click the arrow, you can select the item to enter into that cell.
