MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Understanding Array Constants


September 17, 2020 - 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


This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.


Bill Jelen is the author / co-author of
Excel Insights – A Microsoft MVP Guide to the Best Parts of Excel

There are fewer than 100 Excel MVPs worldwide. 24 of them have contributed to this book. Written, edited, reviewed and printed by Excel MVPs, this is practical Excel passion undiluted, with each MVP highlighting some of their favorite topics.