15 Most Useful Excel Formulas to Know
Get Ahead by LinkedIn News
We talk about leveling up, about geting ahead in your career and about excelling where you are right now.
Excel is a powerful spreadsheet program that allows users to manipulate and find data. The real strength comes from formulas, which streamline the process of adding values and sorting information. From SUM to VALUE, 15 easy-to-learn formulas can handle the bulk of what most users need.
Seasoned Microsoft Excel users know the program’s real power is in its formulas and functions. In the right hands, Excel formulas can take the task of entering data into a spreadsheet, turn it on its head and streamline the process.
What Is an Excel Formula?
Excel formulas are functions that tell Excel how to manipulate data. They always begin with the equal symbol (=), followed by operators and functions.
Operators are symbols that specify what kind of calculation a user wants to perform on a formula’s elements.
Excel treats cell contents that begin with the (=) as a formula. Other formula designations include beginning cells with plus (+) or minus (-) signs.
Note: It is possible to add a leading apostrophe (‘) to the cell. This allows the formula to present as text rather than the intended result.
Now that we have the basic syntax down, we can dive into those common Excel formulas most people use daily.
1. SUM
Sum functions — SUM() — are the first ones most Excel users need. The sum function adds up all values in a specified group or range. The most common syntax for a SUM formula is:
=SUM(value1, value2, etc.)
But what about adding numbers in cells as a row or column? In this case, a person can type the following formula into the desired cell:
=SUM(B3:E3)
For sequential sums (such as a column), a user can click the corner of a cell where they have applied the SUM formula and drag the cursor down. This fills the cells below with SUMs adding the appropriate numbers.
2. AVERAGE
The AVERAGE() function calculates the average of a selected range of cell values. An example of the correct syntax for this formula is:
=AVERAGE(A2, A3, A4)
3. MEDIAN
The MEDIAN function returns a median of a given set of numbers. The median is the number in the middle of this set or range.
MEDIAN function syntax can look like this:
=MEDIAN(C5:C10) or =MEDIAN(number1, [number2], …)
The MEDIAN function is helpful, but it comes with a few caveats. If a range or set includes an even number of elements, the median calculates the average of the two numbers in the middle. If an argument (the items in the set) contains text, logical values or empty cells, the formula ignores it. The formula does count cells with a value of zero.
A limitation to the MEDIAN function occurs when arguments either contain text that cannot be translated or point to cells with errors (such as improper formulas). In these cases, the MEDIAN function will return an error.
4. MIN and MAX
The MIN and MAX formulas calculate the smallest or largest numbers in a range. For contiguous cells in a row or column, the arrow next to the AutoSum button on the Editing tab allows users to quickly calculate min and max values.
If the cells do not fall in a contiguous row or column, the process is more complicated. In this case, users can use MIN, MAX, SMALL or LARGE functions. For example, the formulas below allow users to find their needed information for a given range (A3:A9):
5. COUNT?
The COUNT function counts a given number of cells that contain numbers. It also counts those numbers within the list of arguments. Excel users should use COUNT to find entries in a number field in a range or array of numbers.
The syntax for the COUNT function is as follows:
COUNT(value1, [value2], …)
This formula counts numbers, dates or text representations of numbers, including those enclosed in quotation marks (“3”). The formula does not count error values or items that cannot be translated into numbers.
Counting logical values, text or error values uses the COUNTA function. Counting only numbers that meet specific criteria uses the COUNTIF or COUNTIFS functions.?
6. TRIM
The TRIM function removes the spaces from text, excluding single spaces between words. A person may use TRIM when text from other applications has irregular spacing.?
The TRIM function works as follows:
=TRIM(sample text)
An example of the formula in practice is =TRIM(“ First? Name ”), which would then render the text in the cell as “First Name.”
7. CONCAT
The CONCAT function replaces the legacy CONCATENATE function and is helpful when users need to join two or more text strings into one string. It is a more complicated function than the previous ones on this list.
The syntax for this formula is as follows:
=CONCAT (text 1, [text2], …)
Use the CONCAT function to combine text fields to create full sentences, lists of names and more. For example, =CONCAT("I"," ","like"," ","turtles.") will return the sentence “I like turtles.”
CONCAT can also combine elements such as first and last names, as in =CONCAT(“Andrew,” “, Jones”) to return the result “Andrew Jones.”
8. Percentages
Calculating percentages in Excel does not use a specific function. Instead, users can find percentages by using the following formulas:
领英推荐
9. IF
One of Excel’s most popular functions, IF, allows users to compare a value and an expectation logically. IF statements typically have two results: true and false.
In practice, the syntax looks like this:
=IF(B3=”Yes”,1,2) where B3 = Yes and returns a 1. Otherwise, it will return a 2.
IF is a powerful function allowing users to evaluate text values and numbers. Users may also nest multiple IF functions within a cell to perform multiple comparisons simultaneously.
10. VLOOKUP
Another valuable function, VLOOKUP enables Excel users to find items in a table or array by range or row.
VLOOKUP syntax can be incredibly complex and require a few different pieces of information, including the following:
With the above items, an example of VLOOKUP syntax reads as follows:
=VLOOKUP(lookup value, range with lookup value, column number containing return value, [optional] TRUE for approximate match or FALSE for exact match).
The following screenshots provide examples of the VLOOKUP formula in action:
11. LEFT/RIGHT
The LEFT and RIGHT functions return variants (strings) that contain a specified number of characters from the left and right sides of a string. The syntax of this formula is as follows:
The above functions specify that text is a string of text containing the characters a user wants to extract. The num_chars value is optional and specifies the number of characters the user wants LEFT or RIGHT to extract.
12.? VALUE
The VALUE function converts text strings that represent numbers to that specific number. The text must be in any constant number, date or time formats the Excel program recognizes. The syntax for the formula is:
VALUE(text)
For example, VALUE(“$1,500”) returns a result of 1500.
VALUE is not necessary for many formulas because Excel usually converts text to numbers when necessary. The VALUE function primarily exists to provide compatibility with other spreadsheet programs.
13. SUMIF
Users may use the SUMIF function in Excel to find the sum of values in a range that meets specific criteria.?
The syntax for the SUMIF formula is as follows:
=SUMIF(range, criteria, [sum_range])
If a column contains numbers and the user only wants to add values that are larger than 5, they may use =SUMIF(C2:C11, “>10”).
IMAGE: [Sumif_image_1] and [sumif_image_2]
14. COUNTIF
COUNTIF is a statistical function that counts the number of cells which meet specific criteria. It might count the number of times a specific salesperson or city appears in a list.
The simplest form of COUNTIF syntax is =COUNTIF(range, criteria). For example:
Both range and criteria are required. Range can describe numbers, arrays, named ranges or references with numbers. The formula ignores blank or text values. Criteria describe numbers, expressions, cell references or text strings that determine the cells to be counted. While COUNTIF uses a single criterion, the COUNTIFS formula can use multiple criteria.
15. LEN
The LEN formula is a simple one that returns the number of characters in a specific text string. The syntax for this formula is as follows:
=LEN(text)
“Text” can be an actual string of characters or a cell containing them. See the images below:
Want to Learn More Excel Formulas?
Excel formulas increase the platform’s versatility and reduce time spent working with data. Plus, hundreds of them are at a user’s disposal. One of the best ways to learn the ins and outs of Excel formulas and functions is through online coursework that provides practical experience. LinkedIn Learning hosts more than 150 classes to help users learn how to use Excel to its fullest potential.
Top Takeaways
(Reporting by NPD)