Top 20 Excel Formulas Every Job Applicant Should Know: Build Practical Skills with PW Gyaan-E

Excel is used across roles in sales, HR, finance, operations, administration, and data handling. At work, you may need to calculate expenses, check attendance, find employee details, compare sales figures, or clean customer records. Excel formulas help you handle these tasks without calculating or checking every entry manually.

For job applicants, the goal is not simply to memorise formulas but to understand where each one fits into an actual spreadsheet task. We cover 20 useful Excel formulas with workplace examples, while the Microsoft Excel Formulas: Mastery Course by PW Gyaan-E can help you take these skills further through formulas, lookups, data tools, and practical Excel tasks.

Why Should Job Applicants Know Excel Formulas?

Excel requirements vary between roles, but formulas are useful whenever a job involves working with data. They can help you:

• Calculate figures: Find totals, averages, highest values, and lowest values quickly.

• Apply conditions: Count or calculate records that meet specific criteria.

• Find information: Retrieve employee, customer, product, or transaction details from tables.

• Clean data: Remove unwanted spaces or extract useful information from text.

• Handle errors: Keep reports readable when information is missing or a formula returns an error.

• Prepare reports: Turn raw spreadsheet entries into information that is easier to review.

These are also the types of spreadsheet skills that PW Gyaan-E builds on through its Excel course, moving from formulas and conditional calculations to lookups, data analysis, and other workplace applications.

Top 20 Excel Formulas Every Job Applicant Should Know

The following formulas cover calculations, conditional analysis, lookups, text handling, and data cleaning. They also provide a useful foundation before moving to topics such as Pivot Tables, charts, Dynamic Arrays, and automation covered in the PW Gyaan-E Microsoft Excel Formulas: Mastery Course.

1. SUM

• What it does: Adds numbers in selected cells.

• Syntax: =SUM(number1, [number2], ...)

• Simple example: If B2:B6 contains monthly sales figures:

=SUM(B2:B6)

This gives the total sales for the selected period.

• Where it is useful at work: Sales totals, expenses, budgets, invoices, and inventory quantities.

2. AVERAGE

• What it does: Calculates the average of a group of numbers.

• Syntax: =AVERAGE(number1, [number2], ...)

• Simple example: If B2:B5 contains performance scores of 70, 80, 90, and 80:

=AVERAGE(B2:B5)

The result is 80.

• Where it is useful at work: Average sales, performance scores, expenses, ratings, or response times.

3. COUNT

• What it does: Counts cells that contain numbers.

• Syntax: =COUNT(value1, [value2], ...)

• Simple example:

=COUNT(B2:B20)

This counts the cells containing numerical values in the selected range.

• Where it is useful at work: Counting transactions, scores, quantities, or other number-based records.

4. COUNTA

• What it does: Counts non-empty cells, including those containing text or numbers.

• Syntax: =COUNTA(value1, [value2], ...)

• Simple example:

=COUNTA(A2:A20)

If column A contains employee names, this counts how many names have been entered.

• Where it is useful at work: Employee lists, customer databases, task trackers, and registration sheets.

5. MAX

• What it does: Returns the largest number in a range.

• Syntax: =MAX(number1, [number2], ...)

• Simple example:

=MAX(C2:C12)

If column C contains monthly sales, this returns the highest sales figure.

• Where it is useful at work: Finding the highest sale, largest order, maximum expense, or top score.

6. MIN

• What it does: Returns the smallest number in a range.

• Syntax: =MIN(number1, [number2], ...)

• Simple example:

=MIN(D2:D20)

If column D contains stock quantities, this shows the lowest quantity recorded.

• Where it is useful at work: Identifying low stock, minimum sales, lowest costs, or other minimum values.

7. IF

• What it does: Checks a condition and returns one result if it is true and another if it is false.

• Syntax: =IF(logical_test, value_if_true, value_if_false)

• Simple example:

=IF(B2>=50,"Pass","Fail")

If B2 contains 65, the result is Pass.

• Where it is useful at work: Marking targets as achieved, checking eligibility, assigning status, or categorising records.

8. SUMIF

• What it does: Adds values that meet a specified condition.

• Syntax: =SUMIF(range, criteria, [sum_range])

• Simple example:

=SUMIF(A2:A20,"Sales",B2:B20)

If column A contains departments and column B contains expenses, this totals expenses for the Sales department.

• Where it is useful at work: Department expenses, category-wise sales, product revenue, and other conditional totals.

9. COUNTIF

• What it does: Counts cells that meet a condition.

• Syntax: =COUNTIF(range, criteria)

• Simple example:

=COUNTIF(C2:C30,"Present")

This counts employees marked Present.

• Where it is useful at work: Attendance, completed tasks, order status, customer categories, and similar records.

10. AVERAGEIF

• What it does: Calculates the average of values that meet a condition.

• Syntax: =AVERAGEIF(range, criteria, [average_range])

• Simple example:

=AVERAGEIF(A2:A20,"Sales",B2:B20)

If column A contains departments and column B contains performance scores, this returns the average score for the Sales department.

• Where it is useful at work: Comparing average sales, performance, costs, or ratings for a particular group.

11. VLOOKUP

• What it does: Searches the first column of a table for a value and returns related information from another column.

• Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• Simple example:

=VLOOKUP(E2,A2:C20,3,FALSE)

If E2 contains an employee ID, Excel searches column A for that ID and returns the corresponding value from the third column.

• Where it is useful at work: Employee records, product prices, customer information, and other table-based lookups.

12. XLOOKUP

• What it does: Searches one range for a value and returns the corresponding result from another range.

• Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

• Simple example:

=XLOOKUP(E2,A2:A20,C2:C20,"Not Found")

Excel searches column A for the value in E2 and returns the corresponding entry from column C.

• Where it is useful at work: Employee databases, inventory records, price lists, and customer data.

13. INDEX

• What it does: Returns a value from a specified position within a range.

• Syntax: =INDEX(array, row_num, [column_num])

• Simple example:

=INDEX(B2:B10,3)

This returns the third value in the selected range.

• Where it is useful at work: Retrieving information from datasets, particularly when combined with MATCH.

14. MATCH

• What it does: Finds the position of a specified value within a range.

• Syntax: =MATCH(lookup_value, lookup_array, [match_type])

• Simple example:

=MATCH("EMP105",A2:A20,0)

This returns the position of EMP105 in the selected range.

• Where it is useful at work: Locating employee IDs, product codes, customer names, or other records.

• INDEX and MATCH can also work together:

=INDEX(B2:B20,MATCH(E2,A2:A20,0))

• MATCH identifies the required position, while INDEX returns the corresponding value.

15. CONCAT / CONCATENATE

• What it does: Combines text from multiple cells. CONCAT is the newer function, while CONCATENATE may still appear in older spreadsheets.

• Syntax: =CONCAT(text1, text2, ...)

• Simple example:

=CONCAT(A2," ",B2)

If A2 contains Neha and B2 contains Sharma, the result is Neha Sharma.

• Where it is useful at work: Combining names, addresses, labels, codes, or other separate text fields.

16. LEFT

• What it does: Extracts characters from the beginning of a text value.

• Syntax: =LEFT(text, [num_chars])

• Simple example:

=LEFT(A2,3)

If A2 contains DEL-1025, the result is DEL.

• Where it is useful at work: Extracting location codes, department abbreviations, and ID prefixes.

17. RIGHT

• What it does: Extracts characters from the end of a text value.

• Syntax: =RIGHT(text, [num_chars])

• Simple example:

=RIGHT(A2,4)

If A2 contains INV-2026-1045, the result is 1045.

• Where it is useful at work: Extracting invoice numbers, product codes, or information stored at the end of an entry.

18. MID

• What it does: Extracts a specified number of characters from the middle of a text string.

• Syntax: =MID(text, start_num, num_chars)

• Simple example:

=MID(A2,5,3)

If A2 contains EMP-SAL-105, the result is SAL.

• Where it is useful at work: Separating department, location, or category codes stored within IDs.

19. TRIM

• What it does: Removes unnecessary spaces from text while keeping single spaces between words.

• Syntax: =TRIM(text)

• Simple example:

=TRIM(A2)

If A2 contains a customer name with unwanted spaces, TRIM cleans the entry.

• Where it is useful at work: Cleaning imported employee, customer, supplier, or survey data before using it.

20. IFERROR

• What it does: Replaces an Excel error with a result you specify.

• Syntax: =IFERROR(value, value_if_error)

• Simple example:

=IFERROR(VLOOKUP(E2,A2:C20,3,FALSE),"Not Found")

If VLOOKUP cannot find the required record, Excel displays Not Found instead of an error.

• Where it is useful at work: Keeping reports readable when lookups or calculations encounter missing or invalid data.

Once you are comfortable with these 20 functions, you can start combining them to solve larger spreadsheet problems. PW Gyaan-E builds on this foundation with SUMIFS, COUNTIFS, lookup techniques, Pivot Tables, charts, Dynamic Arrays, and automation tools.

How Can You Practise Excel Formulas for Real-World Jobs?

Instead of practising formulas with random numbers, create small datasets based on information you might actually handle at work.

Once the sheet is ready, give yourself questions to solve without being told which formula to use. This is also where the practical exercises, quizzes, and final project included in the PW Gyaan-E Excel course can help you move from recognising formulas to applying Excel skills to complete tasks.

How Can PW Gyaan-E Help You Go Beyond Individual Excel Formulas?

Knowing individual formulas is a useful starting point, but workplace spreadsheets often require you to combine functions, analyse data, and choose the right Excel tool for a particular task. The Microsoft Excel Formulas: Mastery Course by PW Gyaan-E takes this further with lookup functions, data analysis, visualisation, and automation.

PW Gyaan-E therefore gives you a way to progress from practising individual formulas to working with several Excel functions and tools within more complete spreadsheet tasks.

Excel formulas become valuable at work when you can recognise a data problem and choose the right function to solve it. Practising these 20 formulas with sales, HR, inventory, finance, and customer datasets can help you prepare for common spreadsheet tasks and Excel-based assessments. As you become comfortable with the basics, the PW Gyaan-E Microsoft Excel Formulas: Mastery Course can help you progress to lookups, Pivot Tables, charts, Dynamic Arrays, and automation through practical lessons, quizzes, and a final project.