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
MrExcel LX – The Holy Grail of Excel Tips

A book for people who use Excel 40+ hours per week. Illustrated in full color.