Home20+ Most Popular Excel Formulas You Should Know

20+ Most Popular Excel Formulas You Should Know [Beginner Friendly]

You may be familiar with Microsoft Excel. This application made by Microsoft Corporation is the application with the most users in the world in 140+ countries. This application has a myriad of Excel formulas that can accurately present numbers and data.

The primary function of Microsoft Excel is the calculation, which is why it has been used by many users, both students and office workers. Despite other spreadsheet programs (competitors), Excel maintained its dominance and even became an industry standard.

Also Read: 5 Common Bookkeeping Mistakes on Small Businesses

It feels like Excel seems too good to be true. All you have to do is enter an Excel formula, and almost everything you need to do manually can be done automatically.

If you need to combine two sheets with the same data, Excel can do this. Do you need help with mathematics? Excel can do this. Are you using data from multiple cells? Excel can do this too.

In this article, you will understand the function of Excel in different professional fields. Then you’ll understand what formulas you need to know. Later, you will be able to apply automatic calculations to be used according to your needs.

The Uses of Excel in Professional Fields

Many job openings require candidates to master Excel. Not surprisingly, excel is needed by a wide variety of fields of work that require data collection.

To that end, we will present the following professions, which can benefit from the use of Excel, among others:

Accountancy

It is widespread for accountants to be able to use Excel. The field of accounting benefits from using Microsoft Excel in several ways, including making it easier to calculate corporate profit and loss, identify large profits over a period, calculate employee salaries, and more.

Also Read: Salary Slip: Understanding, Benefits, Format, Example

Mathematical calculations

The next advantage of using Microsoft Excel is mathematical calculations. Use calculations to find data from numbers, subtractions, multiplications, and divisions, as well as various other variations.

Data processing

In data management, there are helpful excel formulas, including managing statistical databases, calculating data, searching for median values, averages, and searching for the maximum and minimum values of data sets.

Graphic creation

Excel allows you to create graphics. Such as graphing population development data for one year, the graph of student library visits for one year, student graduation graph for six years, the graph of student number development for three years, and others.

Table operations

The last application is to be able to operate the table. With a total of 1,084,576 rows and 16,384 columns in Microsoft Excel, it will make it easier for you to enter data that requires a large number of columns and rows.

Excel Formulas
Source: versionmuseum.com

Complete Excel Formulas with Their Functions

In addition to the functionality and use of Microsoft Excel mentioned earlier, operating this Excel program requires Excel formulas. Each formula has a different function that you can use for a specific need.

Here are the Excel formulas you need to know, among others:

FormulasFunctional Description
SUMSumming
AVERAGELooking for Average Values
ANDFinding Value by comparison and
NOTFinding Value with Exceptions
ORFinding Value by Comparison or
SINGLE IFLooking for Values If Conditions ARE RIGHT/WRONG
MULTI IFFinding Values If Conditions ARE RIGHT/WRONG With Many Comparisons
AREASView The Number of Areas (Range or Cells)
CHOOSEView Selection Results Based on Index Numbers
HLOOKUPSearch for Data from a table organized in a horizontal format
VLOOKUPSearch for Data from a table arranged in an upright format
MATCHDisplays the position of a specific cell address
COUNTIFCounting the Number of Cells in a Range with specific criteria
COUNTACounting the Number of Filled Cells
CEILINGRound the number up
FLOORRounds numbers down
DAYLooking for The Value of the Day
MONTHSearching for the Value of the Moon
YEARLooking for Year Value
DATEGet a Date Value
LOWERChange text letters to lowercase
UPPERChange Text Letters to UpperCase
PROPERChange the Initial Character of Text To UpperCase
23 Excel Formulas

For more information about how to use the excel function formulas mentioned above, here is a detailed description, along with examples:

SUM

This Excel summation formula has the primary function of summing numbers in specific cells. However, it is also often used to complete a job or task quickly. 

To use SUM, first create a summation table and enter the summation formula below. For example, =SUM(G8:H8), or as shown in the image below:

Excel Formula: Sum

Then, if the SUM formula has been entered, press enters to specify the amount. As shown in the picture below:

Excel Formula: Sum 2

AVERAGE

The primary function of the AVERAGE formula or the average formula in Excel is to find the average value of a variable. The trick is to create a table for grades and then enter the AVERAGE formula to determine the student’s grade point average. For example, =AVERAGE(D2:F2), as in the following image:

Excel Formula: Average

