Neicy Techno – Excel Functions List for Beginners, The function formulas that will be discussed are basic Microsoft Excel functions that are often used in the world of work or daily activities in using Excel applications.
Before discussing the basic functions of Excel, here I will explain the meaning of functions in Microsoft Excel. You have to understand the basic material to make it easier to use the function.
Daftar
Explanation of formulas and functions in Microsoft Excel
Maybe you already understand the meaning of formulas and functions in excel. The two have a slight difference which refers to the writing. Formulas are written manually such as =2*3, while functions use formula templates such as SUM, COUNT, and others.
Excel formulas and functions are the same things. However, the writing is slightly different. Here you can use both as needed, Sometimes you have to write formulas manually and use functions as shortcuts.
Benefits of Functions in Excel
There are several benefits that you can get when using Functions in Excel. You can be facilitated in writing logic, calculations, or other activities. In general, there are several benefits that are often felt by users, namely:
Accurate
By using functions, you can easily perform calculations accurately and clearly. Usually, the data entered into Excel is always a lot. With that much data, you need functions to tidy up or perform calculations perfectly.
Data Visualization
You can visualize the data to be more interesting. That way, you will have more value when making presentations.
Manage Data
Sometimes the data entered into Excel is always a lot. That way, you can sort or group data by category using functions. Of course, you will find it easier in the process of working.
Data processing
You can process a lot of data at once with maximum results. In other words, processing data in excel will be faster and easier.
easy and practical
You just need to enter the function formula in excel. To write a function, you can follow the instructions given by the system. Using this function is so easy that any person or user can use it very well.
How to Use Functions in Excel
To use functions in Excel, you can use them very easily. The first step is to write down the numeric data or the numerical calculation to be calculated. After that go to the Formula Tab and select the dropdown icon on AutoSum. There are some basic functions that you can use.
Apart from that, you can also use the following steps:
- Prepare data that you will use in excel. For example, use student test scores.
- Enter the value of the student starting from Cells A1:A10.
- In cell A11, enter the formula for this function:
=Average(A1:A10) - After that press ENTER. This function is used to find the average value.
Excel Function List (Complete for Beginners)
There are many basic functions that you should know. Especially for those of you who are just learning to use Microsoft Excel. Here I will discuss the basic functions that are often used in daily work.
SUM
SUM is used to add up the numbers in a particular cell. For example, suppose you want to add up the numbers from cells A1 to A11, then the formula is:
=SUM(A1:A11)
AVERAGE
AVERAGE is used to calculate the average value of numbers in a certain range of cells. For example, you want to calculate the average grade of students in cells A1 to A11, then you can use the following formula:
=AVERAGE(A1:A11)
MAX dan MIN
This formula is used to find the highest or lowest number in an excel data series. For example, you have a list of student grades written in cells A1 through A10. If you want to find the highest value, you can use this formula:
=MAX(A1:A10)
Meanwhile, if you want to find the lowest value. Then you can use this formula:
=MIN(A1:A10)
COUNT
The COUNT function is used to count the number of cells that contain numbers. For example, in cells A1 through A11, only A1 through A5 contain numbers. So, if it is calculated by the following formula:
=COUNT(A1:A11)
Then the result of the formula is 5 because only five cells contain numbers.
COUNTIF
COUNTIF is a formula used to count the number of cells with the same criteria. That way, you can easily search for data with the same criteria without having to sort manually.
For example, say you have a company employee data set. There are several job positions such as treasurer, secretary, leader, and employee. You can use the COUNTIF formula to search for any of these criteria.
Examples of use:
=COUNTIF(C4:C7,"employee")
The first step is to enter the data of the Secretary, Treasurer, Management, and Employees in Cells C4 to C7. After that, enter the formula =COUNTIF(C4:C7,”employee”) in cell C10 (or any other cell). Then the results will appear, there will be seen the number of criteria with the name “employee”.
COUNTA
COUNTA is used to count the number of cells that contain numbers and letters. It can be said that its function is to count the number of cells used, except for empty cells. So, empty cells will be ignored. For example, Cells A1 through A5 contain Numbers, and cells A8 through A10 contain Words. So, if it is calculated by the following formula:
=COUNTA(A1:A10)
Then the result will be 8 because Cells A6 and A7 are empty which means they don’t count.
SUMIF
SUMIF is used to accumulate numbers in cells with certain criteria. For example, you have company employee data. You plan to collect the total amount of expenses from all employees with administrative staff positions. to calculate it, Use this formula:
=SUMIF(A10:A18,"administrative staff",B10:B18)
Range A10:A18 is employee position data, “administrative staff” is the defined criteria, and Range B10:B18 is expense data for each employee in the company.
IF
The IF function is used to identify data based on certain logic. Usually in the form of true or false.
For example, you have 2 different numbers in Cells A1 and A2. Then you want to test the logic by coming up with the word True or False. If the number is greater, then the result is “True”, and if it is smaller, then the result is “False”. To test it, use the following IF function:
=IF(A1>A2, "True","False")
TRIM
The TRIM function is used to remove excess whitespace in cells. such as double spaces, spaces at the beginning of sentences, or at the end of sentences. This function can only be used on one cell only. So, you can’t use it on multiple cells at once, it has to be one at a time. Examples like this:
=TRIM(A2)
AND
AND is a logical function. You can use it to look up information about a cell, whether the value is TRUE or FALSE. Examples like this:
=AND(A1>100,A1<200)
If the number meets the criteria (A1>100 and A1<200), then the result is TRUE. Meanwhile, if it does not meet the criteria, then the result will be FALSE.
OR
The OR formula can be used to find information about a predetermined criterion, whether the criterion is TRUE or FALSE. In contrast to AND, which must meet all the criteria for the result to be TRUE. The OR function can be used even if only one of the criteria is met. As an example:
=OR(A1<100,A1>200)
If the number meets the criteria (A1<100 or A1>200), then the result is TRUE. Meanwhile, if it does not meet the criteria then the result is FALSE.
NOT
NOT is the opposite of the AND and OR formulas. With this formula, Results that meet the criteria will be FALSE. As an example:
=NOT(A1>100)
The result is “TRUE” if the number does not meet the criteria (A1>100). Meanwhile, if the number meets the criteria, the result is “FALSE”.
LEN
The LEN formula is used to count the number of characters in a cell. Examples of use:
=LEN(A1)
This function cannot be used on multiple cells at once. You can only use it in 1 Cell only.
AREAS
this formula can be used to calculate the number of areas that you make as a reference. Examples of use:
=AREAS(A1:C10)
VLOOKUP
The VLOOKUP function is used to retrieve data that is arranged vertically. You can retrieve data from other tables for reference or other needs. The formula is generally written like this:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
HLOOKUP
HLOOKUP is used to retrieve data arranged horizontally. You can take references from other tables or for other purposes. Writing the formula like this:
=HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
CHOOSE
The CHOOSE formula is used to retrieve values from a data table. You can use the CHOOSE function to recap data from another table. For example, if you have item data along with item number. Then you can use the CHOOSE formula to bring up the item name data just by writing the item number. Writing the formula like this:
=CHOOSE(index_num, value1, value2, …)
For example:
=CHOOSE(C5,B2,B3)
MATCH
The MATCH formula can be used to find data in a specific row. For example, you have computer equipment data from rows A1 to A10. Then you can find any of the data using the MATCH formula. Examples like this:
=MATCH("computer",A1:A10)Using this formula, you can search for an item with the name “computer” and provide information about the row location.
CEILING
The CEILING formula is used to round a number (a multiple of ten) to the nearest top number. Examples of use:
=CEILING(C4, 10)
As you can see, in the CEILING formula example above, there is cell C4, which is the cell we want to round the number to. While the number 10 is the limit you set. So you will round the (maximum) number to be 10 numbers higher than the original number.
FLOOR
The FLOOR function is used to round a number (a multiple of ten) to the nearest lower number. Examples of use:
=FLOOR(C4, 10)
Excel Function List for Beginners (Complete). The list of Excel functions above is the basis for using function formulas. You can use it very easily and clearly. After understanding the basic functions above. I think you are ready to take excel functions to the next level.
Tag: Excel Function List
- How to Create a dropdown in Excel - How to add Google Maps and Website in Smart Apps Creator - 15 Cara Menjumlahkan di Excel - Mengubah Nama Wifi dan Kata sandi Tplink W8961