This tutorial was written by Digital Studio Student Assistant, Nicholas Park. Learn more about Nicholas and our other student assistants here.
Excel is extremely useful for a variety of different functions and purposes: organizing or analyzing data, creating tables and graphs, and much more. With Excel, you can access, review, and edit spreadsheets for personal as well as professional use. There are wide variety of different functions, tools, and gimmicks that come with the application. Some of the most basic of these functions, that this tutorial will explore, can help make time-consuming tasks simpler and easier.
There are a variety of different formulas you can input to complete specific tasks. Some of the few, easy ones this tutorial will give explore are: =SUM, =AVERAGE, and writing your own functions or equations.
The SUM and AVERAGE functions are used by typing in “=SUM” or “=AVERAGE” then highlighting the specific cells of interest that you would like to SUM up or take the AVERAGE of. The SUM function, note, will add the numbers in the cells; therefore if you wish to subtract, mark the number you desire to subtract as a negative number with the “-” symbol (I.e. -50). The function will do the rest. The AVERAGE function will take the average of the highlighted cells in a similar fashion to the SUM function.
Some things to familiarize yourself with are the “:” symbol, which serves as the range operator. It describes the range between the two cells you are selecting. For example B5:B15, would describe the cells between 5 and 15, inclusive, in the B column. If you were to pair this range with say, =SUM(), then the result would be the sum of all numbers in B5:B15 [=SUM(B5:B15)].
Commas allow you to combine multiple references into a single reference. For example, =SUM(B5:B15,D5:D15), would take the sum of B5:B15, in addition to, D5:D15. Simple enough right?
Before we continue, there are several things to remember. Excel follows general mathematical rules for calculations, which is Parentheses, Exponents, Multiplication and Division, and Addition and Subtraction, or the acronym PEMDAS. Using parentheses allows you to change that calculation order. When writing your respective formulas, remember “plus” is “+”, “minus” is “-”, “divide” is “/”, amd “multiply” is “*”. By using parentheses, you can alter the order in which these calculations are done. Some other things to keep in mind are that exponents are introduced with the “^” symbol (For example, 3^3 = 27) and “%” represents “percents”. When writing your formulas, keep in mind you can use numbers and/or cell references in order to complete your calculations. For example, B1-54.312=, would result in the value that exists in B1 subtracted by 54.312. ((B1*3)/16) =, would take the order of PEMDAS and multiply the value of B1 by 3, and then divide it by 16, giving you the desired calculation.
Formulas can be dragged across different cells if you click the bottom right corner of the cell with the equation, and drag to highlight the desired cells with the respective calculations. If you’re using a specific range of cells, the calculations will shift accordingly as well. To better explain this, examine the image below:
As you can see, the cells involved (B3 and C3), are calculated in D3. However, when the equation is dragged down the respective cells, you can use the formula for B4, B5, and B6, multiplied by their respective “C” column counterpart for the total cost. These formulas are not limited to just multiplication, so be creative!
In the event in which you want to edit an existing formula, click the cell you wish to edit. Then proceed to double-click the cell to view the formula bar and edit the formula from directly within the cell. Color coordinated highlights will help you keep track of your targeted and edited cells. If you make a mistake, you can press the Esc button to avoid making any undesired changes.
Keep practicing, and test other functions as well. Good luck!