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.

 

Related Posts

Find Circular References in Excel