If you enter the AVERAGE formula, press enters to see the result, shown in the image below.

Excel Formula: Average 2

AND

The AND formula function generates a TRUE value if all previously tested arguments are correct or can return a FALSE value if all arguments or answers are incorrect.

To determine TRUE or FALSE, you must create a table and enter THE FORMULA AND. For example, =AND(G7>I7).

This technique is usually used to help fill out questionnaires or answer question columns to speed up the value-setting process.

NOT

The NOT formula has the opposite function of the AND formula because it produces TRUE if the condition tested is FALSE and FALSE if the condition tested is TRUE.

The first step is to create a table and enter the FORMULA NOT to find out the results. For example, as shown in the diagram below, =NOT(G7>J7).

OR

The OR is the following excel formula will produce TRUE data if some given argument is correct. If all of the arguments presented are incorrect, the answer is FALSE.

A student’s average test score result is an example of how OR can use it in Excel. If it is less than 70, he must repeat the course; if it is greater than 70, the student graduates.

SINGLE IF

The IF function is to return a value that, when examined, is TRUE and another value that is visible as FALSE.

Not much different from the technique of using formulas in OR. It just seems more straightforward.

For example, when a student’s average grade is less than 75, that student does not graduate, and vice versa. How to create an IF formula is =IF(J8<75;” NOT GRADUATING”;” PASS”). To be clear, take a look at the following image:

Excel Formula: Single IF

After entering the formula, press enters, and the result will match what you ordered.

Excel Formula: Single IF 2

MULTI IF

This formula is a further lesson from SINGLE IF, but if SINGLE IF only uses only one condition with two options, on The MULTI IF uses two terms and three options.

A Double/Multi FORMULA is a type of logic-IF formula consisting of 2 or more IF in formula writing. If a single IF only needs 1 IF, for example =IF(B2=>=70;” Graduated”;” Failed”) then Double IF requires more than 1 IF, for example =IF(B2>=70;” Good”;IF(B2>=50;” Enough”;” Less”)).

So that the full Excel formula will provide Syntax on Double / Multi IF, among others:

=IF(Logical_Test; Value_If_True;IF(Logical_Test; Value_If_True; Value_If_False))

AREAS

You can use the usability of AREAS if you want to calculate the number of areas you want to select. The AREAS formula can use the formula =AREAS(G6:K8).

Excel Formula: AREAS

The result is one area. The results are based on the example in the image of selecting only one range of cells.

CHOOSE

The primary function of a CHOOSE formula is to display selection results based on index numbers or sequences in reference (VALUE) that contain text, numeric, formula, or range names. The image below shows an example of how to write the CHOOSE formula.

Excel Formula: CHOOSE

Meanwhile, the results of the CHOOSE formula are shown in the image below.

Excel Formula: CHOOSE 2

HLOOKUP

The HLOOKUP function displays data from tables arranged horizontally. The arrangement of tables or data in the first row should be based on small to large sequences or raising them. 

For example, the numbers 1,2,3,4… Or the letter A-Z. Sort by ascending the menu if you previously typed randomly. Cases like the ones listed below are examples.

Excel Formula: HLOOKUP

=HLOOKUP(Cells that contain packet types; Range cell table details costs; The location of the data column you want to retrieve;0).

VLOOKUP

The primary function of the VLOOKUP formula is almost identical to the HLOOKUP formula; The difference is that the VLOOKUP formula displays data from tables arranged in an upright or vertical format. The requirement is that the first row of the data table is prepared in small to large order or up.

For example: 1,2,3,4… Or the letter A-Z. For example: 1,2,3,4… Or the letter A-Z. Sort through the Ascending menu if you previously typed randomly.

MATCH

The MATCH function is one of the components of an Excel formula that you can use to perform lookups or reference data searches.

The MATCH function works by finding the relative position of a value in a range or array and generating a number that is the index of the cell’s relative position that contains the value it is looking for.

The use of the Excel MATCH function must follow the syntax rules, namely MATCH(lookup_value, lookup_array, [match_type])

To better understand, understand the image below:

Excel Formula: MATCH
Source: je-excel 

COUNTIF

COUNTIF is a helpful formula for counting the number of cells that have the same criteria. As a result, you no longer have to struggle with data sorting.

Assume you’re doing a survey. You want to know how many civil servants there are out of 20 respondents. As a result, you can use the following formulas:

