Working with a spreadsheet can be tough and confusing for newbies, so this basic guide to Microsoft Excel formulas is heaven-sent. You will learn how to perform the four standard math operations, calculate the percentage, and learn terms such as Vlookup to name a few. These Excel formulas cheat sheet should help you get started with the spreadsheet, reduce the learning curve, and even have fun with Excel!
How To Create Microsoft Excel Formulas
The Difference Between Microsoft Excel Formulas And Functions
Wait up! Before you proceed with the list, it’s important you know the difference between a formula and a function. Although both can be found in Excel, they don’t work similarly. A function is a premade command such as SUM or AVERAGE. A formula, on the other hand, is any calculation you do to the spreadsheet. It may or may not contain or use a function. An example is =SUM(A1:B1). For a complete idea of how Excel calculates, go here.
— Tissh Quinn (@Tissh_D_Troll) June 6, 2017
Common Microsoft Excel Formulas
This will be your Excel formulas cheat sheet! Now, although you can just go through the list, it’s so much better if you can apply it directly. Do this easy exercise. Open your Microsoft Excel and input the following information. Consider this as the time sheet of four employees. Take note of the columns and rows they are placed.
— Erick Archila (@eerickarchila) July 17, 2017
There are two ways, to sum up, numbers. Use basic addition if you’re working with only two or three numbers, as well as you want to add figures that are not on the same row. Otherwise, use SUM.
Formula: =CELL + CELL
Exercise: Add the working hours of Eric and Claire on Monday and put on your answer on CELL A9. To do that, go to the cell and input =C5+D5 (or =D5+C5). Note how numbers stay close to the symbols. Then press Enter. Try this yourself: add the working hours of Dan on Thursday and Fred on Friday and place it on CELL A10. You should get 17.
The subtraction formula follows almost the same structure as your addition, only that you’re using the minus (that’s a hyphen) on the keyboard.
Formula: =CELL – CELL
Exercise: Get the difference of working hours between Claire and Dan on a Tuesday and put your formula on A12. That should be =E3-D3 (=D3-E3). (Don’t worry if you’re going to get negative with the latter. It’s still the right answer.) Try this yourself: Get the difference between Fred’s and Eric’s work hours on Wednesday and place it on A13. You should get zero.
You’re almost done with the basic math operation. One of the Microsoft Excel formulas to learn is multiply. Again, there’s not much difference with the previous two formulas other than the symbols used. You don’t use an X but an asterisk instead.
Exercise: Multiply the working hours of Dan and Fred on Tuesday and Thursday, respectively, and place your answer on A13. The formula will be =E3*F5 (or =F5*E3). You should get a product of 88.
Dividing numbers is easy in Microsoft Excel. Like multiplication, you don’t use the standard division symbol but rather a forward slash (/) to make your calculations.
Exercise: Divide Dan’s working hours on Tuesday over the total working hours for the day. Place your calculations on A14. That should be =E3/B3. Note how you cannot interchange the cells this time.
— Microsoft Excel (@msexcel) June 9, 2017
SUM is a function, but it becomes one of the helpful Microsoft Excel formulas once you identify the variables such as cells. As its name suggests, it adds all the numbers in a given series, which can be rows or columns, or a series of random numbers.
Formula: =SUM (CELL:CELL)
Exercise: Determine the sum of the working hours for the week. To do that, go to B7 then type =SUM(B2:B6). You can also select from the drop-down menu. You should arrive at 40. Now get the sums of the remaining columns, which represent the total working hours for all four employees.
The average represents the mean of a series of numbers. To get it, you need to add the numbers you wish to find the average and divide the sum by the total identified numbers. For example, if you have 8, 9, 10, and 11, the sum is 38, but the average is 9.5 since you divide 38 with 4. In Microsoft Excel, AVERAGE is a function, and using it is similar to SUM.
Formula: =AVERAGE (CELL:CELL)
Exercise: Get the average of the working hours of the four employees. Place your answer on B8. That should be =AVERAGE(C7:F7) or =AVERAGE(C7,D7,E7,F7). The answer must be 39.5.
CountIF is one of your best friends when you need to determine the frequency of the value over a given set of data. Just imagine having to look for the number 20 or words like “get” over hundreds of information. That would take a lot of time, so use this as your shortcut.
Formula: =COUNTIF(RANGE, VALUE)
Exercise: Determine the number of times these four employees met the required 8-hour work time without manually counting. To do this, go to A15 then type =COUNTIF(C2:F6, “8”). Now try the same method for looking the number of times both Dan and Claire reported to work for 8 hours.
To determine the highest or lowest value in any given column or row is easy – if you deal with only a few numbers like the example we have. It becomes time-consuming and prone to errors if you have, say, hundreds of them. Fortunately, there’s a shortcut, and it’s the MIN (or MAX) function. The MIN function tells you the lowest value; MAX, the highest.
Formula: =MIN(CELL:CELL), =MAX(CELL:CELL)
Exercise: Determine the highest value (working hours) of Dan. Go to C12 and type =MAX(C2:C6). Now know the MIN. Go to C13 and type =MIN(C2:C6). Do the same with the rest.
If MIN/MAX functions tell you the lowest and highest values, respectively, COUNT informs you how many numbers are in every column or row. Again, this is useful when you’re working on hundreds of entries and you want to make sure your entries match your raw data.
Exercise: Know how many working hours you put in per day for Claire. To do this, go to D12 and type =COUNT(D2:D6). That should give you 5. Now let’s add one more entry for Saturday and write 6 for Claire. Did the COUNT answer change? It should already be 6.
— Excel Efficiency (@ExcelEfficiency) July 7, 2017
A hyperlink allows you to link a word or number to a website URL or email address. The conventional way is to click the cell you want to link, press Insert, look for the Hyperlink icon, and add the URL or email address. But that’s a lot of clicking. You can shorten the process using the HYPERLINK function.
Formula: =HYPERLINK(LINK LOCATION, FRIENDLY NAME)
Exercise: Let’s say each of the names above has an email address, and you want to link both. Let’s start with Eric. Go to A11 and type Eric’s email address. Now proceed to C11 and type =HYPERLINK(A11, C1). You should be able to generate a hyperlinked name.
One of the most-used Microsoft Excel formulas uses Vlookup. To know how it works and how to do it, watch this video:
Is learning these Microsoft Excel formulas easy? The answer is it depends on you. These 7+ are just some of the hundreds of formulas you can make with these functions, and some of them are going to be difficult. But you can be an expert as long as you’re always open to learning and apply what you’ve learned.
If you have cool tricks on Microsoft Excel formulas, feel free to share them with us in the Comments section! Do you know you can download Excel on your Android? You can also get these Android apps for first-time users too.