…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.
Jeff, this was my only request on User Voice. It got folded into Zack Barresse’s that you linked to, but if you go back to January 23, 2016 of the comments you can see the request, which should look very familiar. I had hoped it would be done in time for Christmas for you, but so far no such luck.
205 votes and counting. Another 30 votes and we’ll make it to the first page of the Excel UserVoice site. Spread the word, folks.
” I want it more than world peace. I even want it more than cold beer”
How about more than All Blacks to continue beating Wallabies?
(I’ve voted in favour anyway :))
When you insert a row into a table, two things happen: a row is inserted; formulas are written to cells in the new row. At least that’s how it’s supposed to work. In your scenario, the protected sheet message isn’t displayed when you insert a row, it’s displayed in step 2: write formulas to new rows.
I’m not saying it’s not broken – it is. It’s just not weird.
What about this to protect table formulas: Add data validation
@Dick: Clever with the validation. I believe you have hit the nail on the proverbial head in your first comment. I’ve asked Microsoft countless times what the actual process is for inserting new rows into a Table, because it obviously doesn’t handle like a normal row insertion. Since data below the Table doesn’t move (except certain conditions), this always led me to describe it as row *consumption* as opposed to *insertion*. What I think happens is:
1. Insertion
2. Deletion/Adjustment
3. Update
The deletion/adjustment would be the size of rows from the insertion, but performed after the Table’s last row, removing rows to compensate for the shift. Hence why data below the Table (not abutted to it) would appear to not be moved down.* The update is for calculated columns. These don’t auto-update either, as sometimes users can have formulas in a Table column but it not be a true calculated column.
* Again, for clarity, this is not in all circumstances.
@Jeff: Very well put, and I obviously agree wholeheartedly. I actually spoke to several project manager’s at Microsoft about this. I’ll bet you a pint that you’ll never guess what they told me in response to me saying this was a bug. Take a moment. Think. Get those creative juices flowing. (Also, I’m going to collect from you next time I’m in New Zealand.) ….. ….. ….. It was told to me – on multiple occasions, this was “by design”. Trust me, I went around, and around, and around, trying to make this project manager understand how this was an actual bug. The amazing part of those conversations is that they actually argued with me that it’s currently working how users think it should work. It was one of the most ridiculous conversations I’ve ever had with anyone on Excel, comical at points.
Intelligent design? If that’s what they think, then I vote for some evolution instead. This is so a bug.
* If you unprotect the cells in the calculated column and then lock the sheet while allowing row inserts, then when you insert an entire row in the sheet the unprotected formulas get copied down just fine.
* If you protect the cells in the calculated column and then lock the sheet while allowing row inserts, then when you insert an entire row in the sheet you get an error message and the protected formulas don’t get copied down. In effect, the status quo here allows users to delete a protected formula.
Dick: You’ve lost me. How does that help?
You make the calculated cells unlocked and the dv prevents the user from overwriting them.
But it doesn’t stop users from deleting them. Which they will do.
Ah, deletion. I hadn’t thought of that.
Ah well…this post has garnished an additional 30 votes and counting. So who knows…Santa may be kind. Not that I’ve been that good this year.
I should have run with a Dickens’ theme for this post: The Excel of Christmas Future.