I am only starting to get to know my way around pivot tables and what it does. In most tutorials there are dates and numbers, which seem to make things easier, but I have mainly text.
I have a table with all kinds of “actions”. Each action is a row. Per column I define all kinds of progress reported, so I have everything in one place. If I need to make a report or a new table with certain info, I use index/match to get everything I need. I created a version of this and attached it to this post.
I am trying to create a dashboard, and they all filter on the domain name and the company.
On one part of the dashboard, I would like a stacked column chart. Where the first bar says that on meeting date 1 progress was reported as "2 actions Green; 2 Orange; one Red and one on hold". The second bar says that for meeting date 2 there were "2 actions Green, 2 orange, 1 red, …"
But no matter how I’m trying to put this in a pivot table, it doesn’t seem to work. It works on just one Color status date. Then I can get one stacked bar. But the moment I try the second date; it just puts everything together in one stacked bar; not a second one.
And that’s even without trying to filter on company and/or domain name so I can put it on the dashboard.
edit: I use excel 365; windows computer.
Hi Eva,
You need to lay the data out in a tabular format with numeric values for the things you want to count.
If you look at the attached file you'll see I've done this and created a table from the data. This can then feed a pivot table and you can create a pivot chart from that.
Regards
Phil
Thank you for this!
I read the post on tabular data. It is kind of what I tried to do. Keep everything on one row, but unfortunately the basis of what I'm gathering are the "actions", rather than dates. There's also lots of text instead of numbers. Maybe excel wasn't the ideal format.
I have tried to implement your proposed solution, but I feel I need to stick with my original layout of gathering the info per action. I have 70-something of actions that I need to gather information on. Every month or so I have a meeting with different projectleaders and they give me information, that I keep in this table. So I now created an extra worksheet to apply the format you suggested, but then when I add the graph to my dashboard that I already created (where I want to filter on domain and company), the slicers don't connect to the new graphs: probably because the data isn't coming from the same single worksheet? "report connections" doesn't show my new tables. If I create new slicers, it doesn't recognise the old ones.
Any more ideas?
Maybe I should try to make a more extensive dummy workbook so I can better show what I want?
Hi Eva,
Your Slicers are connected to the original source data. If you created a new version of the data for your charts, then you need to start from scratch and insert new PivotTables and Slicers.
When you create a PivotTable, Excel creates a copy of the data and puts it in the Pivot Cache, which is what the PivotTables & Slicers reference. If you have multiple different tables containing source data, then each of these will have its own Pivot Cache. More on what the Pivot Cache is here.
You could keep your data in a layout that makes sense for data entry, and then use Power Query to unpivot it into a format that's suitable for your PivotTables and charts.
I hope that points you in the right direction.
Mynda
