MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Protect Rows with an Array Formula


July 31, 2019 - by Bill Jelen

Excel Protect Rows with an Array Formula. Photo Credit: Jon Moore at Unsplash.com

Here is an odd use for an array formula: Say that you don‘t want anyone to delete or insert any rows in one section of a worksheet. Scroll far to the right, off the screen, and build an array in those rows. Select Z1:Z9. Type =2 and press Ctrl+Shift+Enter. You can use any number, =0, =1, =2, and so on.

Instructions on the worksheet ask people not to delete any rows. Someone will try to delete a row. Off to the right, select Z1:Z8. Type =2 and press Ctrl+Shift+Enter. This creates an array in those rows.

If someone tries to delete a row, Excel prevents it and shows a cryptic message about arrays, shown below.


When someone tries to delete a row in the area of rows 1 to 8, a message will appear with "You Can't Change Part Of an Array."

Thanks to Excel Ace and Tracia Williams for suggesting this feature.

Title Photo: Jon Moore at Unsplash.com


Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.