Work Faster in Microsoft Excel: 10 Secret Tricks

Page 2 of 2

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.

To comment on this article and other PCWorld content, visit our Facebook page or our Twitter feed.
| 1 2 Page 2
Shop Tech Products at Amazon