Excel for Data Analysis - Functions, Examples and PivotTables

Excel can turn a small sales table into a useful report with lookups, conditional totals and sorted lists. This guide covers nine groups of functions and a PivotTable workflow, with inputs and expected results you can check yourself.

Use Excel for Microsoft 365 or Excel 2024 for the complete guide. Excel 2021 supports many of these functions, but not LAMBDA. XLOOKUP is unavailable in Excel 2016 and 2019. Formulas use English function names and commas; regional settings may require semicolons. Microsoft lists supported versions on each linked function page.

Set up the sample data

Copy this table, including headings, into A1:H7 of a blank worksheet. Each row represents one product's weekly sales in one region; sales are pounds, stock is units, and PrevSales is the previous week's sales. These are invented practice figures.

Product	Category	Region	Quarter	Stock	PrevSales	Sales	Status
P001	Oranges	North	Q1	60	1200	1300	Active
P002	Apples	North	Q1	55	1000	1020	Active
P003	Oranges	South	Q1	45	1000	870	Active
P004	Pears	South	Q1	70	800	760	Discontinued
P005	Apples	South	Q2	80	500	600	Active
P006	Pears	North	Q2	30	0	400	Active

Keep A:H for source data. Enter each example in a separate empty area, such as J2, and clear the previous result before trying the next. FILTER, UNIQUE and SORT need empty cells below and beside the formula for their results to spill. A blocked output area causes #SPILL!. If you convert the source to an Excel Table later, put spill formulas outside that table.

1. XLOOKUP - find a product's sales

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

=XLOOKUP("P002",A2:A7,G2:G7,"Not found")

Result: 1020. Search product codes in A and return sales from G. Exact matching is the default. A missing code such as P999 returns Not found. Duplicate codes return the first match by default, rather than a total across records.

Microsoft's XLOOKUP documentation.

2. FILTER - extract matching rows

Syntax: =FILTER(array, include, [if_empty]).

=FILTER(A2:H7,G2:G7>1000,"No matches")

Result: two rows and eight columns, containing the complete P001 and P002 records in their original order. Changing the sales threshold to 2000 returns No matches. To select only the first matching product code:

=INDEX(FILTER(A2:A7,G2:G7>1000,"No matches"),1)

Result: P001. The @ implicit-intersection operator is not a general instruction to select the first FILTER result.

Microsoft's FILTER documentation and INDEX documentation.

3. UNIQUE - list distinct categories

Syntax: =UNIQUE(array, [by_col], [exactly_once]).

=UNIQUE(B2:B7)

Result: Oranges, Apples, Pears, vertically in first-appearance order. Each category appears twice. Setting exactly_once to TRUE would therefore leave no results and return #CALC!; it means values occurring once, rather than one copy of each distinct value.

Microsoft's UNIQUE documentation.

4. SUMIFS - total sales meeting multiple conditions

Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

=SUMIFS(G2:G7,B2:B7,"Oranges",E2:E7,">50")

Result: 1300. P001 is the only orange product with stock above 50. SUMIFS adds sales, not stock. All conditions must hold for a row to contribute, and the ranges must have matching dimensions.

Microsoft's SUMIFS documentation.

5. COUNTIFS and SUBTOTAL - matching versus visible rows

Syntax: =COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...).

=COUNTIFS(C2:C7,"North",G2:G7,">1000")

Result: 2 (P001 and P002). COUNTIFS evaluates these ranges even when rows are hidden or filtered out.

For a visible-row count, select A1:H7, enable Data > Filter, filter Region to North and Sales to greater than 1000, then enter this outside the filtered range:

=SUBTOTAL(103,A2:A7)

Result: 2. Clear both filters and it returns 6. With filters cleared, manually hide worksheet row 2 and it returns 5; unhide it before continuing.

SUBTOTAL takes a cell reference, not a scalar COUNTIFS result. Function number 103 counts nonempty cells while excluding filtered-out and manually hidden rows. Counting product codes works because every record has a nonblank code. Filter the source rows to apply criteria; do not wrap COUNTIFS inside SUBTOTAL.

Microsoft's COUNTIFS documentation and SUBTOTAL documentation.

6. AVERAGEIFS - average a defined group

Syntax: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

=AVERAGEIFS(G2:G7,C2:C7,"South",D2:D7,"Q1")

Result: 815, from (870 + 760) / 2. This includes the discontinued product because no Status condition was specified.

If no cells qualify, for example Region = West, AVERAGEIFS returns #DIV/0!. Inspect missing matches and source errors before handling them. A blanket IFERROR can conceal a broken reference or bad input as well as a missing group.

