MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Understanding Array Constants


September 12, 2019 - by Bill Jelen

Excel Understanding Array Constants. Photo Credit: Dan Gold at Unsplash.com

There are several dynamic array that get better with an array constant. Here is a simple way to understand them. A comma inside an array constant means to move to the next column. A semi-colon means to move to the next row. How do you remember which is which? The semi-colon on your keyboard is located near the Enter or Return key which also goes to the next row.


On the US keyboard, a semi-colon is near the Enter key. When you see a semi-colon, think "new row". In this screenshot, ={1;2;3} entered in A4 fills A4, A5, A6 with 1, 2, 3. Commas mean new column. A formula of ={1,2,3} entered in A9 fills A9:C9 with 1, 2, 3.

When you see an array constant with a mix of commas and semi-colons, remember that each semi-colon moves to a new row.

Array constants can be a mix of semi-colons and commas. ={1,2,3;4,5,6} entered in G4 will fill G4:I5. You will have 1, 2, 3 in the first row and 4, 5, 6 in the second row.

Title Photo: Dan Gold at Unsplash.com


Bill Jelen is the author / co-author of
Microsoft Excel 2019 Pivot Table Data Crunching

Use Excel 2019 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours, to take control of your data and your business. Even if you’ve never created a pivot table before, this book will help you leverage all their remarkable flexibility and analytical power–including valuable improvements in Excel 2019 and Excel in Office 365.