Need to create a special Chart

It's been a while since I have seen something like this, but I recall being somewhat successful at building something like this as a filled radar chart:
https://www.excelforum.com/excel-cha...ttachment.html
https://www.excelforum.com/excel-cha...wind-rose.html

Hey MrShorty,

Unfurtunately I was not able to proceed. Any other suggestions?

Your sample file only shows data and no attempt to create the table that the radar chart will need and no attempt at the chart. My suggestion is still the same -- use a filled radar chart.

I don't know how much you learned from the other examples, but, as with a lot of charting questions, most of the effort in creating a chart like this is building the data table in the spreadsheet. In order to get the each "slice", I will need at least 2 entries for each data point. In order to get the "slices" to be adjacent to each other, I will need at least 2 data series.

1) Insert a row in between each data point. Select row 49 -> Insert row. Select row 48 -> insert row. Continue until you have a blank row in between each data point.
2) I need to move every other entry into column E. Select what is now D7 -> cut/paste/move into E7. Repeat for every other data point.
3) I will need three entries per point, overlapping with the previous entry in order for the filled radar chart to "draw each slice". In D6, I enter =D5. Then copy and paste that in the cell beneath each entry whether in column D or column E. In E6, I enter =E7 and copy/paste into every cell above an entry. This should give you a table that looks like this:

Please Login or Register  to view this content.

4) Select the table and insert a filled radar chart.
5) Evaluate the result.

Hello MrShorty,

I was able to proceed this time thanks to your suggestions. I believe some fine tunings still needed.

1. Diagramm 2 (data 2): It seems that the programm is not able to connect first and last measurement properly. We can see that in the diagramm.
2. I need to add the item name for each location and to plot the results according to the items.
-For graph 1 to have 3 main items (structure, culture, vision)
-For graph 2 to have 2 main items (dep) with the related under items (system, process, method ect.). If this is complex, I would be fine with only the under items.
3. How can I improve the grid lines? It would be fine if I can see the grid line for each measurement until 100% ? (like the mentioned graph in the top)
4. How can I enlarge a little bit the center of the radar chart?

Many thanks again.

1. Diagramm 2 (data 2): It seems that the programm is not able to connect first and last measurement properly. We can see that in the diagramm.

That's because the first category's data and the last category's data ended up in the same column (D). You would need to do something so that first and last end up in different columns. Easiest may be to:
1) Select the chart. Expand the chart source data range to include column F.
2) Select the first or last category's data (D5:D6 or D92:D94) and move into column F.
3) Add a 0.7 entry in either F94 (if you moved D5:D6) or into D94 (if you moved D92:D94).
or any other strategy for making sure that the first and last category do not end up in the same column/data series.

I need to add the item name for each location and to plot the results according to the items.
-For graph 1 to have 3 main items (structure, culture, vision)
-For graph 2 to have 2 main items (dep) with the related under items (system, process, method ect.). If this is complex, I would be fine with only the under items.

Neither chart is formatted to display the horizontal category axis and labels. Excel does not allow us to access the horizontal category axis, so we will need to change the chart type to something with a category axis, change the category axis, then change the chart type back to a radar chart:
1) Select the chart -> Change Chart type -> Clustered column (or any other chart type with a category axis).
2) Find the list of available chart elements and add the horizontal category axis.
3) Edit the horizontal category axis labels (Select Data dialog) so that it includes the necessary columns (data1 looks like you want columns B and C, data2 looks like you want columns A to C).
4) Make sure the horizontal category axis is formatted to exist and show labels.
5) Your question 3 appears to be asking how to add "vertical gridlines". Again, this is easier to do as a column (or similar) chart, so add the major vertical gridlines now.
6) When all necessary edits are completed, select chart -> change chart type -> Filled Radar.

4. How can I enlarge a little bit the center of the radar chart?

Add another data series that will become the center "circle".
1) In the rightmost column, add a column of 0 values
2) Expand the chart's source data to include this new column.
3) Format the vertical axis to have a suitable minimum (-0.1 seems appropriate).
4) Format this new series to be the last plotted (if it isn't already) and give it a suitable filled color format.

Any other questions?

1) I don't think there is a "plot gridlines in front of data series" option in Excel charts. You could try formatting the data series to be somewhat transparent and making the gridlines darker so that they are visible through the data series, but I don't think Excel will let you chart the gridlines on top of the data series.

Instead of gridlines, you could add additional columns/series that will draw the gridlines, change their chart type to regular lined radar instead of filled radar, and then make sure those series are plotted on top of the other series. Give them an appropriate line color, too.

2) Is this referring to the shaded background? To get this effect, again, add additional data series (2 or 3 as needed) and alternate the value from 0 to 1 -- just like we did with the main data. Change the plot order for these data series to be the first 2 or 3 (so they will be behind the rest of the data) and maybe format them to be mostly transparent.

