Dashboard prettiness in Excel
BetsyD
Cincinnati, OH Icrontian
So, in my new role I'm supposed to update a dashboard that the previous incumbent created. Generally I consider myself fairly high-level on my Excel skills but I am truly stumped and the Google has been of limited help for the first time ever, so I hope one of you can fill in the blanks for me.
The old-me created a dashboard with a source table and then turned it into a pivot table with red/yellow/green indicator icons based on the number (3,2,1 respective) in the status column of the source table. I updated the source table to the projects for 2016, updated the dates to 2016 dates and removed the old status and put in the statues for Jan-2016 and then refreshed the pivot tables. The project names updated and the timeline updated but the indicator icons not only disappeared, I can't even figure out how to let it just be a "regular" sum pivot table until I figure out the icons.
I found exactly 1 article that points me to the right thing but I feel like I'm missing something when it says "Edit the widget's script". Where exactly would I find the widgets script on a pivot table?
(I can't provide the actual dashboard since it has confidential project names and budgets in it, sorry)
Comments
I know pretty much zero about Excel so I can't help you there, but would it be possible to get a copy of the spreadsheet with all the confidential data blanked out (all dollar amounts set to $1.00 or something and all names changed to 'dickbutt', etc.). Might help people help you better.
I can't find anything on "widgets" that isn't a third-party add-on. Agreed that if there was any way to share the table with confidential data removed, it would be easier to diagnose.
If the widgets are screwy, it sounds like you could accomplish something similar with conditional formatting. E.g. make a cell red if the value in some other cell is 3. I've gotten that to work before, although I only try about once a year and it always takes me some experimenting to get it right.
Yeah, I experimented with a fairly simple pivot table, then just highlighted the cells and applied a red/yellow/green stoplight conditional formatting to it to see if that mimicked what you were looking for, but that should have survived if you just recalculated the pivot data, so I figured that probably wasn't it. Could be an okay stand-in, though.
Yea, the audience (the global CIOs) like the bubbles not the conditional formatting. I saw earlier versions of the dashboard that were rejected that used versions on conditional formatting and/or windings as the items on the dashboard.
I wouldn't even know where to start zeroing/changing because (some of the source data, though not the data I'm currently mucking with) is linked to nearly a dozen other financial spreadsheets. My theory is that I can simplify it but I need to understand it first. Chances are if I start breaking the links and zeroing I'll screw something else up that I didn't know about.
I'll keep fiddling with it. I have an email out to the original author but he hasn't been very helpful on other topics I've reached out to him on now.
Script? Like VB script? If so you'd access that via the script editor, which in all the versions of Excel I've used (through 2013) is accessed via Alt+F11.
Harglebargle! It was conditional formatting using a thing under it called "Icon sets" and then some random settings under there to remove the actual values... How does The Google not know about this?!
I was right!