Copyright Notice read tutorial watch tutorial

The content in this file was created by Mynda Treacy from My Online Training Hub.
Individual users are permitted to recreate the examples for personal practice only.
Recreating the examples for training or demonstration to others is not permitted, unless writt

The workbook and any sheets within must be accompanied by the following copyright notice:

This sheet must remain in any file that uses this data and or these techniques.
Any uses of this workbook and/or data must include the above attribution.
tutorial

raining Hub.
actice only.
permitted, unless written consent is granted by Mynda Treacy.

owing copyright notice: My Online Training Hub ©.

TRIMRANGE Function Read TRIMRANGE Deep Dive tutorial

The TRIMRANGE function scans in from the edges of a range or array until it finds a
non-blank cell (or value), it then excludes those blank rows or columns.

Syntax: =TRIMRANGE(range, [trim_rows], [trim_cols])

range The range (or array) to be trimmed

trim_rows Determines which rows should be trimmed

0 - None
1 - Trims leading blank rows
2 - Trims trailing blank rows
3 - Trims both leading and trailing blank rows (default)

trim_columns Determines which columns should be trimmed

0 - None
1 - Trims leading blank columns
2 - Trims trailing blank columns
3 - Trims both leading and trailing blank columns (default)

Tip: A Trim Ref can be used to achieve the same functionality as

TRIMRANGE more succinctly by replacing the range's colon ":" with one
of the three Trim Ref types described below:

Equivalent
Type Example Description
TRIMRANGE
Trim leading
Trim All (.:.) A1.:.E10 TRIMRANGE(A1:E10,3,3) and trailing
blanks

Trim trailing
Trim Trailing (:.) A1:.E10 TRIMRANGE(A1:E10,2,2)
blanks

Trim leading
Trim Leading (.:) A1.:Z10 TRIMRANGE(A1:E10,1,1)
blanks

This pattern can also be applied to full-column or -row references (eg. A:.A)
TRIMRANGE & Trim Ref Dot Operator Examples

