Seven steps to (almost) protected Calculated Columns

Earlier I complained about how you can’t lock down Calculated Columns in Tables, while still allowing users to insert new rows. Dick gave me half a hack in the comments of that post, and I’ve come up with the other half to give you an almost-foolproof way to protect those precious Calculated Columns from your almost fools.

Step 1: Unprotect the entire rows that your Table occupies.

Step 2: Park a shape over the Calculated Column. Leave it’s protection as the default ‘Locked’. This will act as a shield once the sheet is protected.

Step 3: Format the shape so that it has no border, and a white fill that is 99% transparent. (If it’s 100% transparent, then Excel will let users click right through it, to the cells behind).

Step 4: Add some DV that stops users from changing the formula in the column, should they navigate around the shape shield via using the arrow keys. Dick’s formula below ensures that someone will have to spend an awful long time typing before they are allowed to overwrite the existing formula.

Step 5: Add a pop-up input message that politely warns them not to mess with your calculated column, should they manage to reach it using the arrow keys. This is still needed, because while they can’t change the formula, they can still clear it using the Delete key.

Step 6: Protect your worksheet in a manner that allows row insertions/deletions.

Step 7: Go vote for Zach Barresse’s UserVoice request for Microsoft to fix Tables so that you don’t have to use 6 hacks to work around their “feature”.

This works a treat: Try as they might, they can’t select the unprotected cells in the Calculated Column by clicking on them. If they click in the bordering cells and use the arrow keys to navigate behind the shield, they get warned off politely:

…and yet they can still insert new rows:

…and as you can see, the unprotected formula in the calculated column copies down just fine.

Identifying PivotTable Collisions

So you’ve got a file with dozens of PivotTables in it. One day you hit Refresh All, only to see this complaint:

…or perhaps this variant:

What’s happened is that the size of the PivotTable has increased, and the PivotTable is now trying to occupy space where another Pivot or Excel Table already lives. So you know what has gone wrong. But you have no idea where. You’re either going to need to eyeball each and every bit of each and every worksheet (including the hidden ones), or you’re going to have to run a bit of code. Something like this:

…which does this:

A (refactored) Christmas Carol

Our story begins on a cold and bleak desktop. Excel, an aged application , ignores an invitation to Christmas dinner from his nephew UserVoice. Excel turns away desperate pleas from 222 voters who seek an upgrade to Tables in order to provide data integrity and usability for their poor users. Excel only grudgingly allows his overworked, underpaid Tables to be used while a sheet is protected, and even then, only to conform to the bare minimum of the existing Sheet Protection functionality.

At home that night, Excel is visited by Lotus 1-2-3’s ghost, who wanders the Earth, entwined by heavy patents and dependency chains forged during a lifetime of greed and selfishness. Lotus 1-2-3 tells Excel that it has one chance to avoid the same fate: Excel will be visited by three spirits and he must listen to them or be cursed to carry dependency chains of his own, much longer than Lotus 1-2-3’s chains.

The first of the spirits, The Ghost of Microsoft Past, takes Excel to scenes of Excel’s boyhood and youth, reminding him of a time when he was more innocent. The boyhood scenes portray Excel’s lonely childhood, his relationship with his beloved sister Word, and an office party hosted by his first employer, Mr. Gates, who treated Excel like a son. They also portray Excel’s neglected fiancée Access, who ends their relationship after she realizes that Excel will never love her as much as he loves unstructured data.

The second spirit, the Ghost of Microsoft Present, takes Excel to a joy-filled help forum of people gathering the makings of a PowerQuery, and then on to a celebration of business intelligence in a data-miner’s PowerPivot data model. They then visit Acme Inc, where we meet Jolly Jeff, an otherwise happy analyst who’s Table-driven macro-free Excel templates are seriously unprotected. The spirit informs Excel that Jolly Jeff’s carefully constructed templates will soon be corrupted by ignorance and laziness unless the course of events changes. Before disappearing, the spirit shows Excel two hideous, emaciated columns of data named Calculated_Column and Freetext. He tells Excel to beware the former above all and mocks Excel’s complete lack of concern for their unprotected state.

The third spirit, the Ghost of Microsoft Yet to Come, shows Excel a business office in the future. The ghost shows him scenes involving the death of a disliked application. The application’s funeral will only be attended by local businessmen if donuts (of the charting variety) are provided. When Excel asks the ghost to show anyone who feels any emotion over the application’s death, the ghost can only show him the pleasure of Stephen Few. The ghost then shows Excel the application’s neglected grave:

Excel: 1985 – 365.
Here lies one who frivolously cavorted with the entire office while not using adequate protection.
Recalculate AND Die.

Sobbing, Excel pledges to the ghost that he will change his Table Protection to avoid this outcome. Excel awakens the next day a changed program. Excel spends the day with Mr Gates’ family and anonymously sends a large update to Tables to the Weir home for Christmas dinner. From then on Excel began to treat everyone who uses Tables in a protected workbook with kindness, generosity and compassion, embodying the spirit of User Experience.

What I want for Christmas

…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.

Inconsistent ListRow Copy

Here’s a cautionary tale for you. Let’s say you want to let users copy rows from a Source table row by row to a Dest table, by pushing a button:

…so you whip up a bit of code like this, and assign it to that button:

And when you run it, it works just fine:

…until that is, someone hides an entire column in the source table, sets a filter that similarly hides some rows, and leaves a cell in that Table selected before running the code again, in which case you get this:

