The existence of Microsoft Excel is now very helpful for humans to complete their work, especially in the field of data management of goods and prices. The best number management software made by Microsoft is indeed designed to calculate a variety of numerical and table-based data.
The Most Important Collection of Microsoft Excel Formulas 2019
For those of you who want to master Microsoft Excel for the benefit of work or study assignments, you don’t need to be complicated in knowing history or understanding. The key lies in understanding which ones are rows or columns and utilizing formulas to make calculations easier. You can also consider an advanced Excel training course.
You need to memorize it and understand it so that the effectiveness of using your Microsoft Excel increases.
One powerful way to master Excel formulas is to practice them directly. For that, Aygek will provide a list of the most important excel formulas used in the world of work and school so you can practice them directly.
Rows and columns in Microsoft Excel
Rows and columns are important units that you must memorize when using Microsoft Excel. Rows indicate numbers in the table, if number 1 means row 1, if number 2 means row 2 and so on.
For columns marked with letters, if letter A means column 1, if letter B means column 2, and so on. These rows and columns will become the main requirements for using Excel formulas.
A complete collection of excel formulas and their functions
Note: Before proceeding to the formula, there is one thing you need to know, namely the use of a semicolon (;) in the example formula below.
You can replace it with commas (,) if your laptop / PC / microsoft version supports using commas instead of semicolons. So you have to experiment first by trying a simple formula using commas or semicolons.
1. Excel If Formula
IF is an excel formula which is quite confusing because this formula functions to take one of two values based on a condition. The logic is the same as IF in the programming language, but the writing must match the format in Excel as follows.
IF formula : = IF (condition, [value_if_false_data], [value_iff_data_false]]
Example IF Formula :
= IF (G2> 89; “A”; IF (G2> 79; “B”; IF (G2> 69; “C”; “D”)))
The example formula above is classified as a multiple or storied IF formula where there are more than one conditions. The example formula above explained that if the Score (marked with G2) is more than 89, it will get a value of A. But if a score of more than 79 is less than 88 then it will get a value of B, and so on until the last if the value does not meet the number 89.79, 69 will automatically get a D.
2. Excel SUM Formula
SUM formula is a formula that is very easy to remember because this formula only serves to add numbers from one column to another column or can add more than two columns.
SUM formula : = Sum (lift1, [liftabel2], ..]
Example SUM Formula : = SUM (G2: G6)
The example above means adding the numbers from row 2 column G to row 6 column G.
3. Excel VLOOKUP formula
Lookup formula functions to find a data value in a column or a row vertically. What do you mean by this vertical where and how? If the data is arranged in a vertical format, then data retrieval is more suitable using Vlookup.
Excel vlookup formula is usually used to find out detailed data from a data such as finding out the basic salary of an employee or the number of employees of a particular employee.
Vlookup formula : = vlokup (lookup_value, lookup_array, [match_type])
Example vlookup formula : = VLOOKUP (B5; $ B $ 13: $ E $ 15; 2)
The above example has two basic tables where there are employee data tables and salary tables. Salary table here is already listed data from a company that is used to classify several employees. Well, for employee data tables still vacant only filled in number , goal , and name of the employee .
To be able to determine the basic salary of employees, we can use the vlookup formula by retrieving data from the salary table automatically.
Because the employee data table and salary table in the Gol column have relevant data, we can use that as a vlookup formula. For data B5 it is a marker to the goal column in the employee data table then for $ B $ 13: $ E $ 15
That means selecting the contents of the salary table, you can see the coordinates of the row and column. And the last number 2 at the end meant to choose column 2 in the salary table, namely Basic Salary .
4. Excel HLOOKUP formula
This formula has the same logic as Vlookup, the difference is in searching data that is arranged horizontally.
Vlookup formula : = hlokup (lookup_value, lookup_array, [match_type])
Example of the hlookup formula : = HLOOKUP (B5; $ B $ 13: $ E $ 15; 2)
5. Excel Trim formula
This formula serves to justify irregular text in the use of excessive spaces.
Example Trim Formula : = trim (“Ayman is Geek Most good techno”) or = trim (rows & columns_want_want_wings)
6. Proper Excel Formulas
The function of the proper formula is to fix text that has an irregular capital word. If there is more than 1 word for example “AyGek iToE NoMeR sThat’s a TeKNO Issue” then all of these words start with capital letters and replace capital letters in the middle or end letters into regular letters. The result is like this “Aygek Itoe Number One Techno Affairs
Examples of the Proper Formula : = proper (“AyGek iToE NoMeR is the Problem of TeKNO”) or proper (row & column_yang_ingin_dibenahi).
7. Excel Upper Formula
If the above changes some ordinary letters into capital letters, Upper changes the common letters to all capital letters. For example the word “GUM GuM nO PiSToel” becomes “GUM GUM NO PISTOEL”.
Example of the Formula Upper : = upper (“gUM GuM nO PiSToel”) or upper (rows & columns_want_want_wings).
8. Excel Lower Formula
The logic is the same as the Upper formula but the difference is that the Lower formula replaces all lowercase or all lowercase letters.
Example of the Lower Formula : = lower (“gUM GuM nO PiSToel”) or upper (rows & columns_want_want_wings).
9. Excel Average Formula
The average formula is useful for finding average values from data. It can be two data or more than two data which the usage is almost the same as SUM formula but the difference is SUM to add.
Formula Average : = average (lift1, [liftabel2], ..]
Example of the Average Formula : = Average (G2: G6)
10. Excel Max Formula
Max’s formula is used to find the highest value of a series of numbers selected. For example in the table, you fill in data 5, 8, 10, 20, 25. If you use the Max formula and select the five data then the end result is 25 because the highest value of the others.
Max formula : = max (lift1, [lift2], ..]
Example MAX formula : = max (G2: G6)
11. Excel Min Formula
Min is the inverse formula of MAX, so the Min formula looks for the lowest value. The logic is also the same as the MAX formula, you just copy the example formula.
Min formula : = min (lift1, [lift2], ..]
Example MIN formula : = min (G2: G6)
Those are some complete excel formulas and their functions start from the addition formula, looking for data to processing numbers. Good little extra for those of you who want to find a laptop that is suitable for low-cost office and very smoothly used for Microsoft Excel or Word can check about.