Microsoft's AVERAGEIFS documentation.

7. TEXT, CONCAT and TEXTJOIN - create report labels

Syntax: =TEXT(value, format_text), =CONCAT(text1, [text2], ...) and =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).

=CONCAT(A2," - ",TEXT(G2,"£#,##0.00"))

Result with UK number formatting: P001 - £1,300.00.

=TEXTJOIN(", ",TRUE,A2:A4)

Result: P001, P002, P003. TEXTJOIN inserts a delimiter; CONCAT joins exactly what you supply. TEXT produces text, so keep the numeric sales column for calculations. Apply a cell number format to display currency while retaining a number.

Microsoft documents TEXT, CONCAT and TEXTJOIN.

8. LET - name intermediate calculations

Syntax: =LET(name1, name_value1, [name2, name_value2], ..., calculation).

=LET(sales,G2,discount,0.15,discounted,sales*(1-discount),ROUND(discounted,2))

Result: 1105, or 1105.00 with two decimal places displayed. LET keeps names local to the formula. Naming an expression can avoid calculating it repeatedly when you reuse that name; LET does not make volatile functions nonvolatile.

Microsoft's LET documentation.

9. LAMBDA - reuse a percentage-change calculation

Syntax: =LAMBDA([parameter1, parameter2, ...], calculation).

First test a LAMBDA by calling it immediately:

=LAMBDA(previous,current,IF(previous=0,"No baseline",(current-previous)/previous))(F2,G2)

Result: 0.083333…, displayed as 8.33% with percentage formatting. An uncalled LAMBDA entered in a cell returns #CALC!.

In Formulas > Name Manager > New, create the workbook-scoped name PctChange with this Refers to formula:

=LAMBDA(previous,current,IF(previous=0,"No baseline",(current-previous)/previous))

Then enter:

=PctChange(F4,G4)

Result: -0.13, or -13%. Using F7 and G7 instead returns No baseline: a percentage change from zero is undefined. Workbook scope makes the function available throughout this workbook; it does not install it in other workbooks.

Microsoft's LAMBDA documentation.

10. PivotTables - summarise sales by region

A PivotTable is a reporting feature rather than a function. With all source rows visible:

1. Select A1:H7 and choose Insert > PivotTable. Use a new worksheet.
2. Drag Region into Rows and Sales into Values.
3. Check Values uses Sum of Sales, not Count. Sales must be numeric.

Expected result:

Region	Sum of Sales
North	2720
South	2230
Grand Total	4950

Add Status as a report filter and select Active: North stays 2720, South becomes 1470, and the grand total becomes 4190. PivotTables do not inherit the source sheet's AutoFilter visibility; use the PivotTable's own filters.

After changing source values, refresh the PivotTable. A fixed A1:H7 source excludes new rows outside that range; use an Excel Table as the source for expanding data. Refreshing a PivotTable and refreshing an external Power Query connection are separate steps.

Microsoft's PivotTable guide.

Put the functions together

These examples use the same A1:H7 data, with filters cleared.

Total North sales where stock exceeds 50

=LET(sales,G2:G7,region,C2:C7,stock,E2:E7,SUMIFS(sales,region,"North",stock,">50"))

Result: 2320 (1300 + 1020).

Rank active products by sales

=SORT(FILTER(A2:H7,H2:H7="Active"),7,-1)

Result: five complete records, ordered P001 (1300), P002 (1020), P003 (870), P005 (600), P006 (400). Column 7 of the returned eight-column array is Sales; -1 sorts descending. This ranks individual records, so aggregate first if your real data contains repeated product codes.

For just the sales values, without product details:

=SORT(FILTER(G2:G7,H2:H7="Active"),1,-1)

Result: 1300, 1020, 870, 600, 400, vertically. A one-column array must sort on column 1, not column 2. These formulas return all active records; the sample happens to have five. They do not impose a top-five limit on a larger dataset. If none are Active, FILTER returns #CALC! here; check that case before building a production report.

Microsoft's SORT documentation.

Find products whose sales fell by more than 5%

=FILTER(A2:A7,IF(F2:F7=0,FALSE,(G2:G7-F2:F7)/F2:F7<-0.05),"No declines")

Result: P003, vertically. P004 fell exactly 5%, so the strict less-than condition excludes it. P006 has a zero baseline and is excluded explicitly. Report zero-baseline products separately instead of treating them as zero growth.

Keep the report auditable

Keep source numbers separate from formatted labels, state the unit and period for every measure, and check totals against the source. Use Tables when ranges must expand, leave room for spills, and document what happens when no rows match or a denominator is zero. Formula recalculation does not by itself refresh external data or a PivotTable.