Microsoft Excel Cheat Sheet - Formulas

Need to remember the order of operations? Don't retake high school math (blech)! Use this cheat sheet instead!

 

Intermediate Excel Cheat Sheet

 
Order of Operations
o PEMDAS: Do what is in parentheses first! Then exponents, multiplication, division, addition, 
subtraction

Formulas:

  • Labels - text, words
  • Constants - just a number
  • Formulas - a mathematical equation used to calculate 

Steps for Creating Formulas:

1. Type the equal sign

2. Type the cell names or click on the cells you will be using for the formula, and add the appropriate formula symbol
 
3. Press the ENTER key
 Example: = A1+A2
 (This formula will add the contents of cells A1 and A2)
 
Use Cell Addresses When Creating Formulas:
 

-The point of Excel is to write formulas in a way that allows you to change the data without having to change the formulas themselves.

-To do this, you need to tell Excel which cell the data is located in. A cell's location in the spreadsheet is referred to as its cell reference.

-To find a cell reference, simply look at the column headings to find which column the cell is in, and across to find which row it is in. You can also refer to the number box.

Auto Sum

This shortcut button helps you to: add a set of numbers faster, find the average of a set of numbers
1. First click in the cell you want the sum/ answer to appear
 
2. Then click the AutoSum command icon in the Editing Group of the Home Tab. (image above) if you are averaging, make sure you click on the dropdown arrow next to the AutoSum icon to change what the function will do. 
 
3. Select the text you wish to add or average, or click on the cells you wish to add to the AutoSum function, and then add in the operator. 
 
4. Press the ENTER key to calculate the formula when you finish.
 
Functions (Fx)
 
Predefined formulas that perform calculations using specific values in a particular order
1. Click on the empty cell where you want the return value or answer to go
 
2. Click on the “insert function” command in the Function Library Group of the Formulas Tab, or the icon next to the formula bar (image above).
 
3. In the Insert Function dialog box that appears, type in a description of your function, such as round, or square root in the “search for function” box, then press the “go” button
 
4. Click on the appropriate function when it appears below in the “select a function” box. Then press the “ok” button
 
5. Enter in the function arguments using cell addresses in the function arguments dialog box that appears, then press the “ok” button
 

 

Tip Type: