1

I have a survey data set that I need to periodically update and produce reports out of in Excel 2016. I want to filter the data by location and then compare locations by year>quarter to a total from all reporting locations ("provincial") using some charts.

The data is categorical: column is called "location", and then there are about 20 locations available to select.

I "solved" this originally by creating pivot charts and converting them to formulas to create standard charts that allowed me to add provincial totals. This is not flexible though and I want to do a better job.

I tried adding a new column to the data that is populated by "Provincial" for each row. Now I can create a set of pivot tables for each question and connect them with a filter so that one pivot has the locations I want and the dates I want, while the second pivot has the provincial total filtered by the same dates. Only problem with this is that you can't create a chart that pulls data from two different pivot tables.

Example Pivot tables filtered to one location for 2019

One option I considered was to somehow duplicate each row but replace the location data for each with "provincial". This way I could filter everything and have provincial as a location option that has all of the data I want. I don't know how to do this in any kind of automated way though, and it needs to be flexible enough for me to add new data over time.

LeroyBrown
  • 55
  • 6
  • Are you able to provide a small set of your data, show us what you have done with it in pivot table and in charts at the moment, and show us what you want to achieve and where is the constraint? – Terry W Oct 17 '19 at 21:57
  • Unfortunately I can't provide the data itself as it contains sensitive information. I did post a link to one of the sets of pivots for one of the survey questions. It's filtered to a single location and a set year. The second pivot is exactly the same but instead of location it's using my makeshift field "Provincial" which I added to every row of the data. The crux of the problem is that I can't get Excel to combine data from two pivot tables to create a single chart, even if the columns are the same b/w them (see img above). I'd like to get that working or find a workaround. – LeroyBrown Oct 17 '19 at 23:04
  • You can provide mock data without actual information, also you did not show what kind of chart you are building and why it is not possible for you to create the chart? Do you know the concept of using dynamic named ranges in charts? – Terry W Oct 17 '19 at 23:07
  • I cleaned up the stuff that I can't share. Hopefully this is helpful. See tab Q2 as the example chart. It has 2 columns for the two clinics but I can't add the 3rd column for the provincial numbers. I am also not familiar with using dynamic named ranges in charts. Is this something that you can do by pulling from dynamic pivots or is it limited to non-pivot tables? EDIT: I'm not sure how to upload files here. This is a new account and I can't upload images either so is this a limitation for me? – LeroyBrown Oct 18 '19 at 15:27
  • Is the only reason for multiple tables due to only being able to generate a report for one location at a time? If so, append the tables in Power Query and then create your pivot chart from the consolidated data. – Jenn Oct 19 '19 at 20:00

0 Answers0