Excel 2020: Protect Rows with an Array Formula
August 03, 2020 - by Bill Jelen
![Excel 2020: Protect Rows with an Array Formula Excel Protect Rows with an Array Formula. Photo Credit: Jon Moore at Unsplash.com](/img/excel-tips/2020/08/excel-2020-protect-rows-with-an-array-formula.jpg)
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.](/img/content/2020/08/LIV119.png)
If someone tries to delete a row, Excel prevents it and shows a cryptic message about arrays, shown below.
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.