Formulas and functions are great features in Excel, that can make your life a lot easier. But if you don’t know some of the essentials, they can be really frustrating. Here are a five things I think everyone should know about formulas and functions:
- When you enter a number over 999 in a formula or function, never put a thousands separator comma in the number. In a function, it will move you on to the next argument prematurely. In a formula, you will get an error.
- A formula cannot reference itself. This is called a circular reference. (Using iterations is an exception; search the Excel Help files or read here for more on this advanced topic). If you want to do a calculation on a value in A1, you cannot enter the formula in A1. You will have to enter it in another cell. Learn more.
- Copy formulas down a column or across a row using the fill handle.*
- Use cell references instead of constants in a formula whenever possible. This way, if you update the cell, the formula will update automatically.*
- Use an absolute cell reference when you are going to copy a formula somewhere else on your worksheet, and don’t want the cell reference to change relative to where you pasted the formula.*
* I’ve made a video explaining the last three tips. Have a look!
Can you think of another essential? Leave it in the comments.