Excel formula library

Excel Tutorial

Every Excel formula in one place — search, filter by category or level, then open syntax, samples, and pitfalls. New formulas added to the library appear here automatically.

Start with first formula Browse formulas

127 Formulas

11 Categories

3 Skill levels

Live Search & filters

Formula list

Find any Excel formula

Filter by category — Math, Lookup, FILTER and other Dynamic Arrays, Text, Date, and more — then open a formula for syntax and examples.

Chapter 8

Dynamic Arrays (365)

101 UNIQUE =UNIQUE(array, [by_col], [exactly_once]) Returns a list of unique values from a list or range. In Excel 365, it automatically spills the deduplicated values into adjacent cells below. Dynamic Arrays (365) Intermediate Office 365
Syntax
```
=UNIQUE(array, [by_col], [exactly_once])
```
Arguments

array

The range or array from which to return unique rows or columns.

[by_col]

FALSE (default) compares rows; TRUE compares columns.

[exactly_once]

FALSE (default) returns all distinct values; TRUE returns ONLY items that occur exactly once.
Sample
```
=UNIQUE(A2:A8)
```
Result HR (Spills to C2:C4)
Sample data
Download

A B C D
2 Sales Rohan Sharma Sales Distinct #1
3 HR Priya Singh HR Distinct #2
4 Sales Amit Verma Finance Distinct #3

Pro tip
Combine with SORT: =SORT(UNIQUE(A2:A100)) to produce an alphabetized, deduplicated dropdown or list in a single formula.

Common pitfall
If any cell in the destination spill zone contains data, Excel returns a #SPILL! error. Clear the cells below to fix it.
102 FILTER =FILTER(array, include, [if_empty]) Filters an array or range based on a boolean criteria array, automatically spilling all matching rows dynamically. Replaces tedious manual filtering. Dynamic Arrays (365) Intermediate Office 365
Syntax
```
=FILTER(array, include, [if_empty])
```
Arguments

array

The array or range to filter.

include

A boolean array whose dimensions match array, determining which rows/columns to include.

[if_empty]

The return value if no items meet the criteria.
Sample
```
=FILTER(A2:C6, B2:B6="North", "None")
```
Result Filtered Table (Spills)
Sample data
Download

A B C D E F
2 Rohan North 45,000 Rohan North 45,000
3 Priya South 52,000 Amit North 61,000
4 Amit North 61,000 (Filtered Spilled Row)

Pro tip
For multiple conditions: Use * for AND logic: (B2:B10="North")*(C2:C10>50000), and + for OR logic: (B2:B10="North")+(B2:B10="South").

Common pitfall
The include argument must have the exact same number of rows as the array range; otherwise, a #VALUE! error occurs.
103 SORT =SORT(array, [sort_index], [sort_order], [by_col]) Sorts the contents of a range or array by specified column index and order. It automatically updates whenever source data changes. Dynamic Arrays (365) Beginner Office 365
Syntax
```
=SORT(array, [sort_index], [sort_order], [by_col])
```
Arguments

array

The range or array to sort.

[sort_index]

A number indicating the row or column to sort by (1-based index).

[sort_order]

1 for ascending (default), -1 for descending.
Sample
```
=SORT(A2:B5, 2, -1)
```
Result Sorted Table (Spills)
Sample data
Download

Pro tip
Combine with TAKE: =TAKE(SORT(A2:B100, 2, -1), 5) immediately returns the Top 5 best-performing items!

Common pitfall
Setting sort_index higher than the total number of columns in array will trigger a #VALUE! error.
104 SORTBY =SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...) Sorts a range or array based on the values in one or more corresponding ranges. Unlike SORT, the sorting column does not have to be inside the source array. Dynamic Arrays (365) Intermediate Office 365
Syntax
```
=SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], ...)
```
Arguments

array

The range or array to sort.

by_array1

The range or array on which to sort.

[sort_order1]

1 for ascending, -1 for descending.
Sample
```
=SORTBY(A2:A5, B2:B5, -1)
```
Result Neha Kapoor
Sample data
Download

A B C D
2 Rohan 78 Tech Neha Kapoor (95)
3 Priya 84 Sales Priya (84)
4 Amit 62 HR Rohan (78)

Pro tip
Pass multiple by_array arguments for tiered multi-level sorting: =SORTBY(A2:C50, B2:B50, 1, C2:C50, -1).

Common pitfall
All by_array ranges must have the identical height (number of rows) as the primary array.
105 SEQUENCE =SEQUENCE(rows, [columns], [start], [step]) Generates an array of sequential numbers across rows and columns, such as 1, 2, 3, 4, or custom sequences like dates and step increments. Dynamic Arrays (365) Beginner Office 365
Syntax
```
=SEQUENCE(rows, [columns], [start], [step])
```
Arguments

rows

The number of rows to return.

[columns]

The number of columns to return (default 1).

[start]

The starting number in the sequence (default 1).

[step]

The increment for each subsequent value (default 1).
Sample
```
=SEQUENCE(5, 1, 101, 1)
```
Result 101 (Spills to A2:A6)
Sample data
Download

A B C D
2 101 Rohan Sharma Morning B-101
3 102 Priya Singh Morning B-102
4 103 Amit Verma Evening B-103

Pro tip
To generate all days of the current month: =DATE(2025, 4, 1) + SEQUENCE(30, 1, 0, 1).

Common pitfall
Using 0 or negative numbers for rows or columns produces a #VALUE! error.
106 RANDARRAY =RANDARRAY([rows], [columns], [min], [max], [whole_number]) Generates an array of random numbers between specified minimum and maximum values. Can output decimals or whole integers across any 2D dimensions. Dynamic Arrays (365) Intermediate Office 365
Syntax
```
=RANDARRAY([rows], [columns], [min], [max], [whole_number])
```
Arguments

[rows], [columns]

The number of rows and columns to generate.

[min], [max]

Minimum and maximum boundary values.

[whole_number]

TRUE for integers, FALSE for decimals.
Sample
```
=RANDARRAY(4, 2, 50, 100, TRUE)
```
Result 74 (Spills 4x2)
Sample data
Download

A B C D
2 Rohan Sharma 84 92 Sample Tested
3 Priya Singh 71 88 Sample Tested
4 Amit Verma 95 64 Sample Tested

Pro tip
RANDARRAY is volatile and recalculates on every workbook change. Copy and "Paste as Values" (Ctrl+Alt+V > V) once you establish test datasets.

Common pitfall
Setting min greater than max produces a #VALUE! error.

	A	B	C	D
2	Sales	Rohan Sharma	Sales	Distinct #1
3	HR	Priya Singh	HR	Distinct #2
4	Sales	Amit Verma	Finance	Distinct #3

	A	B	C	D	E	F
2	Rohan	North	45,000	Rohan	North	45,000
3	Priya	South	52,000	Amit	North	61,000
4	Amit	North	61,000	(Filtered Spilled Row)

	A	B	C	D
2	Rohan	78	Tech	Neha Kapoor (95)
3	Priya	84	Sales	Priya (84)
4	Amit	62	HR	Rohan (78)

	A	B	C	D
2	101	Rohan Sharma	Morning	B-101
3	102	Priya Singh	Morning	B-102
4	103	Amit Verma	Evening	B-103

	A	B	C	D
2	Rohan Sharma	84	92	Sample Tested
3	Priya Singh	71	88	Sample Tested
4	Amit Verma	95	64	Sample Tested