Excel 2020: Protect Rows with an Array Formula

August 03, 2020 - 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

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