Formulas are an integral part of Excel, and are the defining feature that elevates a workbook far above a simple calculator.
Contents:
Understanding the Basics
A formula in Excel is a type of expression that performs a calculation on values in your worksheet. They can be a combination of values, cell references, and operators that perform these calculations.
Here are some examples of different formulas:
Every formula in Excel must begin with an equal sign (=), followed by the values, cell references, or functions that you want to use in the calculation. For example, the following formula adds the values in cells A1 and A2:
= A1 + A2When you enter this formula into a cell, Excel will perform the calculation and display the result in that cell. You can also use functions in your formulas to perform more complex calculations, such as finding the average of a range of cells or calculating the interest payments on a loan.
Using the Formula Bar
In each cell that contains a formula, the cell will display the formula's calculated value. This is great unless you are trying to figure out how a number is actually being calculated. To do that, we can use the formula bar.
The formula bar is located at the top of the Excel window, and is used to display and edit the formulas used in the different cells of a worksheet. When you select a cell, the contents of the cell are displayed in the formula bar, and you can edit the contents of the cell by typing directly in the formula bar.
The formula bar is particularly useful when you're working with formulas and functions in Excel, as it allows you to see and edit the formulas in the cells without having to go into the cells themselves.
Using Basic Mathematical Operators in Formulas
Addition - To add two numbers in Excel, you can use the plus (+) operator.
Subtraction - To subtract one number from another in Excel, you can use the minus (-) operator.
Multiplication - To multiply two numbers in Excel, you can use the asterisk (*) operator.
Division - To divide one number by another in Excel, you can use the forward slash (/) operator.
What is a Function?
Functions in Excel can make it easier to perform complex calculations and allow you to automate tasks in your worksheet. There are hundreds of functions available in Excel, each with a specific purpose and set of arguments, and you can use them in combination with each other to perform even more advanced calculations.
For example, let's say that you were trying to take the average of four different numbers in cells A1, A2, A3, and A4. You could use the standard operators to add them all up and divide by 4, like so:
= (A1 + A2 + A3 + A4) / 4 Or, you could use the built in AVERAGE function to do this all in one step:
= AVERAGE(A1:A4)Learning new functions and knowing where to use them is crucial to getting better at Excel. Let's look at some of the most common functions.
Most Common Excel Functions
For all of the below examples, we'll be using the "Units Sold" numbers and performing some basic Excel functions on them to see the results.
SUM - This function will sum (or add) up every cell in a given range.
AVERAGE - This function will take the average of a given range of cells by adding up all of the cells and dividing by the number of cells in that range.
COUNT - This function will count the number of cells in a range that contains numbers.
MIN and MAX - These functions will return the minimum and the maximum value from a range of cells
What are Cell References?
Cell references are an essential part of creating formulas in Excel.
A cell reference is a combination of a column letter and row number that represents a specific cell or range of cells in your worksheet (like A1 or B2), that are used to refer to the data in those cells.
There are several types of cell references that you can use in your formulas, including relative and absolute references.
Relative Cell References
Relative cell references is any standard cell reference with the cell letter and number standing alone like A1 or B2. When you copy and paste a formula that contains a relative cell reference, the reference will adjust to the new location of the formula.
In this example, the formula " = B3 + C3" will always sum up the two cells directly the left of where the formula is input.
We can copy and paste, or move this formula around but the formula will always sum up the two left cells relative to where the formula is.
The cell references change depending on where the formula is.
Absolute Cell References
Absolute cell references are indicated by a dollar sign ($), before both the column letter and row number. An absolute cell reference will always reference the same cell, even if you copy and paste the formula to another location in your worksheet. For example, $A$1 will always reference cell A1, no matter where the formula is located.
To demonstrate this, let's say we have a list of production costs, and we want to apply a 20% markup to each of those costs to get our sale price.
Here we need to use an absolute reference inside of our formula to ensure that the 20% cell stays selected when copying our formula.
If we use this formula, the markup in C2 will move downward with the formula.
= B5 * C2So, by using an absolute reference, and locking down that C2 with $ signs, the cell remains locked, and every formula will now properly multiply the production cost by that markup.
= B5 * $C$2
Here you can see that the markup stays in use when the formula is dragged down or copied to the below cells. The cell reference does not change depending on where the formula is placed.