As you can see from the above:

  • For any ListRows in your source that happen to be visible, only the cells from the visible columns get copied, to a contiguous block in the Dest table, but
  • For hidden ListRows, all cells get copied

Add to this the fact that everything works just fine if the user happened to select a cell outside the table before triggering the code:

…and you’ve got the makings of a hard-to-diagnose bug that will eat up hours of your time trying to replicate.

The fix? Don’t use the .copy method. Just set the values of the second range directly to the values of the first:

…which works fine, and is faster anyhow:

In case you’re wondering what happens if you bring the whole DataBodyRange through from the Source Table using that dangerous .Copy method i.e using code like this:

…then again the results depend on whether a cell is selected in the Source table:

…or not:

Again, avoid the inconsistency by setting the values of the second range directly to the values of the first:

…which works fine, fast:

Here’s a sample file:LRCopy Test

Junk Chart? Cover Art!

Yes, I have all of Edward Tufte’s beautiful books, as well as quite a few from Stephen Few and others. Yes, I know that you shouldn’t embellish charts with images and unnecessary bits for the sake of it. But even so, I just love this cover from The Economist that just arrived in the mail:

It’s not a junk chart when it’s cover art.

If only someone in the PowerBI community would create a custom visual for this, and upload it to the Office store. Actually, let’s go one bigger: If only Microsoft open-sourced the Excel visualization engine like they have done with PowerBI visuals.

Don’t know what I’m talking about? Microsoft enables external developers to create their own custom visuals into Power BI, and even better, to share those visuals with the wider PowerBI community. Heck, they’ve even published the code for all their own Microsoft visualizations to GitHub, so that developers can study them, learn from them, and improve on them. Meaning developers can create new charts that Microsoft haven’t got around to making yet, haven’t thought of yet, have stuffed up, or wouldn’t bother with.

Imagine how cool life would be if we could do this too.

Cue dream sequence

Say you’ve got some internal migration data sourced from a couple of Population Census tables. And say you want to see what net inflow and outflows look like in your home town. You go look on the ribbon for some suitable geographical chart to display the data, but find the cupboard is pretty bare:

So you head over to the Office store, and see something that looks promising, put together by some generous non-Microsoft developer with far too much time on his or her hands:

You download and import the visualization template into your Excel project, see it’s icon appear in the ribbon, click it, connect the resulting chart up to your own data, and reap the insights:

Man, that kind of custom functionality would be enough to make Jon Peltier do this:

Like my dream? Vote for it at UserVoice. Your vote matters.

Look Ma…No PowerPivot!

So back in 2015 when I was trying to write a book, I took time out to rant that despite every version of Excel since 2013 having the Data Model baked in as standard, it wasn’t a heck of a lot of use to you in the event that:

  1. You wanted to mash together say a UnitPrice from one Table with a SalesQuantity from another, in order to display the derived Total Revenue in a PivotTable; and
  2. You were a tightwad that didn’t happen to have one of the premium PowerPivot SKUs installed.

Because while you could create a relationship between two Tables without PowerPivot:

…the option to create a calculated field to multiply Qty against Price was greyed out.

Disheartened with the lack of functionality in the non-Premium-Excel space (and sternly talked-to by my wife about the need to shave and go out earn a crust) I flushed the nearly-finished manuscript down the toilet in disgust. But now that I’m flush again, I’ve dusted off that manuscript (it wasn’t really down the toilet) and am updating it, so that I can nearly-finish it all over again.

Anyways, I was subsequently looking at this DataModel thing again, and rewriting the bit that said how sucky it is that you need to pay a premium in order to do a pretty standard thing, when I came across this:

Wow…It IS possible. Tight-wad analysts of the world: You too can do PowerPivoty stuff, without paying a cent more. You heard it hear first. Or second, rather.
Thanks, Mike Girvin, from the bottom of my wallet. :-)

Look Ma…no PowerPivot:

I wonder what limitations this PowerPivot back door has, if any?

I found this from Chris Webb’s blog:
PowerPivot has been integrated into Excel, kind of. This means that the xVelocity (aka Vertipaq) engine is now native to Excel, and you can do all the basic PowerPivot stuff like loading vast amounts of data from multiple data sources and querying it via PivotTables directly in Excel, without installing any extra addins. PowerPivot does still exist as an optional extra however: you need it if you want to use the more advanced functionality that exists in PowerPivot today, such as filtering data before import, using diagram view, defining hierarchies and perspectives and so on.

And further down the page there’s some great commentary between Chris and Colin Banflied, including this from Colin:

  • xVelocity is divorced from PowerPivot, and built into Excel
  • PowerPivot is now simply a UI to administer the data model.

I’d always thought that while any edition of Office 2013/2016 can interact with a PowerPivot Excel Workbook (open it, click on slicers, change the pivot table etc.), if you wanted to “author” the Model you would need the correct version of Office, which includes the Power Pivot authoring functionality i.e. the PowerPivot window, which lets you define relationships/measures etc. But to some degree it appears not. Which has got me wondering…to what extent is PowerPivot just a UI for the DataModel baked in to every model of Excel? Sure, it can help you filter data before import, but so can PowerQuery, which is free in every version of Excel 2013 or later. Meaning there’s probably nothing stopping a savvy Excel VBA developer from simply rolling their own UI to help users of any post 2010 version of Excel do 99% of what they’d likely otherwise need PowerPivot for.