Wind Rose Chart
In the resent past I have been seeing many charts that looks cool to the eye, and was wondering whether I could create this on Excel. One such chart is the “Wind Rose Chart”, or sometimes known as the “Circular Bar Chat”. This lead to me in researching on what this chart represents, and did find some interesting facts too.
The first use of the wind rose chart appears in the 1855 to 1856, and the chart has been used to depict the causes of deaths of British soldiers during the Crimean War graphically. The chart was invented by the British nurse and a social reformer Ms. Florence Nightingale. (Please refer image 1)
Whilst the first ever such chart was used for reporting the causes related to the soldiers during the war, today the it is more commonly used by meteorologist to graphically report the wind speed and the direction of the wind for a given location. The chart depicts the direction of the wind blew, and then the length each spoke depicts how often the wind blew in the said direction. The colors also depicts the speed the wind blew in. (Refer Image 2).
Finally after going through many articles about how the chart came to exist and few experiments of my own, and then subsequently reading through few blogs as well, I did manage to create the chart myself.
“Wind Rose Chart” in simple is a combination of a radar chart and column chart. To create this in excel all you need to do is create a filled radar chart. In order to depict how I used the chart, I have obtained data for reported road accidents data from 2016 – 2019. Data has been sourced from statistics published in the web page of Ministry of Transport, where road accident data from the National Council for Road Safety could be found. (Refer Table 1)
One thing that has to be kept in mind is that since the chart will be used for comparison, it is advisable to ensure that few elements are plotted, to reduce the complexity of the chart. In the case of the data considered, I would plot the years within the bars with using the type of accident in the outer rim.
Step 1
The first step is would be decide how many data lines are used for each one of the elements that would be considered. In the case of my example this would be 5 elements. Once you have counted the elements the next would be to determine how many data rows will be used for each of the elements, to make it look a perfect circle.
To create a perfect circle you would need at least 40 data lines, with each element having 7 lines with blanks in between to create the gap.
Step 2
Once you have determined the number of data lines, and you could enter the data, as indicated in the Table 3 given below.
As I do need to the data to be depicted for each element similar to a stacked chart, now we do need to create an additional data set prior to creating the chart, with using cumulative each year.
Add the 2016 and 2017 together for 2017 data, and add 2018 data to the total of 2016 and 2017 as 2018 data. Progressively adding the value as you increase one year. For this purpose you could us the =SUM() function with creating a absolute column to increase the range as you copy right wards.
In the case of the example thus, my formula would be =SUM($J3:J3) on Q3 as this would be my first data point for progressive cumulative value.
Now copy it right wards and downwards to each element, and you would have a data set similar to the Table 4 given below.
Step 3
Once you have created the data set with cumulative values, then we could use this data set to start creating the chart.
Since in Excel you cannot insert a radar chart (web chart) without data, fist select a smaller range of data, and insert the chart. Once you have inserted the chart we would do the formatting manually to fit the data we are presenting. I used data range from P2:Q17 as the data set to start with.
Once the data set is selected then go to insert, select the “Filled Radar” chart, from the radar chart options.
Do not worry about the initial output as this we are going to change now.
Now as the fist step, format the chart height and width to be 5 inches. This will ensure that the chart is uniform square, which looks much better.
Once this is done as the first step we need to introduce our first set of data, and for this purpose I would be adding the data for 2019. And continue to add data for 2018, 2017 and then 2016 as the order of data.
To start with select the chart, and then click on the “Select Data” option “Chart Design” tab.
Now remove the data that appear under “Legend Entries (Series)”. Now click on the “Add” button and start adding data one data series at a time, to the dialog box that appear each time you click the “Add” button (Image 4).
As I am going to enter the data 2019 as the fist data set, and my data is stored in the cell range P2:T41, cell address for “Series name” would be T2, and the data range would be T3:T42 for “Series values”.
Whilst data is only up to T41 in rows, when giving the range for value, I have kept one extra line, to ensure that there are gaps between each of the data points.
Now the data reference for 2018, 2017 and 2016 would be as per the Image 5 given below.
Once you have entered all the data ranges, then your chart should look something similar to Image 6.
Now it’s time to introduce the elements I have found as the types of accidents that have taken place each year. To do this, click on the “Edit” button found under “Horizontal (Category) Axis Labels” from the dialog box that appear when clicked on “Select Data” for the selected chart. Select the range in which the labels are given. In my data, this would be P3:P42.
Remember to ensure that the label range is similar to the value range number of lines to ensure that element labels appear correctly.
Once the ranges are given, adjust the value of the “Radar Value” axis, where minimum is placed as “0” and maximum is set by using =MAX() and =CEILING() function together identify maximum value of the range, and round it to a value of your choice. The formula for this in my data was, =CEILING(MAX(Q11:T41),1000), where the function identifies the maximum value in the range and then round it up to the closes 1000 value. This I have divided by 20 to get the Major unit. In the example I have used the Maximum value would be “51,000”, with Major Units would be “10,200”. Now your chart should look as what is depicted in Image 7.
Now add the legends to the chart, and change the fill colors of each range, with a choice of colors you like. The final chart I made with the data is as the Image 8 indicated below.
