Microsoft Excel is one of the most widely known spreadsheet software worldwide. It is known for its versatility, and it is used in every industry under the sun. However, a lot of users do their best to work with Excel, but they don’t know about all the basic Excel formulas. These work like codes that let Excel know what you want to do with the data you select from the spreadsheet.
Learning Excel formulas can be beneficial not only in your professional life, no matter what you work in, but it can also contribute to your personal life.
By learning about formulas, you learn that Excel is more than just a data holder. It can help you calculate, organize, and analyze data. Yes, it can help you with a budget for your company, or creating a database of your clients. But it can also help you organize your cash flow, make a calendar, or track your progress on personal tasks. Once you discover new ways of using Excel, you’ll see that the possibilities are endless.
Some of the most known types of formulas are:
Arithmetic formulas: These are your classic addition, subtraction, multiplication, and division.
Text Formulas: Help you format and manipulate text as you need.
Logical Formulas: The ones that help you prove if a statement is true or false and return a result based on it.
Statistic Formulas: These help you bring out information to analyze quantitative data.
Financial Formulas: Formulas that help you calculate financial information, such as the present value or the future value of a payment or investment.
Date and Time Formulas: Formulas that allow you to request or manipulate data related to dates and times of the date.
Lookup Formulas: These help you find and reference information that is located in other cells or even sheets.
Array Formulas: Formulas that allow you to perform calculations in arrays of data at once.
To use a formula, you should know what you can use on each and how to use them. Let’s first talk about the elements of a formula.
You can find four different elements in a formula:
These are already established calculations that you can use with a quick text command. Like SUM or CONCATENATE.
When you are using the information in other cells in a formula, like B2 or H12, you’re using a reference.
It’s the information added directly into the formula. Like 3 or “America”.
Lastly, these are the symbols that represent a specific action or operation. Like “+” or “^“.
To explain the formulas, we will use this dataset:
| A | B | C | D | E | |
| 1 | NAME | SURNAME | AGE | YEAR OF BIRTH | SEX |
| 2 | Natasha | Thompson | 23 | 2000 | F |
| 3 | David Arthur | Williams | 25 | 1999 | M |
| 4 | Nathan | Miller | 43 | 1981 | M |
| 5 | Alice | Johnson | 62 | 1962 | F |
It adds the values you insert to it, whether they are references or constants.
EX.
=SUM(C2;D2) Returns 2023
It gives you the average of the data you input to it.
EX.
=AVERAGE(C2:C5) Returns 38,25
These formulas help you count cells. COUNT lets you know how many are numeric values, while COUNTA returns the number of cells that are not blank no matter if they have a numeric, text, or any other value format.
EX.
=COUNT(A1:E5) Returns 8
=COUNTA(A1:E5) Returns 25
IF formulas help you get an output depending on a condition. I will give you output “A” if a condition is met and “B” if it isn’t. SUMIFS adds the values in a range that meets all the criteria specified in the formula. Finally, COUNTIFS counts the number of cells in a range that meets all the criteria specified in the formula.
EX.
=IF(C4>40;"Correct!";"Not really") Returns “Correct!”
=SUMIFS(C2:C5;E2:E5;"F") Returns 85
=COUNTIFS(C2:E5;"F") Returns 2
These are used to find the highest and lowest values in a range.
EX.
=MIN(C2:C5) Returns 23
=MAX(C2:C5) Returns 62
POWER is used to raise a constant or a reference to a specific power.
EX.
=POWER(C4;2) Returns 1849
These two round the constant or numeric reference you input to the nearest multiple of a specified value. CEILING will round it up and FLOOR will round it down.
EX.
=CEILING(C2;5) Returns 25
=FLOOR(C2;5) Returns 20
As its name suggests, it concatenates the content of two or more cells into one.
EX.
=CONCATENATE(A2;" ";B2) Returns “Natasha Thompson”
It helps you ensure that all conditions are met. It will return TRUE if all conditions are met and FALSE if there is even only one that is not met.
EX.
=AND(C4>30;C2>20) Returns TRUE
=AND(C5<30;C3>20) Returns FALSE
It helps you ensure that at least one of the conditions established are met. It will return TRUE if at least one of the criteria is met. If none of the criteria is met, It will return FALSE.
EX.
=OR(C4<30;C2<30) Returns TRUE
=OR(C5<30;C3>30) Returns FALSE
This function will help you get rid of the extra spaces on a string, which is the term used in Excel to refer to blocks of text. This will help you normalize text information to work better with it.
EX.
=TRIM(A3) Returns “David Arthur”
Both of these replace a portion of a text constant or reference with a new text value. REPLACE will change a certain number of characters in a string with a new text while SUBSTITUTE will
EX.
=REPLACE(A3;7;8;"Christian") Returns “David Christian”
=SUBSTITUTE(A3;"d";"r") Returns “Davir Christian”. Be aware that SUBSTITUTE is case sensitive. That’s why the first “D” in David wasn’t replaced.
These formulas let you input the current date and even time in which you’re accessing the spreadsheet.
EX.
If it was 3:05 pm of October 24, 2024, then:
=TODAY() Would return 24/10/24
=NOW() Would return 24/10/2024 15:05
Of course, when one begins to use a new software or app, some tips and tricks can always come in handy. Here are some tips to make your life easier at the beginning.
The first answer that may come to mind would be to google it, and you’re not wrong. But you can also figure it out without even leaving Excel. To discover what a function is for and how to use it, click on the fx icon while selecting the cell that holds the function you want to know about.
If you press it while there is no function on the selected cell, a dropdown will appear, and it will let you look for any function you need and will then explain to you how to use it.
Oftentimes, you have to use the same formula with multiple cells, and Excel is very aware of this. That’s why it has made it easier to drag the same formula on multiple cells with a simple click.
To do so, select the cell with the formula you want to drag, then grab it from the little square that appears in its lower-right corner and drag it for as many cells as you need.
To conclude, remember that, just like in any other situation, the best teachers when it comes to getting used to something new are time and practice. Take this article as your friendly starter pack to start discovering the wonder that Excel has to offer to anyone who starts using it. As time goes by, efficiency and productivity will come along the way. You’ll feel more comfortable with the program and will implement it in ways you would have never imagined once you give yourself the chance to do so. Don’t forget to enjoy the ride and be always curious about what else you can do.
