I’m sure you’ve seen this before – someone’s opened one of your lovely reports in Excel and made a “notes” field of some type just to the right of your pivot table. They start filling in some information, but when the data gets refreshed, lines are removed or added, and the added column values no longer line up with the data on the left…
Ok, so it might just be me. At any rate, I’ve seen this happen enough that I figured out a way that you can create an updatable column inline with your pivot table lines.
In this instance, I’m going to use a query which only pulls a very small amount of data – let’s say you wanted to see a Project, its start and finish dates, and add a note about what is wrong with said data or something.
I’m going to monopolize on the fact the the Project UID is unique and doesn’t change (unless you delete the project and re-create it) here. Trick 1 is to get Excel to play nice with a UID. If you just put it into the query, it won’t show up. So you have to get fancy in your query and insert something like this so it will show up:
Now you’ll get to see that ugly sucker right in your report. Don’t worry, we’ll deal with that part later.
Now, you can replace that ID (as long as it’s not in the values area) with text (which the pivot table will remember!) as long as the value is always unique. If you find that you have to write the same remark more than once, add an extra space or some kind of punctuation mark. I’ve trained a lot of people in our PMO to do this, so don’t stress, it’s not too hard.
But what about these pug ugly UIDs junking up my view?
Never fear, the @ sign is here! At least, that’s the character that I decided to use as my ‘signifier’ so that I can use conditional formatting to hide values I don’t want to see. You could use another value, but I find the @sign to be good enough. To do this, go back into your query and and +’@’ to your select statement, like this:
Now set up some conditional formatting to look for the @ sign and “semicolon out” the values which contain that @, using the same methods described in my Remove Those Blanking Blanks post.
Looking good! The only remaining thing to remember is that you can’t just delete a comment, you have to add the @ symbol to the end of your unique comment so that it will disappear.
So there you go! We use this in a lot of our reports and it works like a charm. I use it at a task level all the time too, be creative! Anything that has a unique ID can be added this way. As always, the whole query can be found below.