Do you know that? You have developed a smooth formula that does exactly what it should do and you plan to use it every other week or month (meaning it should be reused in some way). Simply add a "Fomula Memory" worksheet to your workbook. In this worksheet enter a short description in column A and then copy your formula with Edit -> Copy or using the keyboard shortcut CTRL+C and rightclick the corresponding column B cell and choose Paste special -> Formulas (see screen shots).
After you have pasted the formula, simply hit the F2 key on your keyboard to edit the formula and jump to the first letter and add a "#" before it. You can use it on several workbooks or as a master workbook for example that opens when your computer is starting every day.
2. Use Named Ranges instead of cell-addresses
In some cases it's quite hard to "read" some of Excel's formulas because if your calculations are only done by cell-references, it may come in handy to have a "speaking" formula. See the following table:
| A | B | |
| 1 | Revenue | 1,575,789 |
| 2 | Personal Costs | 895,783 |
| 3 | Taxes | 46,239 |
| 4 | ||
| 5 | Net Revenue | 633,767 |
| Formulas in the table | ||||
|
Hmmmm... what was B2 exactly? Before guessing, use names instead. They are really easy to apply. Simply go to the input box left of the formula bar which normally reads the cell address. Enter any desired name and hit ENTER. Take care that you do not use spaces in your names. You can use the so called camel technique to write down cell names that consist of more than one word. In our example "Personal Costs" (with a space) will become "PersonalCosts" (with no space inbetween). Now you can do the same calculation with speaking arguments:
| A | B | C | |
| 1 | Revenue | 1,575,789 | |
| 2 | Personal Costs | 895,783 | |
| 3 | Taxes | 46,239 | |
| 4 | |||
| 5 | Net Revenue | 633,767 | 633,767 |
| Formuals in the table | ||||||
|
Display Excel tables online >> Excel Jeanie HTML 4
3. Break down longer formulas
Remember the long formula we had in one of our previous posts on utilizing a VLOOKUP over multiple tables? We had a quite long formula there. It was something like
=IF(ISNA(VLOOKUP(A2,East!$A$1:$B$9, . . . VLOOKUP(A2,East!$A$1:$B$9,2,FALSE))
If you have entered it in the formula bar, simply hit ALT+ENTER and split the formula where appropriate (see screenshot):
This last one came from Catharine of Webgenii. Thank you for the hint! :-) As always, if you like these tips or have any additional ideas and how you would use this information, drop us a comment or send us an email through our contact form.
Discuss this blog post in the forums (click the blue button)
