Welcome back to Tips from the Trenches! For our fourth series installment, we’re going to talk about tips to help you untangle formulas. First, let’s take a tour of the Formula Bar and the Formula tab of the Ribbon.
This is the Formula Bar:
You can add some rudimentary formatting within the formula editing bar by pressing Alt and Enter to add a carriage return and entering spaces wherever you find them helpful. This is especially useful for sorting out which arguments go with which function if you’re combining more than one function (“nesting”) in your formula.
Next, let’s take a look at the Formula tab of the ribbon.
Now, let’s take a look at how formula auditing and defined names can work together. To define a name, you highlight the cell(s) and type in your desired name in the name box. Names can’t start with a number, can’t look like a cell reference, and must be only numbers and letters (no spaces or special characters). The simplest way to create a Defined Name is to select the cell or cell range it should apply to and type the name into the Name Box (see the first image in this post). In the example below, we’ve got a spreadsheet to help a married couple who get paid at different times figure out their income for the first quarter of the year. We’ve named each of the variables to help us write clear formulas.
Using Defined Names can help your formulas read more like English and less like gibberish. This is the same cell with “Trace Precedents” on:
And here’s the Evaluate Formula box at work:
For our final navigation trick, let’s talk about links. The keyboard shortcut (in any Office application) to insert a link is Ctrl + K. This may not seem terribly handy in Excel at first, but you can create links WITHIN an Excel document using cell references or… defined names. This means you can create links that walk the user of your Excel workbook exactly through the places in the workbook they need to go. Here’s the dialog box that pops up with Ctrl + K:
You highlight your name or type in your cell reference and boom! It looks like a regular hyperlink, but it works within the world of your workbook.
Thanks for joining us for this installment of Tips from the Trenches. We hope this ROUNDUP of tips will help you get your formulas sorted out and your users herded in the right direction.
This blog post is a summary and is not intended as tax or legal advice. You should consult with your tax advisor to obtain specific advice with respect to your fact pattern.