15+ Excel Basic Formulas for Admin, Must Be Mastered!

Did you know that according to data from Scottmax in 2019, a market study revealed that 54% of businesses worldwide use Excel? This statistics emphasize the important role of the software in the corporate domain, helping companies in data management, financial analysis, and others.

Meanwhile, in 2021, there were 4.6 million administrative professionals who used Excel. This shows that the mastery of the Excel formula for an admin is very important and cannot be ignored anymore.

But, do you already know what excel formulas are commonly used by an admin? If not, try to refer to the following Kitalulus article.

Excel formula for admin

In carrying out daily tasks, an admin will usually struggle with data. Therefore, an Excel formula is needed to simplify its work.

At least the following is the basic excel formula for the admin that you need to master.

1. If

The IF formula in Excel has a function to analyze the criteria on a data. Usually the use of this formula will produce answers ‘right/wrong’ or ‘graduated/not pass’.

As an admin, being able to master the IF formula is very important because it can help in data classification, data validation, determine status, and automate administrative tasks.

The IF formula itself is written as follows:

= If (logical_test,[value_if_true],[value_if_false])

Example:

You have employee value data and want to know whether they graduate or not.

Number	Mark
Andi	85
Budi	60
Image	90
Dodi	70

= If (b2> = 75, “graduated”, “not graduated”)

The result:

2. Amount

Sum is an Excel formula that functions to add up numbers from certain cells.

The satisfaction of this formula is useful for the admin to assist their tasks in calculating total data, recapitulation of large amounts of data, and helping further analysis by combining other formulas.

= Amount (number 1: number 2)

Example:

For example, you have sales data in column A and want to calculate total sales from A1 to A10.

NO	Wrapped
1	100
2	150
3	200
4	50
5	180
6	300
7	250
8	120
9	75
10	90

= Sum (A1: A10)

= 1,565

3. Average

The excel formula for other admins is Average, which has a function of calculating the average value. For example to find out the average value of the price of goods.

= Average (number 1: number2: …)

Example:

Calculate the average employee value.

Number	Mark
Andi	85
Budi	90
Image	75
Dodi	85

= Average (B2: B5)

= 84.5

4. Count

Count is an Excel formula that functions to count the number of cells that have data in a certain range. The use of this formula is very useful when you need to count the number of transactions or goods in the list.

= Count (value1;[value2]; …)

Example:

You want to find out how many employees served as managers.

Number	Department
Judith	Manager
Village	Supervisor
Budi	Manager
I in	Admin

= Countif (B2: B5, “Manager”)

The result is 2

Also read: 30+ Examples of Excel Admin Training Problems [Plus Jawaban]

5. Vlookup

The vlookup formula has a function to speed up the search and processing of data searching from the vertical table.

In the world of administration, this formula is commonly used to avoid data input errors, moreover this formula can connect several sheets or databases in Excel so as to facilitate the making of reports from a large table.

= Vlookup (lookup_value, table_array, col_index_num, range_lookup)

Example:

Check the customer payment status based on the number invoice.

Invoice number	Number	Status
Inv0001	And	Repair
Inv0002	Image	Not yet paid off
Inv0003	Budi	Repair

If the admin type the invoice number in E2 cells, use:

= VLOOKUP (E2, A2: C4, 3, False)

If E2 = Inv002, the results have not been paid off.

6. Holuck

If Vlookup is used to search for data vertically, the Hlookup formula is used to find data horizontally in the table.

The use of this formula helps the admin manage data arranged in the form of rows (line), not column. Suitable for analyzing sales, attendance, or finance reports in row format.

= Hlookup (lookup_value, table_array, col_index_num, range_lookup)

Example:

Looking for employee salaries based on positions in the following table:

Department	Admin	Supervisor	Manager	Director
Wages	5,000,000	8,000,000	12,000,000	20,000,000

If the admin wants to find a salary based on a position typed in cell B4, use:

= Hlookup (B4, A1: E2, 2, False)

If B4 = Manager, the result is 12,000,000.

Also read: Easy Ways to Use Vlookup and Hlookup Formulas in the World of Work

7. Sumif

Sumif is still related to Sum, but Sumif has a function to add numbers from certain cells with additional certain criteria. These criteria can be numbers, text, comparison symbols, or even cell references.

= Sumif (range, criteria, [sum_range])

Example:

You want to add up employee salaries based on the “Manager” position.

Number	Department	Wages
Budi	Manager	IDR 10,000,000
Image	Supervisor	IDR 8,000,000
Andi	Manager	Rp. 12,000,000

If you want to calculate the total salary of the manager:

= Sumif (B2: B4, “Manager”, C2: C4)

The result: 22,000,000

8. Countif

This formula is useful for counting the number of cells in the range that meets certain conditions. As an admin, the country formula is very helpful to count the number of employees in certain positions, the number of invoices that have not been paid off, the number of products with low stock, and others.

= Countif (range, criterion)

Example:

Want to find out customer data from Jakarta

Number	Origin
Brian	Jakarta
Sari	Bandung
Ridwan	Jakarta
Budi	When

