Before jumping in to this Excel training, take a closer look at the dataset used in this course. In this tutorial, you will be shown how the accident metrics are organized by column, and how these important data sample fields will drive the Power Map visualization for this project.
- Alright, so let's kick things off with a quick overview of the data that we're working with. We've got raw data from the city of Cambridge, which includes records of all accidents that took place over a four year sample from 2010 through 2013. Based on that data, we've created a heat map to quickly visualize when certain types of accidents are most likely to occur. So, in this map, we're looking at accident frequency by hour of the day and by day of week, with the option to filter by different Accident Types.
So, Auto versus Bicycle versus Pedestrian, as well as by Season. We also have a real-time element that highlights rows and columns based on the current moment in time. So, if you're looking at this tool at 9:00 am on a Wednesday, you'll see that the accident frequencies from 9:00 am on Wednesdays are highlighted. So, the goal for this project is to add a completely new layer of insight by introducing geo-spacial visualization using Excel's Power map tool. This will allow us to analyze trend.
- 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