15 Useful Excel Formula Cheat Sheet
The excel Spreadsheet has number of functions which can be used to help you greatly enhance your abilities when it comes to performing day to day maths operations. With excel, you no longer have to waste time doing things manually.
If you are new to excel, know that you can use it to find totals for any given column or a row of numbers and perform many more functions. To achieve this, excel uses formulas in cells to perform the different calculations. When starting a formula you need to know that every formula usually starts with an equal sign which can then be followed by a number, math operators and the functions.
In addition to this article you may also find this ebook about Microsoft Excel : 101 Secrets Of A Microsoft Excel Addict
Snapshot of The Cheat SheetPlease click on the image to see in bigger size.
Printable Useful Excel Formula Cheat Sheet (PDF Format)
For Just a TweetThe Fromdev Cheatsheet for excel formula is available totally free in exchange of a tweet.
As soon as you tweet we will redirect you to the download page and you will be able to downloaded the cheatsheet to your computer.
Reference OperatorsThese refer to a cell or a group of cells. Reference operators are divided into two types including: union and range. As for a range reference, it can be defined as the cells between and also includes the reference. It includes 2 cell addresses that have been separated by a colon. On the other hand, a union reference refers to 2 or more references. It is made up of 2 or more cell addresses that have been separated by a comma.
Excel Formulas That You Need To Familiarize Yourself With
SUMJust as the name suggests, you can expect the SUM function to help you add 2 or more numbers. For further assistance, you can also use the cell references together with the formula. For as long as there are numbers in the reference cells, you can have as many numbers as possible added together.
Example of SUM formula in excel
COUNTAThis function allows you to count the numbers of non-empty cells in a given range. What the function does is that it allows you to count cells that already have numbers together with any other characters found in the cells. The function works with all data types.
Example of COUNTA formula in excel
COUNTYou can use the formula function to count the numbers of the different cells in a range that have numbers in them. The formula cannot work without numbers.
Example of COUNT formula in excel
TRIMThis is a very useful function that was designed to help users get rid of any sorts of spaces found in cells. The only exception is with single spaces between words. Do not allow yourself to pull out data from your database with extra spaces because it can wreak havoc when it comes to comparing using IF statements or VLOOKUP's.
Example Excel TRIM formula
LENWhenever this function is included in a function, you can expect it to count the number of characters in that given cell. You need to note that this function also counts the spaces between words. If you do not need to count the spaces, do not use this function.
Example Excel LEN formula
VLOOKUPAnyone who has used excel for a while can attest to the fact that the VLOOKUP formula is the most used formula. What the formula really does is that it looks for a value that is found in the leftmost column of a table and goes ahead and returns a value in the same row from a column that you'd have specified.
Below example searches for “TextToSearch” in array of cells from A1 to B10, the last parameter FALSE is used to do exact match search.
Example Excel VLOOKUP formula
AVERAGEThis function will help you to calculate the average value of the series of numbers found in between the specified cells. In this case, the function will calculate the averages of the numbers found in the cells B1 to B3 and give you an optimal result.
Example Excel AVERAGE formula
MINThis function can be used to help one calculate the lowest number in a given series of numbers. In the example above, the formula will give you the lowest number found in the series between cells B1 to B3.
Example MIN in excel formula
MAXThe MAX function helps you to find the highest number in a given series of numbers. In the given example, the function will get you the highest figure in the series between cells B1 to B3.
Example MAX in excel formula
IF FunctionYou can use this function to help you determine whether a given statement is true or false which can then allow you to perform a specified action based on your results.
Example IF in excel formula
=IF(Criteria,True value,False value)
SUMIF FunctionMore or less similar to the IF Function. The only difference is that with the SUMIF Function, after finding the values of the set criteria, it will go ahead and Sum up the values of the related cells.
Example SUMIF in excel formula
Below example will do the sum of all number in A1 to A25 if they are greater than 10
COUNTIF FunctionThe function works the same way as the SUMIF Function with the exception that it counts the fields that match with one another in relation to the set criteria instead of summing them together.
Below example will do the count of all number in A1 to A25 if they are greater than 10
Example COUNTIF in excel formula
AND FunctionDubbed as a logical function, the AND function helps you to check specified multiple criteria and will give a TRUE value if say ALL the criteria are found to be TRUE. If that criterion is not met, it will return as false.
Below example will do logical AND operation of two conditions and return a boolean value. In this case it must return TRUE
Example AND in excel formula
OR FunctionThis function is similar to the AND Function. However, the OR Function checks multiple criteria but only requires ONE statement to be true for the entire statement to be TRUE.
Below example will do logical OR operation of two conditions and return a boolean value. In this case it must return TRUE
Example OR in excel formula
RIGHT, LEFT, MID FunctionAll these functions are great TEXT functions that are used for manipulating data in a given cell. With the function, you can use it to take any numbers/words in a given cell, pull it in the right or left from them and go ahead to put them in a new cell.
Example RIGHT in excel formulaExample to get Right part of text, below will return TEXT
Example LEFT in excel formulaExample to get Left part of text, below will return TEST
Example MID in excel formulaExample to get Middle part of text, below will return SOME