Excel Tips and Tricks

By Jim Mollé In my previous article, Common Excel Errors That Are Easily Avoidable, I discussed some common mistakes people make when creating Excel spreadsheets. Now that we have learned a few ways to avoid common errors, I would like to introduce a few tips and tricks to make the Excel using experience more efficient.

The Show Formulas Shortcut

One simple trick that will come in handy when you quickly need to see the actual formula within a cell, as opposed to the formula’s result, is how to toggle the Show Formulas command on and off. This command switches from the result of a formula to the actual formula itself and is useful when troubleshooting or editing formulas in the spreadsheet.

The keyboard shortcut for this command is CTRL + ` (that’s the backward tick markthe same key as the tilde key: ~ .)

You can also go to the Formulas Tab of the Ribbon, and click the Show Formulas button in the Formula Auditing group (see Figure 1). 

figure one

Using Absolute Cell References

Creating a formula in one cell and then copying the formula into other cells is a huge time saver in Excel.

However, there may be a cell reference in your formula that you do not want to adjust as the formula is copied. In such cases, those cell references in which you want to “anchor” in place, so to speak, need to be changed to absolute cell references. To do so, you must insert dollar signs “$” into the cell references. This can be done with the handy keyboard shortcut F4. After clicking or typing a cell reference in a formula that needs to be absolute, simply press the F4 key and Excel will insert “$” signs into the reference, rendering it absolute.

Assigning Names to Cells

While the handy F4 keyboard shortcut makes it easy to convert relative cell references into absolute cell references, there is another way to insert absolute cell references into a formula, one that may also make it easier to read your formulas – and that is by using Names in your formulas.

To assign a name to a cell or a range of cells, first select the cell you want to name, and click the Defined Name command button, located in the Names group of the Formulas Ribbon.

figure two

In the Define Name window you can assign a name to the cell or cells which appear in the Refers to: field.

Note that names cannot contain spaces or other symbols, except for the underscore character. Also note that by default, the cell or cells in which the name refers to are by default absolute, apparent by the presence of “$” signs in the cell reference.

For more Excel tips and tricks, check out our online software training at www.learnitanytime.com or join us in class!

***

JimJim Mollé is a Certified Microsoft Office Master Instructor and Learn iT! Hall of Famer. If you've got Excel questions, he’s got answers!