Period Sales Total 106,727,562 =SUM(_xlfn.trimrange(C35:C5

9/1/2024 4,484,000 Average 7,115,171 =AVERAGE(_xlfn._tro_trailing(
10/1/2024 9,295,611 Maximum
11/1/2024 7,267,203
12/1/2024 5,368,441 Practice: find the minimum sales
1/1/2025 6,607,762 using TRIMRANGE for the range
argument in the MIN function.
2/1/2025 7,297,531
3/1/2025 5,586,860
4/1/2025 6,964,775
5/1/2025 6,210,211
6/1/2025 9,518,894
7/1/2025 8,102,920
8/1/2025 5,864,622
9/1/2025 6,398,697
10/1/2025 12,375,820
11/1/2025 5,384,214
ve tutorial Watch TRIMRANGE Deep Dive Tutorial

Read Excel Tables

Watch Excel Ta
Tutorial
 Read Excel Tables
Ideal Layout - Excel Table Watch Excel Ta
Tutorial

UM(_xlfn.trimrange(C35:C54)) Period Sales Total 106,727,562 =SUM(MonthlySalesTbl

VERAGE(_xlfn._tro_trailing(C35:C54)) 9/1/2024 4,484,000 Average 7,115,171 =AVERAGE(MonthlySale
10/1/2024 9,295,611
11/1/2024 7,267,203
minimum sales 12/1/2024 5,368,441
for the range 1/1/2025 6,607,762
IN function.
2/1/2025 7,297,531
3/1/2025 5,586,860
4/1/2025 6,964,775
5/1/2025 6,210,211
6/1/2025 9,518,894
7/1/2025 8,102,920
8/1/2025 5,864,622
9/1/2025 6,398,697
10/1/2025 12,375,820
11/1/2025 5,384,214
Watch Excel Tables Tutorial
 Watch Excel Tables Tutorial

=SUM(MonthlySalesTbl[Sales])
=AVERAGE(MonthlySalesTbl[Sales])
VSTACK Deep Dive VSTACK Written Tutorial Deep Dive VSTACK Video

VSTACK Function
Appends arrays vertically and in sequence to return a larger array.

Syntax: =VSTACK(array1,[array2],...)
array The arrays (cell ranges) you want to append.

Sibling function
HSTACK Function
Appends arrays horizontally and in sequence to return a larger array.

Syntax: =HSTACK(array1,[array2],...)
array The arrays (cell ranges) you want to append.

VSTACK Examples
Source Data Table 1 Source Data Table 2

Name Region Revenue Name

Fox Clancy North America 307,314 Vicki Tatum
Livia Headley Asia Pacific 115,456 Briley Travis
Ralph Bullard UK 451,885 Bodhi Penny
Tevin Brewster MEA 462,241 Trudy Roach
Velda Pearson Europe 430,878 Marcus Abbott
Darryl Carter

Formula in B31:{=_xlfn.vstack(Table1_vstack[#all],Table2_vstack[])}

#NAME? #NAME? #NAME?

#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
#NAME? #NAME? #NAME?
 #NAME? #NAME? #NAME?

3D VSTACK
Formula in B50:{=_xlfn.vstack(Sales_23[#all],Sales_24[],Sales_25[])}

#NAME? #NAME? #NAME? #NAME?

#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME?
 Deep Dive VSTACK Video Tutorial

e Data Table 2

Region Revenue
Asia Pacific 376,887
UK 391,111
Europe 488,678
MEA 353,149
North America 417,365
South America 167,542
Year Category Product Sales VSTACK Example Data
2023 Clothing Socks 2,300
2023 Components Brakes 3,400
2023 Bikes Mountain Bikes 6,300
2023 Accessories Helmets 17,000
Year Category Product Sales VSTACK Example Data
2024 Clothing Shorts 13,300
2024 Components Handlebars 2,300
2024 Bikes Road Bikes 3,500
2024 Accessories Helmets 8,300
2024 Clothing Gloves 13,300
Year Category Product Sales VSTACK Example Data
2025 Components Chains 20,000
2025 Clothing Bib-Shorts 4,000
2025 Clothing Tights 36,000
BYROW Function Read Deep Dive BYROW & BYCOL Tutorial

BYROW applies a function using LAMBDA to each row in an array and returns an array of equal

Syntax: =BYROW( array, LAMBDA( arrayName, formula))

array - the range of cells you want to pass to the LAMBDA formula one by one.
arrayName – the name you give to the array in the first argument.
formula – the formula you want to apply to each row in the array.

BYROW Average Scores Example

Student Name Physics Art Chemistry Average
Alice 64 64 88 #NAME? {=_xlfn.byrow(C14:E23,_x
Bob 70 58 72 #NAME?
Charlie 56 72 70 #NAME?
David 86 52 85 #NAME?
Eva 76 76 71 #NAME?
Fiona 69 74 67 #NAME?
George 82 54 71 #NAME? Practice: use BYROW to find
maximum grade for each stu
Hannah 76 79 55 #NAME?
Ian 93 57 96 #NAME?
Jasmine 89 77 61 #NAME?
& BYCOL Tutorial Watch Deep Dive BYROW & BYCOL Video

returns an array of equal height.

The LAMBDA(arrayName, formula) IMPORTANT NOTES:

argument can be replaced by a
single reference to the available 1. If you’re familiar with LAMBDA
e by one. ETA lambdas here. note that you do not need to defi
LAMBDA used by BYROW, althoug
For example:
2. BYROW can evaluate a LAMBDA
=BYROW(C12:E21, AVERAGE) without having to input the argum
at the end. e.g. this is not require
=BYROW(C5:F14,LAMBDA(x,SUM

3. LAMBDA takes more than one

with the BYROW function, it can o
{=_xlfn.byrow(C14:E23,_xleta.average)} argument.

Practice: use BYROW to find the

maximum grade for each student.
MPORTANT NOTES:
If you’re familiar with LAMBDA, then it’s important to
ote that you do not need to define a name for the
AMBDA used by BYROW, although you can if you want.

BYROW can evaluate a LAMBDA written inside it,

ithout having to input the arguments inside parentheses
t the end. e.g. this is not required:
BYROW(C5:F14,LAMBDA(x,SUM(x))(C5:F14))

LAMBDA takes more than one name argument, but

ith the BYROW function, it can only take one name
rgument.
SCAN Function Excel Expert Course - Unlock Your Full Potential

Scans an array by applying a LAMBDA to each value and returns an array that has each interme

Syntax: =SCAN(initial_value, array, LAMBDA(accumulator_name, value_name, formu

initial_value sets the starting value for the accumulator

array the array to be scanned
accumulator_name The value totaled up and returned as the final result.
value_name The current value from the array.
formula the formula you want to apply to the array.

SCAN Running Total Example

Year Category Product Sales Running Total
2023 Clothing Socks 2,300 #NAME?
2023 Components Brakes 3,400 #NAME?
2023 Bikes Mountain Bikes 6,300 #NAME?
2023 Accessories Helmets 17,000 #NAME?
2024 Clothing Shorts 13,300 #NAME?
2024 Components Handlebars 2,300 #NAME?
2024 Bikes Road Bikes 3,500 #NAME?
2024 Accessories Helmets 8,300 #NAME?
2024 Clothing Gloves 13,300 #NAME?
2025 Components Chains 20,000 #NAME?
2025 Clothing Bib-Shorts 4,000 #NAME?
2025 Clothing Tights 36,000 #NAME?
lock Your Full Potential

n array that has each intermediate value.

r_name, value_name, formula))

This is the equivalent of:

=SCAN(0,B14:B17,LAMBDA(a,b,a+b))
FILTER Function Read Deep Dive FILTER tutorial

The FILTER function allows you to filter a range of data based on criteria you define.
Syntax: =FILTER(array, include, [if_empty])

array is the range or array containing the values you want filtered.

include is the logical test that returns a Boolean array (TRUE/FALSE) the same height or widt

if_empty is an optional value to return if the included array are empty i.e. if the filter results i

FILTER Example Data

Department Item Quantity Price Total $
Support Stamps 50 0.60 30.00
Support Paper 10 4.99 49.90
Support Pens 15 0.95 14.25
Front Office Stamps 250 0.60 150.00
Front Office Paper 30 4.99 149.70
Front Office Pens 50 0.95 47.50
Sales Stamps 200 0.60 120.00
Sales Paper 20 4.99 99.80
Sales Pens 25 0.95 23.75

Filtered Data{=_xlfn._xlws.filter(B13:F21,B13:B21="Sales","No Records")}

Department Item Quantity Price Total $

#NAME? #NAME? #NAME? #NAME? #NAME?
Practice: use FILTER to retur
#NAME? #NAME? #NAME? #NAME? #NAME? transactions for 'Stamps'.
#NAME? #NAME? #NAME? #NAME? #NAME?
 Watch Deep Dive FILTER tutorial

ia you define.

the same height or width as the array.

y i.e. if the filter results in no records.

Practice: use FILTER to return the

transactions for 'Stamps'.
GROUPBY Function Read GROUPBY & PIVOTBY Deep dive tutorial

The GROUPBY function allows you to create a summary of your data via a formula. It supports group
aggregating the associated values. For instance, if you had a table of sales data, you might generat
year.
Syntax: =GROUPBY(row_fields, values, function, [field_headers], [total_depth],
Click here for syntax explanation.

GROUPBY Example Data

Segment Country Product Units Sold Sales Manager

Midmarket France Paseo 549 8,235.00 Zhang, Evelyn
Government France VTT 1527 534,450.00 Zhang, Evelyn
Small Business USA Montana 2498 741,906.00 Kowalski, Sofia
Channel Partners USA Amarilla 1989 23,629.32 Kowalski, Sofia
Government Germany Montana 1797 610,081.50 Rivera, Carlos
Government USA Velo 736 14,131.20 Kowalski, Sofia
Government Canada Velo 2646 50,803.20 Patel, Aria
Government USA VTT 349 117,264.00 Kowalski, Sofia
Government Canada Paseo 2349 15,620.85 Patel, Aria
Government France Paseo 1031 6,711.81 Zhang, Evelyn
Channel Partners Germany VTT 2215 24,719.40 Rivera, Carlos
Midmarket USA Paseo 2931 40,887.45 Kowalski, Sofia
Small Business Germany Paseo 1123 313,317.00 Rivera, Carlos
Enterprise Germany Amarilla 994 115,552.50 Rivera, Carlos
Small Business Canada Montana 1283 354,108.00 Patel, Aria
Government Germany Paseo 2409 15,513.96 Rivera, Carlos
Small Business France Montana 322 87,906.00 Zhang, Evelyn
Channel Partners France VTT 2234 24,395.28 Zhang, Evelyn
Government France Velo 2805 49,929.00 Zhang, Evelyn
Government USA Paseo 380 2,367.40 Kowalski, Sofia
Government Canada VTT 623 191,884.00 Patel, Aria
Channel Partners Canada Amarilla 2761 29,156.16 Patel, Aria
Midmarket Germany Velo 660 8,613.00 Rivera, Carlos
Midmarket Canada Paseo 2470 31,863.00 Patel, Aria
eep dive tutorial Watch GROUPBY & PIVOTBY Deep Dive Video

ormula. It supports grouping along one axis and

data, you might generate a summary of sales by

ders], [total_depth], [sort_order], [filter_array], [field_relationship])

Example PivotTable GROUPBY Example

Table3[[#All],[Units Sold]],
_xleta.sum,)}
Row Labels Sum of Units Sold
Canada 12,132.00 #NAME? #NAME?
France 8,468.00 #NAME? #NAME?
Germany 9,198.00 #NAME? #NAME?
USA 8,883.00 #NAME? #NAME?
Grand Total 38,681.00 #NAME? #NAME?
PIVOTBY Function Read GROUPBY & PIVOTBY Deep dive tutorial

The PIVOTBY function allows you to create a summary of your data via a formula. It supports group
aggregating the associated values. For instance, if you had a table of sales data, you might genera
state and year.
Syntax: =PIVOTBY(row_fields, col_fields, values,function, [field_headers], [r
Click here for syntax explanation.

PIVOTBY Example Data

Segment Country Product Units Sold Sales Manager

Midmarket France Paseo 549 8,235.00 Zhang, Evelyn
Government France VTT 1527 534,450.00 Zhang, Evelyn
Small Business USA Montana 2498 741,906.00 Kowalski, Sofia
Channel Partners USA Amarilla 1989 23,629.32 Kowalski, Sofia
Government Germany Montana 1797 610,081.50 Rivera, Carlos
Government USA Velo 736 14,131.20 Kowalski, Sofia
Government Canada Velo 2646 50,803.20 Patel, Aria
Government USA VTT 349 117,264.00 Kowalski, Sofia
Government Canada Paseo 2349 15,620.85 Patel, Aria
Government France Paseo 1031 6,711.81 Zhang, Evelyn
Channel Partners Germany VTT 2215 24,719.40 Rivera, Carlos
Midmarket USA Paseo 2931 40,887.45 Kowalski, Sofia
Small Business Germany Paseo 1123 313,317.00 Rivera, Carlos
Enterprise Germany Amarilla 994 115,552.50 Rivera, Carlos
Small Business Canada Montana 1283 354,108.00 Patel, Aria
Government Germany Paseo 2409 15,513.96 Rivera, Carlos
Small Business France Montana 322 87,906.00 Zhang, Evelyn
Channel Partners France VTT 2234 24,395.28 Zhang, Evelyn
Government France Velo 2805 49,929.00 Zhang, Evelyn
Government USA Paseo 380 2,367.40 Kowalski, Sofia
Government Canada VTT 623 191,884.00 Patel, Aria
Channel Partners Canada Amarilla 2761 29,156.16 Patel, Aria
Midmarket Germany Velo 660 8,613.00 Rivera, Carlos
Midmarket Canada Paseo 2470 31,863.00 Patel, Aria
ep dive tutorial Watch GROUPBY & PIVOTBY Deep Dive Video

rmula. It supports grouping along two axis and

data, you might generate a summary of sales by

, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [

Example PivotTable

Sum of Sales Column Labels

Row Labels Amarilla Montana Paseo Velo VTT Grand Total
Channel Partners 52,785 49,115 101,900
Canada 29,156 29,156
France 24,395 24,395
Germany 24,719 24,719
USA 23,629 23,629
Enterprise 115,553 115,553
Germany 115,553 115,553
Government 610,082 40,214 114,863 843,598 1,608,757
Canada 15,621 50,803 191,884 258,308
France 6,712 49,929 534,450 591,091
Germany 610,082 15,514 625,595
USA 2,367 14,131 117,264 133,763
Midmarket 80,985 8,613 89,598
Canada 31,863 31,863
France 8,235 8,235
Germany 8,613 8,613
USA 40,887 40,887
Small Business 1,183,920 313,317 1,497,237
Canada 354,108 354,108
France 87,906 87,906
Germany 313,317 313,317
USA 741,906 741,906
Grand Total 168,338 1,794,002 434,516 123,476 892,713 3,413,045
col_sort_order], [filter_array], [relative_to])

3,PIVOTBY Example
2)}
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?
XLOOKUP Function Read XLOOKUP Deep Dive Tutorial Watch XLO

XLOOKUP replaces VLOOKUP, HLOOKUP and INDEX & MATCH plus built-in error handling

Syntax: =XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found

Argument Description
lookup_value The value you want to find, or cell containing the item you want to find
lookup_array The cell range or array you want to search
return_array The cell range or array containing the value you want returned
[if_not_found] Optional - the text you want returned in the event a match isn't found. If omit
returned.

[match_mode] Optional - Defaults to 0 for exact match

[search_mode] Optional - Defaults to 1 searching first to last

Options 2 and -2 require the lookup_array to be sorted in ascending or descen

*Binary search does not result in faster calculations now that Microsoft have optimised the look

Vertical Lookup Example

Category Product Sales Average Rating Product

Accessories Bike Racks 64,400 94% Caps
Accessories Helmets 36,400 65%
Accessories Lights 36,700 90%
Accessories Locks 35,000 100%
Bikes Cargo Bike 13,000 54%
Bikes Mountain Bikes 8,500 46%
Clothing Bib-Shorts 10,200 30%
Clothing Caps 23,600 57%
Clothing Gloves 90,700 89%
Clothing Jerseys 7,500 40%
Components Bottom Bracket 17,500 46%
Components Brakes 8,500 40%
Components Chains 56,000 88%
Components Handlebars 8,100 28%

Horizontal Lookup Example

Jan Feb Mar Apr May Jun

64,400 36,400 36,700 35,000 13,000 8,500

Oct 7,500 =_xlfn.xlookup(B43,B39:M39,B40:M40)

A vertical lookup_array must contain the same

number of rows as the return_array and a
horizontal lookup_array, as in this example,
must contain the same number of columns as
the return_array.
 Watch XLOOKUP Deep Dive Tutorial

ror handling

ay, [if_not_found], [match_mode], [search_mode])

u want to find

h isn't found. If omitted an error will be

ascending or descending order respectively.*

optimised the lookup algorithms.

Sales
23,600 =_xlfn.xlookup(G21,xlTbl[Product],xlTbl[Sales],"Missing")

The lookup_array and return_array must

contain the same number of rows.
 Jul Aug Sep Oct Nov Dec
10,200 23,600 90,700 7,500 17,500 8,500
LET Function Read LET Deep Dive Tutorial Watch LET Deep Dive Tutorial

Syntax: =LET(name1, value1, [name2], [value2], calculation)

first variable optional second formula

name & value variable name & using
value variables

Example: =LET( x, 5, y, 10, x + y)

Result: =15

LET Function Arguments Explained

name1 Name of the variable

value1 Values can be text, formulas, arrays, numbers, cell references or defined na
name2
Optionally add more name and value pairs as required
value2
calculation The formula that uses the names within the let function

Notes: - LET will not error if you don't use all the names, but any unused names are
- The formula should have an odd number of arguments i.e. pairs of names an
- The calculation can be contained inside of a name+value pair. E.g. =LET(x,
- Values can reference previously declared names.

LET Example

Brand Sales Before LET: 107.1 {=_xlfn.let(_xlpm.salescy,

=IF(SUM(C29:C38)>100, SUM(C29:C38)*1.05,
SUM(C29:C38), SUM(
A. Datum 10 With LET: #NAME? IF(_xlpm.salescy>100,_xlpm.salescy*1.05,_xlpm.sale
Adventure Works 16
Contoso 1
Fabrikam 3 The LET formula only calculates the SUM once, vs
which calculates it twice; once for the logical test a
Litware 2 either the TRUE or FALSE result.
Northwind Traders 20
Proseware 18
Southridge Video 12
The Phone Company 16
Wide World Importers 4
Total 102
 Deep Dive Tutorial

references or defined names

ired

t any unused names are redundant, so you should remove them

ents i.e. pairs of names and values, then a calculation
+value pair. E.g. =LET(x, 5, y, 10, result, x + y, result)

00, SUM(C29:C38)*1.05,
escy, SUM(C29:C38), SUM(C29:C38)*0.97)
0,_xlpm.salescy*1.05,_xlpm.salescy*0.97))}

nly calculates the SUM once, vs the IF formula

twice; once for the logical test and once for
FALSE result.
Automatic Date Lists Read AutoFill Dates Tutorial

Use Excel's DATE function when you need to take three separate values and combine th
The DATE function returns the sequential serial number that represents a particular date.

Syntax: DATE(year,month,day)

The SEQUENCE function allows you to generate a list of sequential numbers in an array

Syntax: =SEQUENCE(rows,[columns],[start],[step])

rows The number of rows to return

[columns] The number of columns to return
[start] The first number in the sequence
[step] The amount to increment each subsequent value in the array

DATE-SEQUENCE Examples

{=DATE(2025,_xlfn.sequence(24),{=EOMONTH(DATE(2025,_xlfn.sequence(12),1),0)}

1st of each Month End of each Month

#NAME? #NAME?

#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME? #NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
#NAME?
 torial Watch AutoFill Dates Tutorial

te values and combine them to form a date.

ents a particular date.

ntial numbers in an array, such as 1, 2, 3, 4.

sequent value in the array

uence(12),1),0)}
More Resources
Tutorials
Excel Functions...............................................................
Charting Blog Posts.........................................................
Excel Dashboard Blog Posts............................................

Webinar Replays
Excel Dashboards & Power BI.........................................

Courses
Advanced Excel...............................................................
Advanced Excel Formulas...............................................
Power Query...................................................................
PivotTable Quick Start....................................................
Xtreme PivotTables.........................................................
Power Pivot....................................................................
Excel Dashboards............................................................
Power BI.........................................................................
PowerPoint.....................................................................
Excel for Decision Making Under Uncertainty................
Excel for Finance Professionals.......................................
Excel Analysis ToolPak....................................................
Excel for Customer Service Professionals.......................
Excel for Operations Management.................................
Financial Modelling.........................................................
Microsoft Word Masterclass...........................................

Support
Excel Forum....................................................................

Follow Us for more Tips & Tutorials

https://www.myonlinetraininghub.com/excel-functions
https://www.myonlinetraininghub.com/category/excel-charts
https://www.myonlinetraininghub.com/category/excel-dashboard

https://www.myonlinetraininghub.com/excel-webinars

https://www.myonlinetraininghub.com/excel-expert-upgrade
https://www.myonlinetraininghub.com/advanced-excel-formulas-course
https://www.myonlinetraininghub.com/excel-power-query-course
https://www.myonlinetraininghub.com/excel-pivottable-course-quick-start
https://www.myonlinetraininghub.com/excel-pivottable-course
https://www.myonlinetraininghub.com/power-pivot-course
https://www.myonlinetraininghub.com/excel-dashboard-course
https://www.myonlinetraininghub.com/power-bi-course
https://www.myonlinetraininghub.com/microsoft-powerpoint-course
https://www.myonlinetraininghub.com/excel-for-decision-making-course
https://www.myonlinetraininghub.com/excel-for-finance-course
https://www.myonlinetraininghub.com/excel-analysis-toolpak-course
https://www.myonlinetraininghub.com/excel-for-customer-service-professionals
https://www.myonlinetraininghub.com/excel-operations-management-course
https://www.myonlinetraininghub.com/financial-modelling-course
https://www.myonlinetraininghub.com/microsoft-word-course

https://www.myonlinetraininghub.com/excel-forum