These days I have been obsessed with researching data maps. I challenged myself and made a data map using Excel. The graphic below is the dynamic map of Hurricane Irma that I have drawn with Excel.

If you are interested, I will be happy to share with you the process of making a hurricane map with Excel. It is to use the bubble chart to outline the dynamic path of the hurricane and to show the change in wind strength. Here are the specific steps for making a hurricane map.

1. Prepare Materials

① Find a map of the US Atlantic that includes lines of latitude and longitude.

Note that the map we need must have latitude and longitude lines. Many maps provided on the public network are inaccurate. I downloaded this map from National Hurricane Center and Central Pacific Hurricane Center.

② Download the storm track statistics of Hurricane Irma from Weather Underground, including date, time, latitude, longitude, wind, and so on.

2. Process Data

① Remove the units of data such as latitude, longitude, wind speed, wind pressure, etc. And the date and time should be converted into a format that is easy for Excel to process.

② We can see that the hurricane statistics are recorded every six or three hours in the data material. Here we keep the data recorded every six hours.

3. Draw a Bubble Chart

① Insert the above map into the table, and then draw a bubble chart on it. The X axis of the bubble chart represents the longitude data, the Y axis represents the latitude data, and the bubble size depends on the value of the wind pressure.

② Design the format of Chart Area. The maximum and minimum values of the coordinate axes in the bubble chart are set according to the latitude and longitude readings. And make the spacing on the coordinates coincide with the spacing of the latitudes and longitudes on the map. Here I set the spacing to 5, which ensures that the data points drawn by the bubble chart match the actual latitude and longitude on the map.

③ Drag and drop the border of the plot area to make it coincide with the coordinate axes in the bottom Atlantic map. Then hide the axis data and set the border to “No line”.

4. Make Dynamic Effects

To achieve the dynamic effects of the hurricane trajectory, we can use Slider Control to control the time. Each time we slide, the time is increased by 6 hours. The cell linked by the slider passes the data to the table area on the left side of the chart. And then it obtains the corresponding latitude and longitude data and wind data by querying the time point. At the same time, two sets of data sources of the chart are generated from this time data. The entire control chain is thus formed and the final control of the chart by the slider is completed. 

Further than that, if I want to implement automatic control of the slider, I will need VBA code to make the chart automatically show dynamic effects. 

Operation Process:

① Write two macros with VBA. One macro controls the start, the other controls the stop.

② Draw 4 controls, representing start, stop, loop, and slider. Specify the above macros.

③ The format of the slider control specifies the left cell, which records the sliding data. And the upper cell “Data & Time” calls the data of this cell.

④ Prepare dynamic data.

In order to achieve the above dynamic effects, two sets of data are actually prepared in the bubble chart. One set shows all the path points that the hurricane has traveled before the current time point, and the other set marks the location of the hurricane at the current time point.

Here we need to use the LOOKUP function and call the data of the cell “Data & Time”.

For the first set of data, we select all the data less than or equal to the current time point and set the other data points to #N/A.

For the second set, we select the data that matches the current time, and the other data is also set to #N/A. Here, #N/A doesnt display data points in the chart.

⑤ Bind the bubble chart to the data source.

In the final step, we bind the bubble chart to the data source and the hurricane trajectory map is complete. 

The textbox in the chart can get the information of the data points directly from the cells by linking with them. In addition to the bubble chart, a set of column charts is added to the map to show the wind, and its data binding operation form is the same as that of the bubble chart.

Well, the general idea of making a hurricane map with Excel is like this. Below is an official map of Hurricane Irma. Is it very similar to the one I made with Excel?

Tips

Excel is very powerful, but if you want to use it to make some complicated charts, you must have a code base and learn VBA language, which is time consuming. I shared an article 4 Uses of Data Maps in Business Analysis, in which all maps are made with the zero-code visualization tool FineReport. The operation is very simple. If you don’t understand the code, you can try this tool to create data visualization charts.

Follow FineReport Reporting Software on Facebook to learn more about data visualization!

You might also be interested in…

 Top 16 Types of Chart in Data Visualization

 How Can Beginners Design Cool Data Visualizations?

A Beginner’s Guide to Business Dashboards