BLANK HEX MAP MAKER HOW TO
BLANK HEX MAP MAKER SERIES

BLANK HEX MAP MAKER SERIES

With that data series displayed, I changed the marker type to the hexagon shape found on the вЂњShapeвЂќ sheet вЂ“ making a copy of it and changing the fill color to the color corresponding to the category of the data series and inserting the picture from the clipboard as seen in the dialog box below (this is in Excel 2016, so your options may differ). Once I had all of the X-Y column pairings set up, I entered the X-Y values found in columns G and H for all of the data series columns, one data series at a time, so all of the states would show on the map. I am not absolutely certain these steps need be performed exactly as I delineate вЂ“ I have not set aside time to test other methods вЂ“ but they worked, so I describe them here.

These columns are the meat of what is displayed, but there are a few setup steps that needed to be performed before it all works. The formulae for the X-Y pairings determine if the flag value in column D matches the corresponding data series, and if so, the X-Y pairing values are displayed otherwise, вЂњ#N/AвЂќ is returned to hide those specific data series on the scatterplot (this is actually a feature of using #N/A, not 0 or a missing value). You will probably need to play around with these values in order to get the map to display with spacing between tiles that meets your liking.Д¬olumns I through T contain the X-Y pairings for all of the possible data series, starting with if no data was available (вЂњx-offвЂќ and вЂњy-offвЂќ), then proceeding through the data range from low to high (вЂњx-1вЂќ and вЂњy-1вЂќ through вЂњx-5вЂќ and вЂњy-5вЂќ). The resulting values can be found in columns G and H. In order to handle different versions of Excel and displays properly, a вЂњmultiplierвЂќ is used вЂ“ you can find these in cells E54 and F54 (highlighted in yellow in the image) вЂ“ to obtain the correct spacing between the hexagon state shapes. As Hawaii is closest to the original on a scatterplot, it is the reference state and its X-Y pairing is hard-coded in the yellow cells in columns G and H. One state acts as a reference for all other states вЂ“ meaning, its position is fixed, and all others are relative to its X-Y position. Each state is given a вЂњPosXвЂќ and вЂњPosYвЂќ value to position it appropriately on the map (and you thought those geography lessons would never come in handy!). Confidence intervals on the prevalence statistics are also included, and I discuss how these were approached below.Д®ach stateвЂ™s data value are shown in column C, and are then placed into categories in column D.

These data are (yes, plural вЂ“ I went there) dichotomized into five distinct (color) ranges, exactly mirroring what was performed in this NPR article В a sixth category is includedВ if data was not available for a given state. All of the data series that are either the precursors to what is displayed or are the actual data series for the scatterplot can be found in the columns A through T on the вЂњWorkвЂќ sheet. The guts can all be found in the вЂњWorkвЂќ sheet that the file opens up to, and the chart itself resides in the вЂњGrid MapвЂќ sheet. The data for the map displayed in the file can be found in the worksheet вЂњDataвЂќ (yeah, I go out on a limb in naming my sheets), and the shape used in the tile map can be found in вЂ“ you guessed it вЂ“ the вЂњShapeвЂќ worksheet. The basic premise of the method to develop a hexagon tile map in Excel is a scatterplot with a custom hexagon shape вЂ“ custom in the sense I used the Shape tool within Excel to draw a hexagon of my own liking. IвЂ™ve included the Excel fileВ for your use and am happy to answer follow-up questions or suggestions in the comment boxes below. Now, if at all possible, do yourself a favor and write up the steps you took in developing anything that is even remotely innovative вЂ¦ which I wish I would have done in this instance, since there unfortunately has been in excess of five months since I initially put this demonstration file together. IвЂ™ve taken this a bit further and created a Hexagon Tile Map in Excel.

BLANK HEX MAP MAKER HOW TO

In a couple of posts on this site, Jon wrote a step-by-step guide on how to construct static and interactive Tile Grid Maps in Excel. This is a guest post from David Napoli, a BI & Analytics Director (and recovering rocket scientist) who has worked as lead actuary, statistician, and headed up analytics departments for several healthcare organizations over the last 20 years.

Blank hex map maker