Microsoft Excel is one of the most popular tools used professionally. The office and productivity tool has beyond 1.1 billion users - which is about 1 in 8 people worldwide. Although there is plenty of specialized software, many professionals analyze data and create reports using Excel. The reason is the huge popularity of the software. It's quite impossible to find someone who can't open or understand an Excel file or an office that doesn't have Excel installed.
However, many people still struggle with formulas and linger working on their files. So here is everything you need to know about Excel formulas and functions and how to use them to your advantage.
Contents:
An Excel formula is a mathematical expression (i.e., a combination of numeric values and operations, such as +, -, /, *, and others) that uses records from existing cells to calculate new values. The math behind a formula is entirely up to you.
An Excel formula always starts with the symbol "=". For example, "=A1+A2" is the formula that performs the sum of the two records in cells A1 and A2.
Excel formulas can work on particular cells (e.g., A1 and A2) or a single cell (e.g., A1). For example, "=100*A1" is the formula that performs the multiplication of the record in cell A1 by 100.
Unlike an Excel formula, which the user entirely defines, an Excel function is created by Excel developers to perform a particular task. In other words, functions are built-in formulas that you use precisely how they are without having access to the insight math.
A function has a name and a list of parameters given between brackets: function_name (parameter1, parameter2, and so on). Parameters may be values from a cell or range of cells or fixed values that dictate the function's behavior.
While you can write Excel formulas to perform any task, functions are limited to specific tasks, such as computing sums (e.g., SUM()), finding maximum and minimum values (e.g., MAX()), applying statistics (e.g., AVERAGE()), using logical operators (e.g., COUNTIF()), computing trigonometric expressions, and using financial terms. You can find all Excel functions in the Formulas menu.
The good news is you can incorporate multiple Excel functions in an Excel formula and create complicated expressions. Using functions saves you time and simplifies the mathematical expression of the formula.
Excel formulas transform the program from a records database into a data analysis tool. They allow you to compute values of interest, create statistics, spot trends, and use your data at its real value. Without formulas, Excel would be just a series of tables, and you would perform all the work manually, counting items, adding values, and so on. Formulas empower you to use mathematics and perform repetitive tasks in seconds.
To write an Excel formula, select the cell you want to host the result and start typing the formula without forgetting to begin with the symbol "=". When you finish typing, press Enter, and Excel will calculate the formula. You don't have to know the cells' number; you can select them from the spreadsheet while typing the formula.
If you need to incorporate functions, you can type the beginning of the function's name and let Excel provide a short list of matching function names or use the function library.
One of the best things about formulas is that you can easily carry them from one cell to another or even cover a range of cells with the same formula. For example, let's say you have two columns of numbers and want to add values from each row. You can type the formula "=A2+B2" in the first cell of the result column and then drag it over the rest of the column. Or you can copy and paste the formula from one cell to another. Excel will automatically adjust the cell numbers and add A2 and B2 in the first cell, A3 and B3 in the next one, and so forth.
To lock a cell number, add the "$" symbol in front of the number. Using the same example, the formula "=A$2+B$2" copied or dragged over the entire result column will add the same two numbers and produces the same result.
Excel formulas have a learning curve, but you don't have to know everything from the beginning. Learn a few functions that help you increase your performance and grow from there. Here are the most useful tips for writing your first Excel formulas right now:
The SUM () function adds the values given as parameters. You can use explicit cell numbers, ranges of cells, and numeric values. Instead of using the "+" operator multiple times, you can use the SUM () function.
Example: “=SUM(100, A2:A10, B2)”
Bonus: If you want to add up only values that match a particular condition (e.g., higher than a numeric value), try SUMIF() and SUMIFS() functions.
The PRODUCT () function multiplies all the values given as parameters. Similar to the SUM () function, you can use specific cell numbers, ranges of cells, or numeric values. It's faster and more efficient than using the "*" operator multiple times.
Example: “=PRODUCT(100, A2:A10, B2)”
When explaining the AVERAGE () function, we discuss about computing the arithmetic mean of the values given as parameters. It adds all the values and divides the result by their number. You can provide as parameters specific cells or ranges of cells.
Example: “=AVERAGE(A2:A10)”
Bonus: If you want to average only values that match a particular condition (e.g., smaller than a numeric value), try AVERAGEIF() and AVERAGEIFS() functions.
The MIN () and MAX () functions determine the minimum and maximum values in a range of numeric values. They are frequently used for statistics and finding patterns.
Example: “=MIN(A2:A14)”, “=MAX(A2:A14)”
The COUNT () function counts how many numeric values are in the range given as a parameter. It doesn't count blank cells and cells that contain another form of data than numeric.
Example: “=COUNT(A2:A14)”
Bonus: If you want to count cells with numeric values and blank cells, use the COUNTA() function. There are also functions for counting only empty cells, COUNTBLANK(), and counting cells that meet a particular condition, such as having a given text, COUNTIF().
The CEILING () function rounds up the value given as the first parameter to its near higher multiple of significance. The function has two parameters: the value you need to round up and the significance. The significance may be an integer or a real number.
For example, if you pay someone an amount of money and want to give them a round amount, you can set the significance to 1, the nearest integer higher than your value, or ten, the nearest multiple of 10 higher than your value.
Example: A2 = 40.213, “=CEILING(A2, 1)” is 41, “=CEILING(A2, 10)” is 50.
The FLOOR () function rounds down the value given as the first parameter to its near lower multiple of significance. Similar to the CEILING () function, the FLOOR () function requires two parameters: the value you need to round down and the significance, the value to which multiple you want to get.
Example: A2 = 41.213, “=FLOOR (A2, 1)” is 41, “=FLOOR(A2, 10)” is 40.
UPPER (), LOWER (), and PROPER () are text functions. They help you organize text cells, remove typos, and unify text styles.
The UPPER () function transforms all the characters of the text given as a parameter to uppercase letters. The LOWER () function does the opposite, converting all the characters to lowercase letters. The PROPER () function capitalizes all the words in the text given as a parameter. It's a great function when dealing with employee records and many names.
Example: "=UPPER("text")" produces "TEXT"; "=LOWER("My name is John")" produces "my name is john"; "=PROPER("mark twain")" produces "Mark Twain"
The CONCATENATE () function is also a text function. It puts together the texts given as parameters. For example, if you have the first names of your employees in one column and the last names in another, the CONCATENATE () function gives you the full names of your employees.
Example: "=CONCATENATE("Mark", "", "Twain")" produces "Mark Twain"
If you aren't sure of the quality of the text you use, the TRIM () function helps reduce the diversity by eliminating extra spaces between words and leaving just one space between them. The function provides structure and makes text comparison more efficient. It needs one parameter, which can be a text cell or a specific text.
Example: "=TRIM("This is a text with errors")" produces "This is a text with errors"
The LEN () function counts all the characters in the text provided as a parameter, whether it is a text cell, cell range, or a specific text. It includes spaces and special characters as well.
Examples: "=LEN("My text has 5 words")" produces 19
The IF () function is one of many logical functions of Excel. It evaluates the expression given as the first parameter and gives a result based on whether it is true or false. The results for each scenario are the second and third parameters of the function. For example, if you want to give a raise to people in the Sales department, the IF () function may provide a 20% higher outcome for people in this department than for the rest of the staff.
Example: “=IF(B2=”Sales”, C2*1.2, C2)”
The AND () and OR () functions are logical functions one uses when working with multiple conditions. The AND () function returns true when all the conditions are satisfied. The OR () function returns true when at least one of the conditions is satisfied. For example, the AND () function helps you find employees who work in the Sales department and have a salary higher than a certain amount. The OR () function helps you find the employees who work in the Sales department or any department as long as they have a salary higher than a certain amount.
Example: “=AND(B2="Sales", C2>=1000)”, “=OR(B2="Sales", C2>=1000)”
The VLOOKUP () function is a little bit more complicated but spares you from looking endlessly at a spreadsheet and moving data around. The function looks for a specific value in the table's left-most column and returns the value from a different column. For example, if your table contains employee names, departments, and salaries, the VLOOKUP () function helps you answer quick questions such as "In which department does Employee X work?", "How much does Employee Y earn?", and so on.
The VLOOKUP () function has four parameters: the value you want to search, the table where to look for answers, the index of the column that contains the result, and a parameter indicating whether you want a perfect match (FALSE) or an approximate match (TRUE).
Example: “=VLOOKUP("Employee 4", A2:C10, 2, FALSE)”
The IFERROR() function helps you avoid mistakes such as dividing by zero. It's a checkup you should do from time to time, especially when working with big volumes of data. The IFERROR() function tells whether the evaluated expression is valid or not. You specify the cell or range of cells you need to check and the value returned in case of error.
Exemplu: “=IFERROR(B2, "Error")”
The TODAY () function is one of Excel's many date and time functions. It returns the current date of your system and is useful when you need to have the current date displayed on the spreadsheet regardless of when you open it. Each time you open the spreadsheet, the date will be up to date. The TODAY () function has no parameters.
Example: “=TODAY()” returns 1/25/2024
Similar to the TODAY () function, the NOW () function provides the current state of your system. The NOW () function returns your system's current date and time. The function has no parameters.
Example: “=NOW()” returns 1/25/2024 11:04
A formula for subtracting dates comes in handy when you want to calculate someone's age or years of working for a company. You can use a formula created by you, such as "=YEAR(newer date) – YEAR(older date)" or use the more precise and straightforward DATEDIF () function.
In Excel, DATEDIF () computes the difference between two dates given as parameters and returns the result in years, months, or days as specified by the user. It has three parameters: the older date, the newer date, and one indicator for the returned value, "y", "m", or "d".
Example: “=DATEDIF(D2, TODAY(), "y")”
If an employee starts work at 9 AM and works for 6 hours, when does the employee leave the office? It's a simple question requiring you to add a numeric value to a time value in Excel. And if you have employees with different work schedules, working part-time, and living in different time zones, you need the formula to help you out.
The Excel formula is "=starting_time+hours_to_add/24".
Bonus: If you need to add hours and minutes to hours and minutes, you can use a simple "+" operator, but you have to change the format of the cells to h:mm.
If an employee starts work at 9 AM and finishes at 6 PM, having 30 minutes break in between, how many hours does the employee work? To answer this type of question, you can't rely solely on Excel functions and need to create formulas.
The Excel formula is "clock_out_time – clock_in_time – break_time" in the following conditions: clock_out_time and clock_in_time are formatted as time cells, break_time is formatted as h:mm, and the result is also formatted as h:mm.
Example: “=D2-C2-E2”
Tip: We expand on the most popular methods to calculate time in Excel: Age, elapsed time, Time, Text and other 9 formulas.
If you want to learn faster how to use Excel, these basic excel formulas and functions can help. We have created an XLS file that can be downloaded freely, where you can test out all the formulas in your context. Change the inputs with your employees, or specific values from your business. Testing these popular Excel formulas will help you go beyond the basics and help you gain the knowledge to deliver faster results using the office tools you use most.
Download the Excel file with 20 Formulas
Conclusion
Sometimes, you need Excel formulas and functions to manage and analyze your data. You create a few routines and templates and use them for all your work. Being organized and knowing the best tools Excel offers help you avoid repetitive tasks and mistakes. However, investing in specialized software is an idea to consider when things become too complicated, and the granularity of details is too intricate, and by human work, it will take too much time to reach the desired results.