Basic Excel Formulas: The Top 10 Formulas Every Analyst Needs to Know

Mastering basic excel formulas is the bedrock skill upon which all modern business intelligence, financial modeling, and operational analysis is constructed. Regardless of how complex machine learning algorithms or enterprise data warehouses become, spreadsheets remain the universal medium where business decisions are evaluated, tested, and communicated.

Understanding how to combine arithmetic operators with native Excel functions allows you to transform static tabular extracts into living, interactive calculation engines. Instead of manually tallying ledger balances or eyeballing customer records, these core formulas automate data cleaning, summarize distributions, and enforce business rules with mathematical precision.

In this foundational guide, we review the top 10 basic excel formulas, explain the critical rules of cell referencing, construct a complete departmental performance model step-by-step, and highlight common syntax pitfalls. For a conceptual dive into spreadsheet architecture, read our guide on Excel formulas vs functions and explore basic spreadsheet data cleaning.

On the Data Analyst Roadmap, these formulas represent Week 1 and Week 2 competencies—the exact building blocks you will later translate into SQL queries and Python Pandas functions.

Top 10 Basic Excel Formulas Every Analyst Must Know

1. SUM: Instant Mathematical Aggregation

The workhorse of spreadsheet mathematics, adding ranges or individual numbers together.

Syntax:  =SUM(number1, [number2], ...)
Example: =SUM(B2:B20)
Shortcut: Alt + =

Unlike =B1+B2+B3, the SUM function automatically ignores blank cells and text labels, preventing #VALUE! calculation breaks. Read our detailed SUM formula in Excel guide for advanced 3D multi-sheet summing.

2. AVERAGE: Calculating the Arithmetic Mean

Computes the mean of numeric entries within a designated range.

Syntax:  =AVERAGE(number1, [number2], ...)
Example: =AVERAGE(C2:C100)

Analyst Note: AVERAGE only divides by cells containing true numeric values; it skips text and blank cells, but it does include cells containing 0.

3. COUNT and COUNTA: Tracking Data Volume

Tracking record completeness is essential for validating data imports.

Syntax:  =COUNT(value1, [value2], ...)    -- Counts cells with NUMBERS only
Example: =COUNT(A2:A500)
 
Syntax:  =COUNTA(value1, [value2], ...)   -- Counts cells that are NOT EMPTY (text, dates, numbers)
Example: =COUNTA(A2:A500)

If COUNTA returns 500 but COUNT returns 480, you immediately know that 20 rows contain text, errors, or missing numeric values.

4. IF: Branching Conditional Logic

Evaluates whether a business rule is met, returning one value if TRUE and another if FALSE.

Syntax:  =IF(logical_test, value_if_true, [value_if_false])
Example: =IF(D2 >= 100000, "Bonus Eligible", "Standard")

Nest multiple IF statements or combine with AND/OR to evaluate tiered commissions:

=IF(AND(D2>=100000, E2>=90%), "Top Tier", "Regular")
 
For tracking completion status with visual controls, you can also link logical formulas with [checkboxes in Excel](/blog/insert-checkbox-in-excel). And to generate dynamic serial row numbers automatically, see the [SEQUENCE formula in Excel](/blog/sequence-formula-in-excel).

5. MIN and MAX: Identifying Statistical Extremes

Identifies the smallest and largest values within a dataset without requiring manual sorting.

Syntax:  =MIN(number1, [number2], ...)
Example: =MIN(E2:E50)
 
Syntax:  =MAX(number1, [number2], ...)
Example: =MAX(E2:E50)

Combine these to calculate range spread: =MAX(E2:E50) - MIN(E2:E50).

6. VLOOKUP and XLOOKUP: Cross-Table Data Enrichment

Matches an identifier from your active table against an external catalog to retrieve associated metadata.

-- VLOOKUP (Universal):
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=VLOOKUP(A2, $G$2:$I$50, 3, FALSE)
 
-- XLOOKUP (Modern Microsoft 365):
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
=XLOOKUP(A2, $G$2:$G$50, $I$2:$I$50, "Missing")

For complete deep dives on lookup mechanics, see our tutorials on the VLOOKUP formula in Excel and XLOOKUP formula in Excel.

7. TRIM and PROPER: Text Cleaning

Removes rogue spaces and standardizes inconsistent capitalization from dirty web exports.

=TRIM(A2)             -- Removes double spaces, leading spaces, and trailing spaces
=PROPER(A2)           -- Capitalizes the first letter of each word ("priya sharma" -> "Priya Sharma")
=TRIM(PROPER(A2))     -- Nested combination

8. The Ampersand (&) Operator: String Concatenation

Glues separate cell values together into unified labels or composite keys.

=A2 & " " & B2
-- Joins First Name (A2) and Last Name (B2) with an intervening space