3) I am not sure what happened to your vertical gridlines. I could not edit your existing chart to get the vertical gridlines/spokes to appear. However, if I selected the table and inserted a new filled radar chart (with all of the additional series to accomplish 1 and 2 as desired), the vertical gridlines were automatically displayed. I'm not sure what happened, but you may need to scrap this chart and start over.

When I change your existing chart into a column chart, I notice that your vertical gridlines are formatted in a nearly invisible color. You might try changing your chart type back to a column chart, format the gridlines with a darker color, then change the chart back to a filled radar chart -- just to see if your version of Excel will apply that formatting to the vertical gridlines even if my version will not.

4) Apply a custom number format of 0%; to the axis. https://support.microsoft.com/en-us/...rs=en-us&ad=us

Your latest attachment failed to attach.

Please find the example

It still failed to attach. Be sure you are following the instructions in the yellow banner at the top of the page.

1) The white "empty" slice is because there is a missing value in one of the data series (it looks like one of the background data series) for the first or last categories. Since you uploaded only a picture, I cannot see the source data to see which empty cell needs a value, but it should be as simple as entering 100 into the appropriate cell.

2) I do not understand why you cannot use a number format like 0;. Perhaps your newer version of Excel requires an extra section 0;;0 or something. Check out the "guidelines for custom number format codes" page I linked to earlier to explore how to use number formatting to hide negative values.

3) As near as I can tell, Excel does not offer any position options for category labels in a radar chart. If you don't like where Excel has positioned the category labels, you will need to delete the built in category labels and use an invisible "dummy" series to position the labels where you want them and then use the data labels for that series to display the category labels.

In a radar chart -- like other charts with a category axis -- you "delete" one or more categories from the chart by removing those rows from the source data. In this case, each slice is composed of three "categories" (as the chart engine sees it), so you would need to figure out how many of the last three rows to delete from the chart's source data and still get the correct interaction between the first and last categories.

Edit: In the case of your sample file (it didn't show up when I first saw the post), I changed the data range for each data series to be through row 61 (instead of 62) and put 26 into M2. That seemed to removed the empty slice.

Did you see my edit?

Originally Posted by MrShorty

Did you see my edit?

Yes, Thank you. I changed according to your advice. It is improved. But still I have a half of the empty slice existing.

Edit: When I put the filled background into 0 it seems that the empty slice is fully existing. But the connection between last series and first one is improved and given.

If I start with the file in post #19, change the series to extend to row 61 instead of 62, then enter 26 into M2, I do not replicate your latest picture. The grey background does not have the empty white slice in it. I am not sure what you are doing to get the empty slice.

Originally Posted by MrShorty

If I start with the file in post #19, change the series to extend to row 61 instead of 62, then enter 26 into M2, I do not replicate your latest picture. The grey background does not have the empty white slice in it. I am not sure what you are doing to get the empty slice.

I tested it again. It was working only if I switch from filled radar to any other diagram type then return again to filled radar.
The issue is if I would like to add as last step the vaules as points (radar with data point as diagramm type) the issue of the empty slice returns again.

I may have to look at it tomorrow, but I don't see any reason why changing the chart type of one data series should change whether or not the slice(s) connection the first/last categories should disappear.

Looking closely at the definitions for each data series (Select Data dialog), I notice that your "category axis" data range is B3:B61, but all of your data series ranges are row 2 to row 62. Each of your data series has two more rows than your category data range. We need to make it so that each data series has the same number of rows as the category axis data range. So I select each data series -> Edit series -> change so the Y values range for each is row 2 to row 60. Once each data series has the same number of rows as the category axis range, then the chart should be correct. Then enter the number 26 into M2 to complete last data slice.

Because I like to have my data series value ranges and my category axis data range refer to the same rows, I would then select D2:AE63 and cut/paste/move the data down one row. That way, the data for the chart is all in row 3 to 61 rather than having some of the data in row 3 to 61 and other parts of the data in row 2 to 60.

Originally Posted by malloc123

Perfect. Let me test that.

I still have the same issue also when I followed your suggestion.

All things are fine. Once I would add the values as "radar with data points" I still have the one slice more.

It is difficult to debug from pictures, but that still suggests to me that when you add the last data series, it has more "categories" (rows) than the other data series. My guess is that you just need to figure out how many rows to include for that data series and change the data range accordingly.

It seems related to the change in chart type for the last "regular radar chart" data series, but I haven't had time, yet, to figure out why.

I don't know if it is a but in Excel's chart engine or what is going on, but Excel seems to erroneously add a category/slice whenever you create a radar+filled radar combination chart. My only suggestion is to minimize the size of the extraneous slice by using more entries per colored wedge. Currently, we are using 3 entries per wedge, try expanding to 7 or 11 or more. I expect that Excel will continue to add the extra slice, but perhaps you can minimize the size of that extra slice.