In Excel you can create arrays using almost same notation but:
- Use comma (or semicolon depending on regional settings) to separate values of same row.
- Use inverted slash
\to separate columns.
So let's make an array like this:
{1;3\2;4}
| Column 1 | Column 2 |
|---|---|
| 1 | 3 |
| 2 | 4 |
Just a few examples using INDEX:
=INDEX({1;3\2;4};1;1) will return value from row 1 and column 1 so it returns 1.
=INDEX({1;3\2;4};1;2) from row 1 and column 2 so it returns 3!!
Anyways, this notation is not used often in Excel. Normally arrays are used trough VBA and that would be a different question.
UPDATE: Looks like both separators depend on regional settings so maybe you need to figure this out. Thanks to @Jvdv for this source
UPDATE 2:: Thanks to @JosWolley for this:
If one isn't sure what the separators are for their version of Excel, it's quite easy to find out. Simply enter the formula =A1:B2 in cell C1 and then go into the formula bar, highlight the entire formula and press F9. For English-language versions this produces {0,0;0,0} (with A1:B2 empty)
In spanish version like mine is this:
Microsoft Support
support.microsoft.com › en-us › excel › guidelines-and-examples-of-array-formulas
Guidelines and examples of array formulas | Microsoft Support
Single-cell array formula In cell H20 of the example workbook, type or copy and paste =SUM(F10:F19*G10:G19), and then press Enter. In this case, Excel multiplies the values in the array (the cell range F10 through G19), and then uses the SUM function to add the totals together.
Reddit
reddit.com › r/excel › can somebody explain array formulas for me in a simple way?
r/excel on Reddit: Can somebody explain array formulas for me in a simple way?
May 3, 2019 -
No matter how many times I try to read up on it, I can't seem to grasp it.
How are array formulas useful? When do you know you have to use array formulas or when it is smart to use array formulas?
12:48
Excel Array Formulas Explained with MIN and IF Functions (Part ...
11:24
A Beginner's Guide to Array Formulas in Excel - YouTube
04:10
What is a Dynamic Array Formula in Excel? - YouTube
05:12
What is an Array Formula in Excel??? - Tutorial - YouTube
17:12
#️⃣ Dynamic Arrays in Excel - This Changes Everything! - YouTube
03:28
How to Create Array Formulas in Excel - YouTube
Microsoft Support
support.microsoft.com › en-us › excel › create-an-array-formula-in-excel
Create an array formula in Excel | Microsoft Support
Sum numbers that meet certain ... ... Excel provides two types of array formulas: Array formulas that perform several calculations to generate a single result and array formulas that calculate multiple results....
Exceljet
exceljet.net › home › glossary › array formula
Excel Array formula | Exceljet
For example, this array formula returns the total character count in the range B2:B11: ... The LEN function is given multiple values in the range B2:B11 and returns multiple results in an array like this inside SUM: ... where each item in the array represents the length of one cell value. The SUM function then sums all items and returns 43 as the final result. In all versions of Excel except Excel 365, many array formulas need to be entered in a special way to work correctly.
Becker
becker.com › blog › dynamic array formulas in excel: a practical guide
Dynamic Array Formulas in Excel: A Practical Guide | Becker
June 24, 2026 - A dynamic array formula is a special type of Excel formula that can return multiple values at once, automatically spilling those results into neighboring cells.
The Spreadsheet Guru
thespreadsheetguru.com › excel › excel formulas › all of excel's dynamic array functions
All of Excel's Dynamic Array Functions
March 19, 2024 - With dynamic arrays, when you enter a formula that returns multiple values, Excel intelligently spills these values into adjacent cells, dynamically resizing the output range to accommodate all the results.
GeeksforGeeks
geeksforgeeks.org › google sheets › array-formulas-in-excel
Array Formulas in Excel - GeeksforGeeks
October 4, 2025 - Type ={"Apple", "Banana", "Grapes", "Guava"} in the formula bar. Press Ctrl + Shift + Enter (for legacy arrays) or just Enter (for dynamic arrays in modern Excel).
Ablebits
ablebits.com › ablebits blog › excel › excel functions › excel array formula examples for beginners and advanced users
Excel array formula examples for beginners and advanced users
March 22, 2023 - So, what we have here is two arrays of Boolean values, where TRUE equates to 1 and FALSE equates to 0. Since we are using the AND array operator (*) in the formula, SUM will add up only those rows that have TRUE (1) in both arrays, as shown in the screenshot below: Note. Not all Excel functions that support arrays can convert TRUE and FALSE into 1 and 0.
Office Watch
office-watch.com › home › excel array formulas for everyone
Excel Array formulas for everyone - Office Watch
October 15, 2022 - They are made in memory, temporarily, for calculating the result. That’s faster than making more cells and columns. We’re used to Excel formulas that work off individual cells. Array formulas work with a range or list of values.
Top answer 1 of 2
5
In Excel you can create arrays using almost same notation but:
- Use comma (or semicolon depending on regional settings) to separate values of same row.
- Use inverted slash
\to separate columns.
So let's make an array like this:
{1;3\2;4}
| Column 1 | Column 2 |
|---|---|
| 1 | 3 |
| 2 | 4 |
Just a few examples using INDEX:
=INDEX({1;3\2;4};1;1) will return value from row 1 and column 1 so it returns 1.
=INDEX({1;3\2;4};1;2) from row 1 and column 2 so it returns 3!!
Anyways, this notation is not used often in Excel. Normally arrays are used trough VBA and that would be a different question.
UPDATE: Looks like both separators depend on regional settings so maybe you need to figure this out. Thanks to @Jvdv for this source
UPDATE 2:: Thanks to @JosWolley for this:
If one isn't sure what the separators are for their version of Excel, it's quite easy to find out. Simply enter the formula =A1:B2 in cell C1 and then go into the formula bar, highlight the entire formula and press F9. For English-language versions this produces {0,0;0,0} (with A1:B2 empty)
In spanish version like mine is this:
2 of 2
3
If you are on Microsoft-365 then could use VSTACK(), HSTACK(). Try this-
=VSTACK({1,2},{3,4})
Or using both VSTACK() & HSTACK().
=VSTACK(HSTACK(1,2),HSTACK(3,4))
Clarusft
clarusft.com › home › array formulas in excel
Array Formulas in Excel - Clarus Financial Technology
August 9, 2017 - However, some formulas do not return a single value. They return more than one value. The Excel output is therefore a rectangle of values (x rows by y columns). Most people would refer to this as a “matrix” but Microsoft choose to refer to these as arrays.
Lifewire
lifewire.com › array-array-formula-table-array-3123371
Here's How Arrays, Array Formulas, and Table Arrays Are Used in Excel
November 13, 2019 - In Excel, array formulas are surrounded by curly braces " { } ". These braces cannot be typed. The braces must be added to a formula by pressing Ctrl+Shift+Enter after typing the formula into a cell or cells.
Vertex42
vertex42.com › blog › excel-formulas › array-formula-examples.html
Excel Array Formula Examples – Simple to Advanced
February 10, 2025 - You don't actually type the curly brackets { }, but in this article I will surround all array formulas with brackets to indicate that they are entered as CSE formulas. In the evaluation steps shown in the above example, you'll see that Excel is multiplying each element of the first array by the corresponding element in the second array, and then SUM adds the results.
LinkedIn
linkedin.com › pulse › using-array-formulas-excel-winston-underwood
Using array formulas in Excel
It will walk you through the formula and expand each array so you can actually see what is going on. *: I have no idea why the answer is 72, instead of 70, which is what I would thing would happen; instead of taking the first value, it appears it is taking the second value of the array. If you know why Excel does this, I would love to know.
PerfectXL
perfectxl.com › home › what is an array formula
What is an array formula in Excel // PerfectXL Academy
April 4, 2023 - But when using the array function, we don’t need the intermediate results to calculate the grand total. We simply select the cell in which we want to publish the total fruit revenue and we enter the formula ‘=SUM(B2:B9*C2:C9)’: Again, remember to click CTRL+SHIFT+ENTER to reveal your results! Only pressing Enter will result in a standard Excel error: ‘#VALUE!’. After executing the function correctly, the grand total will show in the designated cell and again, braces { } will appear around the initial formula to indicate it concerns an array formula now.
Acuity Training
acuitytraining.co.uk › news-tips › excel-array-formula
Creating Arrays [Excel Array Formulas] -
February 3, 2026 - To enter the following formula, select cells E3:E9 where the net sales against each product are to be populated. ... And just like that, all your net sales have been calculated! Then, if you want to sort through the data, you can use the SORT and SORTBY functions, specifically designed for arrays. Excel array functions are incredible functions of the Excel.
Ablebits
ablebits.com › ablebits blog › excel › excel functions › array formulas and functions in excel - examples and guidelines
Array formulas and functions in Excel - examples and guidelines
March 22, 2023 - In other words, an array formula in Excel evaluates all individual values in an array and performs multiple calculations on one or several items according to the conditions expressed in the formula.
Quora
excelhacks.quora.com › How-to-use-array-formulas-in-Excel-What-is-an-example
How to use array formulas in Excel? What is an example - Excel Hacks - Quora
Answer (1 of 2): Array formulas in Excel allow you to perform calculations on multiple items in an array rather than a single cell. These formulas are powerful and can simplify complex calculations. Here's how you can use array formulas in Excel, along with an example: How to Enter an Array Form...
Real-Statistics
real-statistics.com › home › excel environment › array formulas and functions
Array Formulas and Functions | Real Statistics Using Excel
July 11, 2026 - This means that you need to type =SUM(B4:B7*C4:C7) and then press Ctrl-Shift-Enter. If you forget to press Ctrl-Shift-Enter and only press Enter, you will get an error message. A few of Excel’s built-in functions are array functions, where the output of the function is an array.
Excel-accountant
excel-accountant.com › index.php › article › dynamic-array-functions-and-formulas-excel-365-features
Excel 365 Dynamic Array Functions and Formulas | Excel Accountant
Excel dynamic array update is not available with Excel 2016 and Excel 2019 but you get it with the Microsoft 365 subscriptions supported by Windows and Mac both. The main advantage of Dynamic Array formulas over the normal formulas is, normal formulas return the solutions in a single cell whereas Dynamic Array formulas result takes a range of cells i.e.
Call: +16466885128
Address: 1467 Silver Leaf Dr, Lakeland, 33813, Florida