Excel Formula: COUNTIF
Source: bitlabs

To get started, all you need to do is enter the range of cells you want to identify into the COUNTIF formula (B2:B21)

Then, enter the cell calculation criteria, namely “PNS” (must be in quotation marks).

This formula produces a result of 7. So, out of 20 respondents, seven were civil servants.

COUNTA

COUNTA has the function of counting the number of cells that contain numbers and the number of cells that contain anything. As a result, you can determine the number of cells that are not empty.

Assume that cells A1 through D1 are words and cells G1 through I1 are numbers. Therefore the use of the formula is =COUNTA(A1:I1)

Excel Formula: COUNTA
Source: bitlabs

That results in 7 because the empty cells are only E1 and F1.

CEILING

The CEILING formula can round the number UP at the multiple values of a specific number. The formula is CEILING (CELLS number; Multiples).

Misalnya, CEILING(G9;100). Then the number on Cells G9 will be rounded up with a multiple of 100.

FLOOR

If the previous use of the formula is to round to the nearest integer and above, the FLOOR formula is used to round to the nearest integer. The formula is almost identical to the one for CEILING, but with the addition of FLOOR.

The FLOOR formula is FLOOR (Number cell; Multiples).

DAY

The DAY formula is used to find the date type data’s day (in the numbers 1-31). Consider the DAY function (column B). The date data in column A will be extracted and converted into numbers 1-31, following the explanation through the image:

Excel Formula: DAY

After entering the formula =DAY(NUMBER), press enters and see the results below:

Excel Formula: DAY (2)

MONTH

Almost similar to the DAY formula, the use of the MONTH formula to search for months (in numbers 1-12) from date type data. For example, the use of the MONTH function (column K) of a column I date data, after extracting it, produces the numbers 1-12 as in the figure below:

Excel Formula: MONTH

YEAR

Meanwhile, there is a YEAR formula. The way you use this formula is similar to the way the previous two formulas were. The use of the YEAR formula takes years (in the range of 1900-9999) from date-type data.

DATE

The DATE formula has a function that allows you to get date data types by entering years, months, and days. The DATE function is the opposite of the DAY, MONTH, and YEAR functions, which describe month and year type data from their respective dates.

Year, month, and day data in the form of numbers combined with the DATE function generate data with date types, as shown in the figure below. Date formula writing image, namely:

Excel Formula: DATE

LOWER

The lower formula function converts all uppercase letters in the text to lowercase. To use the formula, type the LOWER command, and texts will convert the desired writing to lowercase.

For example, when implementing the LOWER formula with the formula =LOWER (Don’t Forget to Use HashMicro Software), the result is “don’t forget to use hashmicro software.”

UPPER

In addition to lower formulas, Microsoft Excel includes upper formulas. The use of UPPER is to convert all text containing lowercase letters into uppercase, the opposite of the LOWER function.

For example, if you type UPPER (hashmicro), your writing will be entirely in capital letters.

PROPER

Sometimes you forget to write sentences in all lowercase letters and no capital letters. So with the PROPER formula, you can capitalize on the first character in each word while keeping the rest. The formula is APPROPRIATE (text).

Conclusion

Although understanding Excel entirely takes time, you will be familiar with all the correct formulas and applications.

Excel formulas are beneficial for assisting in solving problems or activities involving numerical systems and computing. So, the use of various formulas from Excel to date is still a lot in various sectors.

In a company, Excel is overwhelming, especially in presenting data, statistics, and corporate finances such as salaries. The digital world is bringing about corporate transformation to provide more accessible information and payroll with automated systems.

HashMicro has a system that helps with all your human resource and employee administration tasks. With HashMicro’s HRM Software, calculates salaries, manages leave and attendance lists, reimbursement processes, and other operational activities in just seconds.

Produce reports accurately and comprehensively like thousands of large companies that have joined HashMicro. To check out more, click here.

Interest in getting savvy tips for improving your business efficiency?

Fadel Rasyidhttps://www.fadelrasyid.site
I see myself as an active and curious person. Constantly open to learning and growing with values. I never can't see the bright side of a situation — a content writer at HashMicro & a digital marketing enthusiast.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Get FREE Business Software Consultation via WhatsApp

Rachel-Panjaitan

Rachel Panjaitan
Typically replies within an hour

Rachel Panjaitan
Need Help?
Chat with our experts for a free consultation by WhatsApp.
1:40
+6587776207
×
Chat with Us