7 Excel Formulas Every Professional Should Know

There are over 500 functions in Excel, but you don't need to know them all. These seven formulas will handle 90% of your daily spreadsheet challenges. Master them, and you'll be the person everyone comes to when they need Excel help.

The Problem

Most professionals learn Excel by trial and error, picking up basic SUM and AVERAGE but missing the formulas that could save them hours every week. They end up doing manually what could be automated in seconds.

The Solution

These seven formulas are your Excel power toolkit. Each one solves common business problems elegantly and efficiently.

What You'll Need

• Excel 2016 or later (TEXTJOIN requires Excel 2019+ or Microsoft 365; all other formulas work in Excel 2016+)
• A willingness to practice
• Sample data to experiment with

Formula 1: SUMIFS - Conditional Sum with Multiple Criteria

The Problem It Solves: You need to sum values that meet multiple conditions.

Formula:

Real-World Example: Sum all sales in the East region for Q4:

How it works:

• D2:D1000 - The column containing sales amounts
• B2:B1000, "East" - Only include rows where region is "East"
• C2:C1000, "Q4" - Only include rows where quarter is "Q4"

Pro Tip: You can add up to 127 criteria pairs!

Formula 2: COUNTIFS - Count with Multiple Conditions

The Problem It Solves: You need to count items meeting several criteria.

Formula:

Real-World Example: Count high-priority tasks that are overdue:

How it works:

• C2:C500, "High" - Tasks with "High" priority
• D2:D500, "<"&TODAY() - Due date before today

Pro Tip: Use wildcards like "*report*" to match partial text.

Formula 3: IF with AND/OR - Complex Conditional Logic

The Problem It Solves: You need to make decisions based on multiple conditions.

Formula:

=IF(AND(condition1, condition2), value_if_true, value_if_false)
=IF(OR(condition1, condition2), value_if_true, value_if_false)

Real-World Example: Determine bonus eligibility (sales > $50,000 AND tenure > 2 years):

How it works:

• AND(B2>50000, C2>2) - Both conditions must be true
• Returns "Eligible" if both conditions are met
• Returns "Not Eligible" otherwise

Variation with OR:

Either high sales OR long tenure qualifies for senior bonus.

Formula 4: TEXT - Format Numbers and Dates

The Problem It Solves: You need to combine text with formatted numbers or dates.

Formula:

Real-World Example: Create a formatted report date:

Result: "Report generated on November 5, 2025"

Common Format Codes:

Formula 5: IFERROR - Graceful Error Handling

The Problem It Solves: Your formulas show ugly #N/A, #DIV/0!, or #VALUE! errors.

Formula:

Real-World Example: Calculate percentage change without division errors:

How it works:

• Attempts to calculate the percentage change
• If A2 is zero (causing #DIV/0!), returns 0 instead
• Works for any error type

Advanced Version with IFNA:

IFNA only catches #N/A errors, letting other errors show (which might indicate real problems).

Formula 6: CONCAT or TEXTJOIN - Combine Text Intelligently

The Problem It Solves: You need to merge text from multiple cells with separators.

Formula (TEXTJOIN - recommended):

Real-World Example: Create a full address from separate fields:

Where A2=Street, B2=City, C2=State, D2=Zip

Result: "123 Main St, Chicago, IL, 60601"

How it works:

• ", " - Uses comma-space as delimiter
• TRUE - Ignores empty cells (no awkward double commas)
• Combines all provided cell values

For Older Excel (CONCAT):

Formula 7: INDEX-MATCH - The Lookup Power Combo

The Problem It Solves: VLOOKUP is too limited for your lookup needs.

Formula:

Real-World Example: Find an employee's salary by name (with name in column B, salary in column A):

How it works:

• MATCH(E2, B2:B100, 0) - Finds the row number where E2 appears in the name column
• INDEX(A2:A100, ...) - Returns the salary from that row
• Works in any direction (unlike VLOOKUP!)

Two-Way Lookup (Matrix Style):

This finds the intersection of a row and column—perfect for rate tables!

The Complete Solution

Here's a real-world example combining multiple formulas:

Scenario: Create a sales summary that shows total, count, and average for a selected region, with error handling.

="Total Sales: "&TEXT(IFERROR(SUMIFS(D:D, B:B, G2), 0), "$#,##0")&
" | Deals: "&COUNTIFS(B:B, G2)&
" | Average: "&TEXT(IFERROR(SUMIFS(D:D, B:B, G2)/COUNTIFS(B:B, G2), 0), "$#,##0")

Formula Breakdown

Common Variations

Variation 1: Dynamic Date Ranges with SUMIFS

Variation 2: Nested IFs for Multiple Outcomes

Variation 3: INDEX-MATCH with Multiple Criteria

Press Ctrl+Shift+Enter for legacy Excel versions.

Pro Tips

• Name your ranges: =SUMIFS(Sales, Region, "East") is more readable than cell references
• Use tables: Excel tables automatically expand formulas as you add data
• Test incrementally: Build complex formulas one piece at a time
• Document complex formulas: Add a comment explaining what it does

Troubleshooting

Conclusion

These seven formulas form the foundation of Excel mastery. SUMIFS and COUNTIFS handle conditional calculations. IF with AND/OR manages complex logic. TEXT formats your output professionally. IFERROR keeps your spreadsheets clean. TEXTJOIN combines data elegantly. And INDEX-MATCH handles any lookup challenge.

Practice each one until it becomes second nature. Before long, you'll be combining them in creative ways to solve problems your colleagues didn't even know could be automated.

Your journey to Excel expertise starts with these seven formulas. Master them, and the rest becomes easy.