Excel guide

Excel for data analysts: the 20 functions you actually need

Most data analyst work uses the same 20 Excel functions. Learn these and you can handle 80% of real-world data tasks.

Why the same functions keep appearing

Analysts spend most of their time on four tasks: joining data from different tables, summarizing large datasets by condition, cleaning messy text exports, and building time-based reports. Each group of functions below maps to one of these tasks.

You do not need to memorize syntax. You need to know which function solves which problem — the syntax you can always look up. This guide is organized by problem type so the mental model sticks.

Once you can read a dataset and immediately know which function family to reach for, you are ready to work as a data analyst.

Lookup functions

Pull data from one table into another. These are the functions that replace manual copy-paste.

VLOOKUP / XLOOKUP

Find a value in a table based on a key. XLOOKUP is the modern replacement.

Syntax

=VLOOKUP(lookup_value, table_array, col_index, [exact])

Example

=VLOOKUP(A2, Products!A:C, 3, FALSE) — find product name

INDEX + MATCH

More flexible than VLOOKUP. Can look left. The professional choice.

Syntax

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

Example

=INDEX(C:C, MATCH("ProductA", A:A, 0))

Aggregation functions

Summarize large datasets by condition. The backbone of every dashboard and report.

SUMIF / SUMIFS

Sum values that meet one or more conditions.

Syntax

=SUMIFS(sum_range, criteria_range1, criteria1, ...)

Example

=SUMIFS(Revenue, Region, "North", Month, "Jan")

COUNTIF / COUNTIFS

Count rows that meet conditions.

Syntax

=COUNTIFS(criteria_range1, criteria1, ...)

Example

=COUNTIF(Status, "Completed")

AVERAGEIF

Average of values meeting a condition.

Syntax

=AVERAGEIF(range, criteria, [average_range])

Example

=AVERAGEIF(Region, "South", Revenue)

Text functions

Clean and reshape text data — essential when working with exported data that never comes in the format you need.

CONCATENATE / &

Combine text from multiple cells.

Syntax

=A1&" "&B1 or =CONCAT(A1," ",B1)

Example

=A2&", "&B2 — creates "Smith, John"

LEFT / RIGHT / MID

Extract part of a text string.

Syntax

=LEFT(text, num_chars)

Example

=LEFT("ProductCode-123", 11) → "ProductCode"

TEXT

Format a number or date as text.

Syntax

=TEXT(value, format_text)

Example

=TEXT(A1, "MMM YYYY") → "Jan 2026"

Logic functions

Branch your calculations based on conditions. IF is the building block; IFS cleans up the mess of nested IFs.

IF

Conditional logic. Most-used function in Excel.

Syntax

=IF(condition, value_if_true, value_if_false)

Example

=IF(B2>1000, "High", "Low")

IFS

Multiple conditions without nested IFs.

Syntax

=IFS(cond1, val1, cond2, val2, ...)

Example

=IFS(Score>=90,"A",Score>=80,"B",Score>=70,"C",TRUE,"F")

Date functions

Work with time-series data, calculate durations, and align reporting to calendar boundaries.

DATEDIF / DAYS

Calculate days, months, or years between dates.

Syntax

=DATEDIF(start_date, end_date, "D")

Example

=DATEDIF(A2, TODAY(), "M") — months since date

EOMONTH

Get the last day of a month — useful for monthly reporting.

Syntax

=EOMONTH(start_date, months)

Example

=EOMONTH(A2, 0) — last day of the same month

PivotTables: the other essential skill

PivotTables are the other key Excel skill. Formulas work row by row; PivotTables summarize an entire dataset interactively in seconds — no formulas required.

Learn to do these four things with PivotTables:

Group data by dimension — region, date, category, rep, or any column in your dataset
Calculate sum, average, or count of a metric for each group with a single drag
Filter and sort the output to surface what matters without touching the source data
Refresh when data updates — one click keeps your summary in sync with new rows

If you can build a PivotTable and write the functions above, you have the Excel foundation that most job descriptions actually require. Everything else — Power Query, macros, DAX — is additive.

Keep building

Build on these in the Data Analyst track

Excel is the foundation. The Data Analyst learning path adds SQL, Python basics, data visualization, and the statistical thinking that separates analysts from spreadsheet users.

Explore the Data Analyst track