Here is my use-case.
As a training specialist I want to look at a single tab in Excel and see student feedback at a glance. I want to see pie-charts showing who said "Yes" this course was a good use of my time and "No" this was not a good use of my time. Under each pie chart I want to see the list of comments for "Yes" and for "No". I want filtering for just my classes or for a specific class offering to be easy.
So I have a RawData data table with all my survey data on it's own tab. On my ResultsTables tab you have a set of pivot tables that correspond to each question (I only listed one question in my use-case to make it brief). On the Dashboard tab I have the pie charts laid out and slicers to make filtering easy.
Here is where I get stuck. I can't seem to force Pivot Tables to show the survey comments in a separate pivot table under the pie chart with a filter for "Yes" or "No" to help parse the comments based on their context. The comments are text-strings (e.g., You gve the most awsome class evr!)
Is there a way to force Excel 2010 to make it's pivot tables display the actual values of cells in your RawData while filtering for a specific subset?
Best Answer
Have you tried not putting your Comments field in the Value section but on the Row Label section, and displaying your pivot in tabular form?
As long as you are setting your filters correctly, only the comments for the courses you are interested in would display, and every time you refresh the data on one of your pivots, if they are copies of each other, they will all refresh at the same time.