…is for Microsoft to fix how Sheet Protection and Tables interact. I want it more than world peace. I even want it more than cold beer. See…I even put it on my Christmas wish list:
What am I talking about? That template above is a great example: Let’s say I want to send you all some templates for you to fill out and send back. Being a prudent developer, I’ll use Excel Tables, and those tables will have a mixture of Calculated Columns with the formulas locked down (because I don’t trust you one bit), and free-text fields (because I want you to think that I care about what you have to say).
That’s what I’ve done above: I’ve got a RANK column that automatically assigns an incrementing integer to each entry, and I’ve got a freetext field where you can put your own requests, if for some reason you don’t share my world view. And I also want to let you insert additional columns, in case you want more than three things.
I better push Ctrl + F1 to bring up the Format Cell dialog, and make sure those genius formulas in that Calculated Column will be protected when the sheet is locked:
…and I better make sure that the ‘Request to Santa’ column is NOT protected:
Awesome…all I need to do in order to activate this protection is to lock the sheet, while still allowing row inserts and deletes:
And all is good, until some moron decides that Cold Beer comes in a distant third behind some additional thing they would like to add to this Christmas wish-list, like “New President”. So they right-click within the Table Row while Tra-la-laa-ing, only to be stopped mid laa: the Insert > Table Rows Above option is greyed out. :-(
But being persistent, they decide to force the issue by selecting the entire row, and then right clicking. And indeed, this time they encouragingly see that indeed the Insert New President… option is still available to them:
…only to find that Excel has trumped their desire:
But weirdly, when they click OK, Excel decides that it can in fact do it, kinda:
It begrudgingly inserted the new row, but it didn’t autofill the formula in the protected column. Meaning while our hapless agitator can fill out the freetext field with their new second-greatest wish, they can’t rank it. Meaning Santa might simply ignore it as invalid input:
God forbid they would want to do something equally trivial, such as extend the Table by either trying to type underneath it, or by selecting the bottom right cell and pushing Tab or Enter, because that accomplishes nada. As does Sorting and a myriad of other things that the Protect Sheet dialog swears it will let you do.
I just cannot fathom why this bug hasn’t been fixed. I recall Zack Barrasse talking about it years ago, along with some other stuff he’s mentioned on the Excel UserVoice site. Sure, I could write some VBA to add a custom ‘Insert Row’ entry to the right click menu, but I try to keep my templates macro-free as much as possible. Some absolutely need to be macro free. And I wouldn’t need macros anyhow if this functionality actually did what it says right there on the box.
Please go vote for this. And Microsoft, please go fix it…It’s got 191 votes already, and counting. And it is just sooo broken.