By modifying a pivot table’s design settings you can eliminate blank rows and subtitles to help create a clean data source to import into Power Map. In this Excel training video, you will apply your pivot table to generate source data for maps of each accident type, as well as learn how to create new tabs.
- Alright, we've got our pivot table in place. Now it's time to use it to generate the source data for each of our maps, one for each accident type. And we can do this by modifying the pivot table design settings to eliminate some of these blank rows and subtitles, which will give us a nice, clean source data that we can feed right into Power Map. So the first thing we'll do in our pivot table is go in the PivotTable Tools Options, jump into the Design tab, and under the Report Layout Options, what I want to do is show this data in tabular form and I would like to repeat all item labels.
Next thing in the Grand Totals menu, I want to turn those totals Off for Rows and Columns. And I also want to Not Show Subtotals. And as you can see this has kind of reorganized our data view into a much cleaner, more compact format. And this is going to give us the format that we need to treat it as a new source data table. Last thing I'll do here, obviously there's some inconsistencies with the data. We have some data coming in with a latitude and longitude of zero.
All I'll do is just filter that out. So I'll uncheck the zero in my latitude field and that will clear that field out. So now we've got table formatted exactly like we want it. The last step will be to filter by each accident type. We'll start with Auto, just selecting YES in the Auto filter and what we're going to do is move this raw data into a new tab called Auto Accident Locations. So I'll add a new tab here. Again, rename it Auto Accident Locations.
And go back to the pivot and just select all the data from A5 and then control, shift, arrow down to D2902, control c to copy that. I'll go into that new tab that I just created and I'll paste it right here. Now the only other adjustment that I need to make is to rename cell D1 from Count of Crash Number to something that indicates to me that these are auto accidents that we're looking at. So I'll just relabel this Auto Accidents.
And then we're good to go for this tab. Need to do the exact same process for bicycle and pedestrian accidents. So why don't we go ahead and just create those two tabs right now. Sheet5 I'll rename to Bicycle Accident Locations. Sheet6 I'll rename to Pedestrian Accident Locations. Go back to the pivot and now rather than filtering on Auto, I'll clear that filter by selecting ALL.
Now I'll choose Bicycle equals YES. Grab the same array of data that I did. In this case it takes me down through row 519. Press Copy. Drop it right into that Bicycle Accident Locations tab that we created, and then again just to add a more descriptive label to column D, change this to Bicycle Accidents. And last but not least, back to the pivot one more time. Unfilter the Bicycle filter. Choose Pedestrian equals YES.
Grab the array from our pivot table. Copy it. Drop it right into the Pedestrian Accident Location tab. And then relabel D1 Pedestrian Accidents. So there you go. Now we have three new raw data tabs that will serve as the source tables for Power Map.
- Reviewing the dataset
- Standardizing location data
- Creating a new data source in a PivotTable
- Creating new Power Map images
- Populating Power Map images with INDIRECT
- Linking to map images with HYPERLINK