The Best Excel Formulas Every Office Worker Should Know

Spreadsheets run the modern business world. Whether you are in HR tracking employee tenure, Marketing evaluating campaign ROI, or Finance building the annual budget, you are going to encounter Microsoft Excel (or Google Sheets).

Yet, despite its ubiquity, many professionals barely scratch the surface of Excel's power. If you find yourself manually coloring cells, counting rows one by one, or copying and pasting data between sheets because your lookup formula "keeps breaking," this guide is for you.

We will walk through the ultimate toolkit of Excel formulas, starting from essential logic checks and climbing up to intermediate array operations, all explained with real-world office scenarios.

1. The Lookup Kings: VLOOKUP vs. XLOOKUP vs. INDEX/MATCH

The most common requirement in any office is taking data from Sheet A and finding its corresponding value in Sheet B.

VLOOKUP (Vertical Lookup)

For decades, VLOOKUP was the absolute king of this task. It searches for a specific value in the first column of a table array and returns a value in the same row from another column.

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

Example Use Case: You have a list of Employee IDs and need to pull in their salaries from the Payroll sheet. =VLOOKUP(A2, Payroll!A:E, 5, FALSE)

Limitations: VLOOKUP only searches left to right. If your Employee ID is in column C and the Salary is in column A, VLOOKUP breaks.

INDEX / MATCH (The Consultant's Choice)

Before XLOOKUP, this combination was the hallmark of an advanced Excel user. It bypasses the left-to-right limitation of VLOOKUP.

Syntax: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example Use Case: Looking up that same salary, regardless of column order. =INDEX(Payroll!A:A, MATCH(A2, Payroll!C:C, 0))

XLOOKUP (The Modern Standard)

Available in modern versions of Excel to replace both of the above. It is simpler to write, defaults to an exact match, and can search in any direction.

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

Example Use Case: =XLOOKUP(A2, Payroll!C:C, Payroll!A:A, "Not Found") Notice the built-in error handling ("Not Found"). This makes XLOOKUP superior for clean reporting.

2. Conditional Aggregation: SUMIFS and COUNTIFS

Never manually add figures together again. When analyzing data, you usually want to sum or count things only if they meet certain criteria.

SUMIFS

Sum values in one column if they meet criteria in other columns. Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, ...)

Example Use Case: You need to find the total sales revenue generated by the "London" office in "Q3". =SUMIFS(Sales!D:D, Sales!B:B, "London", Sales!C:C, "Q3")

COUNTIFS

Count how many rows meet multiple criteria. Excellent for HR and Operations tracking. Example Use Case: Count how many employees are in the "Engineering" department and have a performance rating of "5". =COUNTIFS(HR!B:B, "Engineering", HR!E:E, 5)

3. Logical Operations: IF, IFS, and IFERROR

The classic IF statement

Returns one value if a condition is true and another if false.

Example Use Case: Flagging overdue invoices. =IF(C2<TODAY(), "Overdue", "Current")

IFS (Multiple Conditions without Nesting)

If you have multiple bands (e.g., grading an exam, or calculating commission tiers), nested IF statements get messy fast. IFS checks multiple conditions in sequence.

Example Use Case: Assigning performance bands based on a score in cell B2. =IFS(B2>90, "Excellent", B2>70, "Good", B2>50, "Average", TRUE, "Needs Improvement")

IFERROR (The Dashboard Cleaner)

Nothing ruins a professional dashboard faster than #N/A or #DIV/0!. Wrapping your formulas in IFERROR replaces ugly errors with clean text or a blank cell.

Example Use Case: =IFERROR(VLOOKUP(A2, Data!A:Z, 5, FALSE), "No Data Available")

4. Text Manipulation: Cleaning Messy Data

Often, data exported from CRMs (like Salesforce or HubSpot) is messy. Trailing spaces, inconsistent capitalization, or combined fields will break your lookup formulas.

• TRIM(text): Removes all spaces from text except for single spaces between words. Mandatory step before doing lookups on manually entered data.
• PROPER(text): Capitalizes the first letter of each word (great for cleaning up names like "JOHN DOE").
• LEFT(text, num_chars) and RIGHT(...): Extracts characters from the beginning or end of a string. Useful for pulling area codes from phone numbers.
• TEXTSPLIT(text, delimiter): A newer Excel function that replaces "Text to Columns." If you have "Smith, John", =TEXTSPLIT(A2, ", ") will put "Smith" in one cell and "John" in the next.

Conclusion

You don't need to memorize these formulas line-by-line. The key is knowing what is possible so you can describe what you need. Bookmark this page, or try using our AI Excel Formula Generator when you get stuck!