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 mark—the 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).
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.
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.