Excel Tips
Optimize Your
Spreadsheets with
Dynamic Array
Formula
Transform your spreadsheets into smart reports
Indra Pandu Prasetyo
Excel practice file included
[Link]/in/indrapandu
Excel Tips
Dynamic Array Formula: Explained
No more Ctrl+Shift+Enter
A formula that returns multiple results - not just in one cell, but
across a range.
A B C D
1 Name Joint Unique Name
2 Supri A1 Supri D2 = UNIQUE(A2:A8)
3 Dody A2 Dody The formula extracts unique values
4 Slamet A3 Slamet from the range A2:A8 and spills them
5 Surya B1 Surya into cells D2:D5
6 Dody B2
7 Supri B3
8 Surya B4
In Excel 365, 2021 or later version, array formulas spill
automatically into adjacent cells, either vertically or horizontally,
depending on the output shape.
No more Ctrl+Shift+Enter. No more copy-paste. Just one formula.
Dynamic Array formulas are not available in Excel 2019 or earlier versions.
Indra Pandu Prasetyo 1
Excel Tips
Dynamic Array Formula
Behind the logic - how it really works
A B C D
1 1 2 3 Bad
2 3 4 7 Bad
3 5 3 8 Good
4 3 5 8 Good
C1 = A1:A4 + B1:B4 D1 = IF(C1#>7, "Good", "Bad")
C1 = 1 + 2 = 3 D1 = 3 > 7= False = Bad
C2 = 3 + 4 = 7 D2 = 7 > 7= False = Bad
C3 = 5 + 3 = 8 D3 = 8 > 7= True = Good
C4 = 3 + 5 = 8 D4 = 8 > 7= True = Good
In the example above, formulas are entered only in cells C1 and D1.
Because the inputs are arrays (not single values), the results are spilled
down into C1:C4 and D1:D4 often called spilled range.
To refer to the entire spilled range, use #, such as =C1#, which
simplifies working with dynamic ranges in dashboards or reports.
Indra Pandu Prasetyo 2
Excel Tips
Sample Case
Filtering data using FILTER()
Parameters in square
=FILTER(array, include, [if_empty]) brackets [] are optional
— if not supplied, Excel
• array - the full range of data to filter will use the default
• include - the condition(s) for rows to keep behavior or value for
that argument.
• [if_empty] - fallback if no match is found
A B C D E F
1 Region Name Score Select Region: B
2 A Ryan 65
3 B Santo 44 Name Score
4 A Toni 77 Santo 44
5 A Rudy 76 Popo 98
6 B Popo 98 Nuka 84
7 B Nuka 84
E4 = FILTER(B2:C7,A2:A7=F1,”No Data”)
array Include
• Returns only matching row - in this case, where
Ryan 65 A=B False
A2:A7 = “B” (include = True)
Santo 44 B=B True
• Returns an array - results spill into adjacent cells Toni 77 A=B False
• Updates instantly when the input condition Rudy 76 A=B False
changes (e.g. F1 value changes to “A”) Popo 98 B=B True
• Displays “No Data” if no matches are found Nuka 84 B=B True
Indra Pandu Prasetyo 3
Excel Tips
Sample Case
Sorting filtered data using SORT()
=SORT(array, [sort_index], [sort_order], [by_col])
• array - the range of data to sort
• [sort_index] - column/row number to sort (default is 1)
• [sort_order] - sort order, 1 for ascending (default), -1 for descending
• [by_col] - TRUE to sort by columns, FALSE by rows (default)
A B C D E F
1 Region Name Score Select Region: B
2 A Ryan 65
3 B Santo 44 Name Score
4 A Toni 77 Popo 98
5 A Rudy 76 Nuka 84
6 B Popo 98 Santo 44
7 B Nuka 84
E4 = SORT(FILTER(B2:C7,A2:A7=F1), 2, -1)
array sorted result
• Array comes from previous FILTER result Santo 44 Popo 98
• SORT then orders the filtered result by column 2 Popo 98 Nuka 84
(sort_index = 2) in descending order (sort_order = -1) Nuka 84 Santo 44
• by_col is omitted - Excel defaults to sorting by rows
Indra Pandu Prasetyo 4
Excel Tips
Sample Case
Dynamic lookup with array XLOOKUP()
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found],
[match_mode], [search_mode])
• lookup_value - the value to find
• lookup_array - the range to search in
• return_array - the range to return the result from
• [if_not_found] - value to show if no match is found (default is #N/A)
• [match_mode] - exact or approximate match (default is 0 – exact match)
• [search_mode] - direction (first-to-last or reverse, default is first-to-last)
A B C D E F G H
1 Category Target % Activity Category Actual % Target % Gap
2 A 34% Act-1 A 21% 34% 13%
3 B 23% Act-2 B 43% 23% -20%
4 C 12% Act-3 C 22% 12% -10%
5 Act-4 D 12% 0% -12%
G2 = XLOOKUP(E2:E5,A2:A4,B2:B4,0) H2 = G2#-F2:F5
• Looks up each value in E2:E5 within A2:A4 • Calculates each value in
• Returns the corresponding value from B2:B4 G2# (G2:G5) and F2:F5
• The result spills vertically into G1:G4 • See slide 2 – it does the
• If not found (e.g., “D”), it returns 0 as defined similar steps
XLOOKUP outperforms INDEX + MATCH with support for array returns, bi-directional
lookups, and built-in error handling — all in a single, readable formula.
Indra Pandu Prasetyo 5
Excel Tips
Array in Named Ranges
Make an auto-update and scalable range
• You can assign either a spilled range (#) or dynamic array formula to a
named range
• Use the name in charts, data validation, or reports
A B C D E
1 Date Value Date Value D2 = UNIQUE(A2:A10)
2 1-Jun 5 1-Jun 6
E2 = SUMIFS(B2:B10,A2:A10,D2#)
3 1-Jun 1 2-Jun 7
4 2-Jun 7 3-Jun 3
5 3-Jun 3 4-Jun 3
6 4-Jun 1 5-Jun 2
7 4-Jun 2
8 5-Jun 1
9 5-Jun 1
10
You can use the range “ListDate” or “ListValue” in other cells or charts
Indra Pandu Prasetyo 6
Excel Tips
#SPILL! Error
Why and how to fix it
The #SPILL! error occurs when a dynamic array formula can't
return results because something is blocking the spill range.
The common causes and fixes:
Cells in the spill range are not empty
clear the obstructing cells
Spill range overlaps with merged cells
unmerge to allow spill
Table objects don't support arrays
use standard cell ranges
A B B2 = SORT(A2:A5)
1 Score Sorted • It attempts to sort and spill values into B2:B5
2 65 #SPILL • Since B4 has a value, this blocks the spill range
3 44
4 77 AA Fix
5 76 • Clear cell B4 to allow the result to spill correctly
Use the blue dashed border to see the intended spill range.
Indra Pandu Prasetyo 7
Excel Tips
Scale Up Your Reporting!
Self-Updating Excel Dashboard
Explore the interactive live dashboard
[Link]/links/dyndashboard
How It Works:
• The excel dashboard is hosted on OneDrive (embedded) with read-only access
• Any changes made by the owner will automatically update for all viewers
• Perfect for sharing live reports with teams or stakeholders
Indra Pandu Prasetyo 8
Excel Tips
Dynamic Array: Do More with Less
Let Excel work smarter, so you don’t work harder
Eliminate repetitive works
Reduce manual drag-downs and redundant formulas
Streamline workbook
No more helper columns or extra data massaging
Enable real-time calculations
Formulas recalculate instantly when data changes
Build smarter reports or dashboards
Cleaner and scalable visual reporting
Save times!
Minimize manual handling, maximize accuracy
Indra Pandu Prasetyo 9
Excel Tips
Downloadable Resources
Practice makes perfect
Dynamic Array Function Samples
[Link]/files/dynarray1
Dashboard Built with Dynamic Arrays
[Link]/files/dynarray2
There are many other powerful Dynamic Array functions worth
exploring — like VSTACK, HSTACK, SORTBY, TEXTSPLIT, and
more.
Start experimenting, try them out and get familiar.
Indra Pandu Prasetyo 10
Excel Tips
“Stay curious. Keep learning.
Keep optimizing. Let Excel do
the heavy lifting.”
Indra Pandu Prasetyo
[Link]/in/indrapandu
Planner · Project Control · Project Engineer
Automation Enthusiast · Lifelong Learner