See our complete guide on how to combine two columns in Excel for advanced TEXTJOIN examples.

9. COUNTIF and SUMIF: Conditional Aggregation

Summarizes subsets of data matching specific filter criteria.

-- Count orders from Bangalore branch:
=COUNTIF(B2:B100, "Bangalore")
 
-- Sum revenue from Bangalore branch:
=SUMIF(B2:B100, "Bangalore", D2:D100)

10. LEN: Measuring String Length

Counts character count in a cell, ideal for validating fixed-length phone numbers, tax IDs, or postal codes.

Syntax:  =LEN(text)
Example: =IF(LEN(C2)=10, "Valid Phone", "Invalid Length")

Step-by-Step Example: End-to-End Sales Performance Tracker

Let's build a real scorecard evaluating five account executives using our basic Excel formulas:

Summary Statistics Table:

• Total Team Revenue: =SUM(D2:D6) $\rightarrow$ 2,660,000
• Average Revenue per Rep: =AVERAGE(D2:D6) $\rightarrow$ 532,000
• Top Closed Deal Count: =MAX(C2:C6) $\rightarrow$ 15
• Lowest Rep Revenue: =MIN(D2:D6) $\rightarrow$ 440,000
• Reps Above Quota: =COUNTIF(F2:F6, "Exceeded") $\rightarrow$ 3

In five minutes, applying basic Excel formulas produces a complete, dynamic executive performance report that recalculates automatically as new deals close.

Basic Excel Formulas: Logical Tests, Date Math, and String Slicing

Beyond primary arithmetic and summary statistics, intermediate analytics relies on logical conditioning, temporal manipulation, and text parsing. Explore the broader roadmap in our Excel Tutorials hub.

Logical Decision Making with IF, AND, and OR

The =IF() function evaluates logical assertions to categorize records dynamically:

=IF(logical_test, value_if_true, value_if_false)
=IF(AND(B2 >= 100000, C2 >= 0.15), "High Value Target", "Standard")

• AND(cond1, cond2) returns TRUE only if all conditions are satisfied.
• OR(cond1, cond2) returns TRUE if at least one condition is satisfied.
• In modern Excel, replace deeply nested IF statements with =IFS():

  excel

  Copy code

  =IFS(B2 >= 100000, "Tier 1", B2 >= 50000, "Tier 2", TRUE, "Tier 3")

Practical Date & Time Arithmetic

Spreadsheets store dates as sequential serial integers (where Day 1 is January 1, 1900). Understanding this architecture enables precise business date calculations:

=TODAY() + 30          ' Calculates deadline 30 days from today
=DATEDIF(A2, B2, "M")  ' Calculates complete months between two dates
=EOMONTH(TODAY(), 0)   ' Returns the last day of the current calendar month
=WORKDAY(A2, 10)       ' Calculates delivery date skipping weekends and holidays

Text Extraction and Slicing

When parsing raw transaction strings, account numbers, or SKU identifiers:

• =LEFT(text, num_chars): Extracts characters from the start of a string.
• =RIGHT(text, num_chars): Extracts characters from the end of a string.
• =MID(text, start_num, num_chars): Extracts a substring from any arbitrary middle position.
• =LEN(text): Measures total character count, indispensable for validating fixed-length phone numbers or tax IDs.

Common Mistakes When Writing Basic Excel Formulas

1. The Shifting Formula Range (Missing $ Anchors)

Writing =D2/SUM(D2:D6) without locking the sum range causes row 3 to evaluate =D3/SUM(D3:D7), missing row 2 and including blank row 7. Always press F4 to lock the denominator: =D2/SUM($D$2:$D$6).

2. Quotation Marks in Logic Formulas

Numbers do not require quotes; text strings always do.

• Correct: =IF(A1 > 50, "High", "Low")
• Error: =IF(A1 > "50", ...) treats "50" as text, which Excel evaluates as alphabetically greater than any number.

Formula Categories Summary

When Analysts Use Basic Excel Formulas in Daily Work

Ad-hoc Cohort Analysis. Filtering lead cohorts with COUNTIF and SUMIF to verify marketing channel acquisition efficiency.

Quick Sanity Checks Before Database Load. Running COUNTA, MIN, and MAX on an exported CSV to spot negative balances or missing foreign keys before loading data into PostgreSQL or Snowflake.

Executive Scorecard Modeling. Delivering lightweight, zero-maintenance reports to non-technical business partners who cannot access SQL or BI dashboards.

For advanced ranking and sorting workflows, explore the RANK formula in Excel and our comprehensive guide to Excel pivot tables.

Related Excel Tutorials

• Excel Formulas: The Complete Guide
• XLOOKUP Formula in Excel
• SUM Formula in Excel
• Excel Pivot Tables Guide
• Explore All Guides in the Excel Tutorials Hub