Excel formulas and functions are powerful tools for performing calculations and data analysis. Formulas are custom calculations you create, while functions are predefined calculations provided by Excel.
Contents:
Formulas vs Functions
Formulas are custom expressions created by the user to perform calculations such as adding up two cells like =A1 + B1.
Functions, on the other hand, are predefined formulas provided by Excel that simplify complex tasks, like =SUM(A1:A10) to add a range of cells.
Formulas can use cell references (a letter then a number like A1 or B1, to designate the cell location) or can use hard coded numbers.
While formulas require manual setup by the user, functions offer built-in shortcuts for common calculations, making data analysis quicker and easier.
Formulas
How to Enter a Formula
Select a cell where you want the result to appear.
2. Type =
3. Enter the formula or function, e.g., =A1 + B1 or =SUM(A1:A10) You can either manually type in the number or cell reference, or you can click on the cells themselves.
4. Press Enter
Now if we enter values in cells A1 and B1, they will be added up together in our formula. You can see the formula "=A1 + B1" in the top bar, but the result "4" is printed in the cell itself.
Common Formulas
Some of the most common formulas use a few different operators that you'll likely be familiar with.
Addition using the "+" operator:
= A1 + B1Subtraction using the "-" operator:
= A1 - B1Multiplication using the "*" operator:
= A1 * B1Division using the "/" operator:
= A1 / B1These operators follow PEMDAS when calculating, so it calculates anything in parentheses first, then exponents, multiplications, divisions, additions, and subtractions.
Functions
How to Enter a Function
Entering a function is just like entering a formula, but instead of entering cell references right away, you type a pre-defined Excel function. After hitting "=", if you type "S", you'll see a list of every function that starts with S.
If you continue to type "=SUM", you can hit the TAB key to select the function or you'll need to manually type "(" to begin the function.
You'll know you did it right if you see some information bellow the cell. This is Excel's way of telling you what inputs go into each function.
Common Functions
These are some of the most common functions you'll use and run across.
Note: These examples are all referencing A1:A5. When you have two cells separated by a ":" sign, that means it's a range. So every cell between A1 and A5 is being included.
SUM: Adds values in a range
= SUM(A1:A5)AVERAGE: Calculates the average of values
= AVERAGE(A1:A5)MIN: Finds the smallest value
= MIN(A1:A5)MAX: Finds the largest value
= MAX(A1:A5)COUNT: Counts cells with numbers
= COUNT(A1:A5)IF: Checks a condition and returns one value if true and another if false
= IF(A1>10, "Yes", "No")