MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel 2020: Understanding Array Constants

September 17, 2020 - by

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.