Our clients who use CDXZipStream or CDXStreamer might find useful several free apps offered in the Microsoft Windows app store that can be used with location-based data. Bing Maps for Excel and Geographic Heat Map can create great worksheet maps for data presentation, and others such as Bubbles, People Graph, and Modern Trends use creative approaches to present data that are not available in the charting options offered within Excel. For cleaning up ZIP code or address data prior to use with CDXZipStream and CDXStreamer, the app called Remove Unwanted Characters is also a good tool. So let’s take a closer look …
(Note: These apps work with Excel 2013 and/or online Excel through Office 365. You can search for Office apps by clicking on the Store icon in the Apps area of the Insert tab, located on the Excel ribbon. The link to each app is also provided below. And here’s a tip: If you need to remove any of the images created by these apps, click on the arrow at the upper right-hand corner of the app image, pick Select, and press the Delete key on your keyboard.)
Plot and visualize your location-based data using Bing Maps. If you have a group of locations in an Excel worksheet, just use your cursor to highlight the locations along with any associated data, such as demographics obtained through CDXZipStream. Locations can be based on address, county, state, city, latitude\longitude, or ZIP code. This little app inserts a map right into the worksheet with pushpins or other symbols of your choosing, showing both the locations and related data. The number of locations is limited to 100 per map. Here’s an example of a data visualization map showing median income data from CDXZipStream by ZIP code:
Microsoft also offers Power Map for Excel, which provide 3D mapping of up to a million data points. It is available from the Insert tab as part of Office 365, although it can also be downloaded as an unsupported add-in for Office 2013 Professional Plus. Please see the Microsoft website for more information. It is not available through the Windows app store.
Geographic Heat Map
The resulting display from this tool is a professional-looking heat map that can display data on the state level. Here’s an example showing median earnings data from CDXZipStream from all states:
Bing Maps for Excel can do a version of this as well, although the maps produced by Geographic Heat Map are a bit more eye-catching. There are not a lot of options in this app beyond selection of different color schemes, but it is very easy to use and it does exactly as advertised.
There are other creative graphing and charting apps that can be applied to geographic data, although not in map form. These provide nice alternatives to the more conventional charting options provided within Excel:
The Bubbles app for Office represents your data as bubbles of different sizes and colors, showing data distributions at a glance. You can even analyze more complex data sets from two separate tables. Bubble colors can be set with any hex values you specify.
Another creative way of showing data using people symbols, especially appropriate for demographics or population data available from CDXZipStream.
With just a few selections in the Modern Trend app, you can create an interactive trend chart. You can also pin specific data points on the chart, and add descriptions on the canvas. Multiple themes and layouts are available.
Remove Unwanted Characters
As the name implies, the Remove Unwanted Characters app automatically removes any unwanted characters in your Excel data, even all non-printing characters and line breaks that sometimes occur when data is imported or copied from the web or other applications. Since these often “invisible” characters can invalidate ZIP codes or address data, this is a great tool to use prior to running CDXZipStream or CDXStreamer. Formulas in the data range will be overwritten with values when using this app.