Today’s tutorial will present how to use the new PowerView add-in in Excel to produce simple dashboards and maps.
Not being trained as a GIS officer, I’ve always been a bit jealous of my colleagues who could produce nice maps in only a few clicks. However, learning GIS systems such as ArcGIS or QGIS looks a bit complex for newbies. If you’d still want to impress your GIS colleagues – or just don’t have any one around to do your mapping for you 🙂 – we’re going to discover today a new tool that allows user-friendly and simple creation of dashboards for quick reporting, including some mapping functions.
This tool is based on a software many of you already master, Microsoft Excel: it’s an add-in called PowerView that has been introduced in the 2013 edition.
How the Geocoder Works. ADDRESSES LATITUDE, LONGITUDE. Geocoding is the process of finding geographical coordinates (latitude/longitude) for an address or place. This is useful for plotting addresses on a map to determine voting demographics, analyze law enforcement and intelligence data, deliver insight into public health information, visualize distances such as roads and pipelines,. How the Geocoder Works. ADDRESSES LATITUDE, LONGITUDE. Geocoding is the process of finding geographical coordinates (latitude/longitude) for an address or place. This is useful for plotting addresses on a map to determine voting demographics, analyze law enforcement and intelligence data, deliver insight into public health information, visualize distances such as roads and pipelines,.
There are currently 4 options to create maps with Excel:
- With a background map in Excel as Dashboard option
- With Bing Map, presented in a blog post last year by Sylvie(in French)
- Mapping points in Excel with PowerView, that we’ll learn today 🙂
- Using Power map in Excel
For this tutorial, we’ll need 3 things:
- Excel 2013 with the adds-in PowerView + PowerPivot installed (Office Professional Plus)
- An Internet access for the Map Tiles (based on Bing Maps)
- A set of geo-located point you want to feature on a map. If you don’t have your own data, you can use this example spreadsheet, with data from a school survey in Central African Republic produced by OCHA.
- An up-to-date version of Microsoft Silverlight.
Open the spreadsheet containing your data in Excel 2013 and go to the Options menu. In the “Adds-in” section, at the bottom, select “Excel adds-in” in Manage and click the “Go” button:
In the window that appears, select “Microsoft Office PowerPivot for Excel 2013” and “Powerview”.
You’ll notice that a new tab appeared in the Excel top menu:
In this new Powerpivot menu, click on the “Add to Data Model” button – you’ll need to have opened a valid dataset file like the one provided earlier.
A new window will open, just close it, we don’t need to edit anything here for a basic use (if you click again on the “Add to Data Model” button, you’ll notice that Excel will refuse to add the same dataset again). Excel automatically selected all your data.
We are now ready to go to the “Insert” menu and click on the (new too) “PowerView” button:
After a few seconds, Excel will create a new sheet for your PowerView dashboard:
The Design tab should also automatically open. This menu will allow you to add the different elements of your dashboard: charts, tiles, and what’s interesting to us here, maps:
Click on the “Map” button. Excel will automatically select some data to prepare the map, however it will most likely not suggest what you’ll want.
Use the menu “Power View Fields” on the right to select the data you want to display, using drag & drop. In this basic example, the location of schools is displayed (Longitude = X, Latitude = Y) with circles proportional to the number of students (Size = NB_ELEVES).
Excel should automatically zoom to the area where your data is (in this example, the Central African Republic) and select a sample if there is too much data.
A few tips:
- You can substitute the Latitude/Longitude by an address, using the Locations field. In fact, the Locations field cannot be empty, even if you don’t actually use it to locate your data: if you intend to use X/Y columns and not addresses like in our example, use a dummy column for the Locations field. Just make sure you’re using a column on which every line has data, e.g. the ID column (if not, the empty lines won’t be displayed).
- The character means that a field requires numeric data (size), and which columns fit it.
- Use the button on the top right of the map to maximise it on the sheet.
- You can display more information on each location by scrolling over it, it will display an information bubble.
- Excel automatically includes the area and level of zoom to display all your data, but you can also move manually on the map like on any webmapping platform.
- To remove a criteria, click on the drop-down menu on the right of each field and select “Remove field”.
You can also play with the Colour of the circles, for instance here differentiating the schools based on the CYCLE column:
The field Vertical multiples and Horizontal multiples allow you to display several maps based on a column, for instance here the REGION_PCO to show several mini maps based on the region:
Finally, you can add Filters, also by dragging & dropping columns in the corresponding menu. For instance here only the private schools are displayed, using STATU_ECO column as filter:
You now have seen all the important aspects of PowerView mapping, we’re now ready for a small exercise!
Want to impress your colleagues with this magical add-in? Not so fast, we first need to make you’re fully proficient with your new toy 😉
Here a quick exercise to check it, you have 10 minutes to perform it:
“As a WASH coordinator you need to target all schools in the region “Equator” which do not have any latrines yet for your programme planning cycle. For now you focus on schools with more than 500 students. Prepare a dashboard to plan your intervention”.
… and it’s time! Did you managed to get that dashboard? Let’s check if you followed the procedure well:
- Insert a new Power View into your Excel Table and create a map
- Map out all schools, using Latitude/Longitude (but also filling the Locations field as explained above), and filter only the ones with REGION_NOM “Equator”
- Filter for all schools without any latrines (LATRINES_S)
- Filter for schools with more than 500 students (NB_ELEVES)
The resulting map should display 13 schools:
If you want to improve your dashboard even more, you can display the Size of the circles based on the number of girls (NB_FILLES) to prioritize the schools with more female students since not having latrines is a protection concern. You can also display Colour based on Construction type (TYPE_CONST) to give pointers to your construction team:
So, based on this dashboard, which schools would you target first?
IV.1. Pros & cons
As you’ve seen, this technique offers interesting advantages, however it also has its disadvantages. In a nutshell:
Pros | Cons |
| Filtering | Resource-consuming for your computer |
| Mapping without much data treatment | Mapping only works when online |
| Mapping locations based on two columns (GPS coordinates and addresses) | Map can only be printed or print screened (no export options as graph) |
| Mapping of categories based on colours | The size of the points cannot be changed easily |
| Mapping of point sizes based on numerical value | A bit more complex and some more advanced knowledge is needed |
| Mapping data out of more than one table |
IV.2. Potential use cases
Still wondering how you can use PowerView for your missions? No worry, we’ve also thought of that. Here’s a list of potential situations in which you could utilize this new tool:
- Mapping out infrastructure points with additional attribute data, such as schools, water points, health centres, etc.
- When you need to visualize data spatially based on more than one database/table: like 3W data with programme intervention to demonstrate that there is no duplication (donor reporting)
- When you need to visualize MDC data which you don’t want to treat any further, can be combined with Power Pivot Charts and Tables
- When you display statistical data linked to points and there is no GIS capacity available
We’ve reached the end of this tutorial, I hope you liked it and use it to help many a beneficiary with Excel (not that I doubt you’re not already doing it 😉 ).
Feel free to comment/ask questions. We haven’t planned to produce tutorials on the other 2 Excel mapping techniques soon, but let us know if you think it’d be useful!
Many mapping tools (and other applications like phones and GPS units) are able to create maps directly from columns containing latitude and longitude data. To use these in Excel-based mapping tools sometimes requires you to modify or transform the data. In this article we're going to go through a number of ways that you can quickly manipulate the latitude and longitude data you already have, and show accurate, high performing maps with 10000s of points.
We've enabled comments for this discussion thread. Scroll to the bottom to ask questions and make comments.
Quick(ish) background
Latitude and longitude are used to locate points on the globe as a pair of numbers. Latitude represents how far north/south the point is and longitude represents how far east/east the point is. The numbers are often expressed in degrees because the earth is approximately a sphere. Because we can travel all around the world we use 360 degrees of longitude for a full circle and 180 degrees of latitude for the half circle of north-south. To make life more interesting, we don't use 0-360 longitude and 0-180 degrees latitude though. We use -180 to 180 degrees longitude and -90 to 90 degrees latitude.
(Source: Wikipedia)
0 degrees longitude passes through the 'prime meridian' of Greenwich, in south west London, England. Points west of this are negative longitude, points east are positive. 0 degrees latitude is the equator with positive numbers representing the northern hemisphere and negative the southern hemisphere.
See https://en.wikipedia.org/wiki/Geographic_coordinate_system for more of the technical details!
Numeric format conversions
The most common step necessary to get existing longitude latitude data successfully into maps is to convert the the values into a format that the Excel maps can understand. Here are some of the most common conversions:
Converting from degrees, minutes and seconds to decimal degrees
Excel Mac 2016 Get Longitude Latitude Coordinates For An Address Free
Probably one of the most common formats for latitude and longitude is the degree format of degrees minutes seconds (DMS). For example (38.897680 , -77.03653) becomes (38° 53' 51.648' N 77° 2' 11.508' W). Degrees, minutes and seconds breaks down angles a little bit like the hands of the clock into hours, minutes and seconds (hence the name). To show these in many mapping tools such as Visual Crossing requires us to convert from DMS to decimal degress using the formula:
Microsoft has created a series of macros that provide a function for performing the conversion - see https://support.microsoft.com/en-us/help/213449/how-to-convert-degrees-minutes-seconds-angles-to-or-from-decimal-angle.
Your data contains prefixes rather than negative and numbers (eg 38.897680N , 77.03653W)
Sometimes you will encounter latitude, longitude data such as (38.897680N , 77.03653W). The N indicates 'North of the equator' and the W indicates 'West of the prime meridian'. In these cases we need to remove the letter suffixes and replace them with negative values where appropriate. Negative values are for 'West' and 'South'. Therefore we use (38.897680 , -77.03653) as our final result. An Example Excel formula to clean up the 'W' and 'S' suffix would be:
Your data contains values that aren't in the right range
As we mentioned above, most mapping tools support latitudes from -180 to 180 longitude and -90 to 90 degrees latitude. If your data is between 0 to 360 and 0 to 180 degrees, simply subtract 180 from the longitude and 90 from your latitude! Sometimes you may encounter data that goes beyond the range. For example, some latitude and longitude data may be expressed in radians rather than degrees or other differences related to the projection of the data.
Making sure the values have enough precision
One of the most common reported problems users have is when a map of points resembles this display:
The real map should look like this.
This problem occurs because the values don't have enough precision - typically you want at least five or six digits after the decimal point to produce an accurate business map (some highly accurate use cases may require even greater precision!) The rounding of the numbers causes the values to snap to a grid-like appearance like the first screenshot. The fix for this common problem is to review your data to ensure that you have at least 5 digits after the decimal point.
How can you use longitude & latitude values in Excel? Try Location Analysis in Excel or try retrieving historical and forecast weather data
Final thoughts
Excel Mac 2016 Get Longitude Latitude Coordinates For An Address In India
Once you have your data in the right format with enough precision you will be able to construct high perfoming maps in many applications. If you are looking to investigate location demographics or weather trends, consider using Location Analysis or Weather Data.