= Countif (B2: B5, “Jakarta”

The result: 2

9. Round

Round is used to round numbers to a certain digit amount. This formula is very useful to ensure numbers in financial statements, payroll, or other calculations appear neat and easy to read, without many decimal numbers.

= Round (numbers, num_digits)

Example:

You want to display the calculation results with 2 numbers behind the comma.

1234,5678

8765,4321

2345,6789

= Round (A2, 2)

The result:

• 1234,5678 → 1234.57
• 8765.4321 → 8765.43
• 2345,6789 → 2345.68

10. and & or or or or

The Formula and or or is used to combine several logic conditions in one formula and produce true or false results. Both of these formulas are very useful for analyzing data involving several conditions that need to be met at once.

For example, if you want to know whether the data meets several conditions at once, such as:

• And: all conditions must be correct to get true results.
• OR: One of the conditions must be correct so that the results are true.

Formula and

= And (logical1, logical2, …)

Formula or

= Or (logical1, logical2, …)

Example:

Number	Age	Old job
Andi	39	4 years
Budi	36	6 years
Barrel	40	9 years

You want to check an employee for more than 30 years old or have worked for more than 5 years.

= Or (B2> 30, C2> 5)

Andi: False (because of the age of more than 30 and works more than 5 years)

Budi: True (because of the age of less than 30 and works less than 5 years)

11. Concatenate

Concatenate or Textoinjin is an Excel formula that functions to combine text from two or more cells.

With this formula, you can combine information from several columns into one more structured column, such as combining the first name and last name in one column.

= Concatenate (text1, text2, …)

Example:

First name	Last name
WISE	Santosa
Dinda	Kirana
Andi	Sadikin
Image	Larasati

= Concatenate (A2, “”, B2)

The result:

• Arif Santosa
• Dinda Kirana
• Andi Sadikin
• Larasati image

12. Max and Min

This formula will help the admin in finding the biggest and smallest value in the data series. Admin used to use this formula when looking for the highest or lowest number in reports, financial data, or statistics.

Max formula

= Max (Number1, Number2, …)

Formula min

= Min (number1, number2, …)

Example:

You want to know what the difference between the highest and lowest sales of the following table data:

Month	Sale
January	15
February	20
March	25
April	18

= Max (B2: B5) – Min (B2: B5)

= 10

Also read: 50 Excel formulas that are often used by work and examples

13. Index and Match

This formula is often used as a substitute for the vlookup or hlookup formula, because it is more flexible in data search.

The index formula is used to take values ​​in a certain position in a range or array based on the specified rows and columns. While the match formula is used to find the position of a value in the range of data, and restore the relative position number.

Formula index

= Index (array, row_num, [column_num])

Match formula

= Suitable (lookup_value, lookup_array, [match_type])

Example:

For example, you want to find the age of the image in the following table data:

Number	City	Age
Follow	Jambi	39
Sincere	Jakarta	35
Image	Surakarta	29

= Index (C2: C4, Match (“Image”, A2: A4, 0))

The result is 27 (because the image is in the third row, and age is in column C).

14. Subtotal

The function of the subtotal formula is to calculate data in a cell range, such as the amount, average or a lot of data, more flexible.

This formula is very useful if you are processing data that is often filtered, because this formula only calculates visible data, so the data you are hiding will be ignored.

= Subtotal (function_num, range)

Function_num: Numbers that represent the type of calculation you want to do. Here are some of the commonly used codes:

• 1 = average (installments -rata)
• 2 = count (counting the number of numbers)
• 3 = Counta (calculating the number of non-medical values)
• 9 = Sum (number)
• 10 = min (smallest value)
• 11 = Max (the biggest value)
• 101 = average (ignoring hidden lines)
• 102 = count (ignoring the hidden line)
• 109 = number (ignoring hidden lines)

Example:

= Subtotal (9, A1: A10): Calculate the total amount of range A1 to A10.

15. Iferror

You can use this formula to overcome errors in other formulas. So, if there are errors, such as division with zero, or wrong cell references, Iferror can replace it with other values ​​that are more suitable, such as text or certain numbers.

= IFERROR (VALUE, VALUE_IF_ERROR)

Example:

= IFERROR (A1/B1, 0)

If there is an error, this formula will display the number 0, not an error message.

16. Left, right, and mid

The excel formula for the admin is used when you want to choose text from certain parts in the cell, for example taking the first name, product code, or part of the email address. Left chose text from the beginning, right from the end, and mid from the middle.

Left dirty

= Left (text, [num_chars])

Example:

= Left (“Excel”, 2)

Results: “ex”. This will take the first 2 characters of the word “Excel”.

The formula is true

= Correct (text, [num_chars])

Example:

= Right (“excel”, 3)

Results: “Cel”. This will take the last 3 characters of the word “Excel”.

Rumm

= Mid (text, start_num, num_chars)

Example:

= MID (“Excel”, 2, 3)

Results: “xce”. This will take 3 characters starting from the second position in the word “Excel”.

17. top and bottom

The upper formula is used to convert the entire text in a cell into uppercase. While lower is used to convert the entire text into lowercase letters.

Top formula

= Top (text)

Example:

= Top (“Excel”)

Results: “Excel”. Change all the words “Excel” into capital letters.

Lower formula

= Lower (text)

Example:

= Lower (“Excel”)

Results: “Excel”. Change all the words “Excel” into lowercase.

After knowing what are the excel formulas for the admin, now you are more confident right when applying for work?

Let’s find the admin locker that is being opened through the Kitalulus application! You can filter based on the fields of work, background, location, even salary.

To be more confident, before applying for you can take advantage of the features for CV Online from Kitalus to create CV ATS friendly So that your profile is glimpsed by HR.

Come on, install Kitalulus application at smartphoneYou now!

Source: