Basic Excel functions, often used in accounting


Working in Excel and proficient use of basic Excel functions, often used in accounting is one of the skills that accountants are required to have. With Microsoft Excel, there are many functions that can assist you in your daily accounting work. Let Taimienphi.vn list the most commonly used functions for accounting people in the following article.


In order to do accounting work, even when you were in the school chair, you contacted and started studying Excel. Perhaps, this is one of the most basic subjects and is compulsory for most of you study accounting. There are many functions in Excel, but only a certain amount will be considered Basic Excel functions, often used in accounting never mind.

Know how to use functions in accounting to help accountants work easier and easier

Basic Excel functions, often used in accounting

Here are 20 common Excel functions in accounting, and they are all basic functions, so you need to look carefully, remember to apply them in the job well.

Statistical function group

1. The SUM function

Use to sum all numbers in the selected range.

– Recipe: SUM (Number1, Number2, Number3 …)

– In which: Number1, Number2, Number3 … are the numbers that need to be summed.

2. The SUMIF function

The SUMIF function helps you to calculate the total value of selected cells with specific conditions, and the SUMIF function is often used in the month-end conversion business when you make general logs, calculate for import and export summary tables. chemical …

– Recipe: SUMIF (Range, Criteria, Sum_range) = SUMIF (Conditional container, Condition, Sum to be calculated)

– Inside:

+ Range: Is the sequence of numbers you want to determine the total.
+ Criteria: Is the condition you want to sum (be it expression, string or number)
+ Sum_range: The cells containing the value you need to sum

– For example:

= SUMIF (B2: B10, “Enter will produce results. Here you are calculating the total value in the selection from B2 to B10 provided that the value is less than or equal to 200)

3. AVERAGE function

This function calculates the average of the arguments entered.

– Recipe: AVERAGE (Number1, Number2, Number3 …)

– Of which: Number1, Number2, Number3 … are the numbers you need to enter the average value.

4. SUMPRODUCT function

This function is used to calculate the product of a range of cells, from which we sum the products.

– Recipe: SUMPRODUCT (Array1, Array2, Array3 …)

– Inside: Array1, Array2, Array3… respectively are the ranges of cells you want to calculate the product, from which we sum the products.

– Example: = SUMPRODUCT (A2: A8, B3: B8, C5: C19)

5. The MAX function

This function helps you return the largest number result in the entered range.

– Recipe: MAX (Number1, Number2, Number3 …)

– Inside: Number1, Number2, Number3… It is the sequence that you want to find the maximum value of.

6. LAGRE function

This function helps you find the kth largest number in the entered range.

– Recipe: LAGRE (Array, k)

– Inside: Array is the range to determine, k is the numerical rank you want to find, counting from the largest number.

For example: = LAGRE (D5: D21, 4) -> here you are looking for the 4th largest number in the range of cells from D5 to D21.

7. The MIN function

This is the function that returns the smallest number in the range you enter.

– Recipe: MIN (Number1, Number2, Number3 …)

– Inside: Number1, Number2, Number3… is the sequence you want to find the smallest value.

8. The SMALL function

Use this function to help you find the kth smallest value in the range you enter.

– Recipe: SMALL (Array, k)

– Inside: Array is the selected range, and k is the rank of the number you need to find from the smallest number.

9. The COUNT function

This is the data counting function of the cells containing numeric data in the range you enter.

– Recipe: COUNT (Value1, Value2, Value3 …)

– Inside: Value1, Value2, Value3… is the range or array of data you choose.

10. COUNTA function

Use this function to help you count the data of all the girls who contain the data.

– Recipe: COUNTA (Value1, Value2, Value3 …)

– Inside: Value1, Value2, Value3… is the range or array of data you choose.

11. COUNTIF function

This is a function that counts cells containing values ​​that satisfy a given condition.

– Recipe: COUNTIF (Range, Criteria)

– Inside:

+ Range is the data range you want to count.
+ Criteria is the condition for the cells to be counted

– For example:

= COUNTIF (A1: A50, “> 2000”) -> You use this formula to count all cells in the range A1 to A50 provided that the response is greater than 2000.

Search function group

12. VLOOKUP function

This is the return function used quite a lot for accountants, this VLOOKUP function helps you return the search value in the column given from the reference table with the database table in accordance with the search value.

If X equals 0, the search results are absolute, X equals 1 then the search results are relative.

– Recipe: VLOOKUP (Lookup Value, Table Array, Col_idx num, [range lookup]) = VLOOKUP (Lookup value, Reference table, Column to retrieve, X)

– Inside:

+ Lookup_value: The value used to search
+ Table_array: Is the table of values ​​detected to be in the Absolute address form (marked with $ in front by pressing F4)
+ Col_index_num: The order in which the columns are to be placed on the lookup table.
+ Range_lookup: Times the relative or absolute search range with TRUE = 1 (relative) and FALSE = 0 (absolute)

– For example: = VLOOKUP (E16, $ C $ 20: $ D $ 22,4,0) -> Here you are performing a search for the value equal to the value in cell E16 and taking the corresponding value in the fourth column .

13. HLOOKUP function

Using this function helps you search for values ​​like VLOOKUP, but by comparing with the value in the first row of the input reference table.

Recipe: HLOOKUP (Lookup Value, Table Array, Col idx num, [range lookup])

Logical function group

14. AND function

This is the function that will return TRUE if all of the function’s arguments are TRUE and return FALSE if there is at least one FALSE argument.

– Recipe: AND (Logical1, Logical2, …)

– Inside: Logical1, Logical2, … It is conditional expressions. The arguments you enter must be a logical value or an array / reference containing the logical value, otherwise #VALUE!

– If the function returns TRUE = 1 if the arguments entered are true, then the function returns FALSE = 0 if there are 1 or more arguments entered incorrectly.

– Example: = AND (D9> 0, D9

15. OR function

Similar to the And function, this function will return True if any value is True and return False if all are False.

– Recipe: OR (Logical1, Logical2 …)

– Inside: Logical1, Logical2… These are the conditional expressions. The function will return a value of True = 1 if any of the arguments entered are true, and it will return a value of FALSE = 0 if all of the arguments you entered are wrong.

– Example: = OR (D8> May 4, 67, D6> February 1, 2019)

16. NOT function

This function you use to reverse the value of the input argument.

– Recipe: NOT (Logical)

– Inside: Logical is a logical expression or a value

Conditional function group

17. IF function

This function helps you return the value 1 if the condition is true, the value is 2 if the condition is false. IF function is often used when accounting for employees’ payroll, personal income tax calculation, sales bonus for employees …

– Recipe: IF (logical-test,[value_if_true],[value_if_true]) = IF (Condition, Value 1, Value 2)

– For example:

= IF (C2> = 7, “CONTENT”, “FALSE”) = CONTENT

= IF (C2> = 9, “CONTENT”, “FALSE”) = FALSE

Math function group

18. Ham ABS

This function is also used quite commonly. This is the function that takes the absolute value of a number.

– Recipe: ABS (Number)

– Inside: Number is a numeric value, an expression / reference.

– Example: = ABS (B9 + 9)

19. The MOD function

Using this function helps you calculate the residual value of the division.

– Recipe: MOD (Number, pisor)

– Inside: number is the dividend pisor is the divisor.

20. PRODUCT function

This is the function for calculating the product of a series of numbers you enter.

– Syntax: PRODUCT (Number1, Number2, Number3 …)

– Inside: Number1, Number2, Number3… is the sequence you need to calculate the product.

So you’ve just Taimienphi.vn learn about how to use Excel basic functions, often used in accounting. The formulas and how to use these functions are not too difficult, especially for those who have been trained in accounting profession. Although it is not difficult, you should also save the formulas, as well as the definitions of these functions to use in the lookup, review the use later if you forget.

There are many other useful Excel functions, especially the basic Excel functions, you should also review and memorize formulas to calculate and apply for your work!

= 200 “)>

.

Add a Comment

Your email address will not be published. Required fields are marked *