Save Your Excel Formulas


Q. I develop lots of special formulas for different spreadsheet files. I’d like to store them in a workbook and call them up when needed. I know I can do that with macros, but I find the language they are written in (Visual Basic) confusing and hard to use. I want to keep it simple.

A. I’ll show you an easy way, but you really should not be resistant to macros; they are very powerful tools.

To save and store a formula, begin by typing it into a cell (for example, =A1+B1 ). Now press F2, which puts Excel in the edit mode. Hold down the Shift key, highlight the formula (see screenshot below) and copy (Ctrl+C) it to the Clipboard.

Now get out of the edit mode by pressing Esc, go to the toolbar and click on Insert and Name , evoking the Define Name screen. In the space next to Names in workbook , type in a name for your formula— add_it . Note that the name must be one word, so if you use more, connect them with an underline dash (_).

Place your cursor in the box below Refers to . It will contain a reference to the location of the formula; ignore it and paste (Ctrl+V) your formula (which is still in the Clipboard) into the box (see screenshot below). Click on OK .

Whenever you want to use the formula, place your cursor in the cell where you want the formula to appear and type an equal sign plus the name you gave to the formula ( =add_it ). It will be copied into the cell and ready to work.



Year-end tax planning and what’s new for 2016

Practitioners need to consider several tax planning opportunities to review with their clients before the end of the year. This report offers strategies for individuals and businesses, as well as recent federal tax law changes affecting this year’s tax returns.


News quiz: Retirement planning, tax practice, and fraud risk

Recent reports focused on a survey that gauges the worries about retirement among CPA financial planners’ clients, a suit that affects tax practitioners, and a guide that offers advice on fraud risk. See how much you know with this short quiz.


Bolster your data defenses

As you weather the dog days of summer, it’s a good time to make sure your cybersecurity structure can stand up to the heat of external and internal threats. Here are six steps to help shore up your systems.