Need help creating a chart

Harlow

New Member
Joined
Sep 10, 2017
Messages
14
Hey everyone. I’m looking for help in creating a unique chart in excel. We are currently using PowerPoint to creat this chart and I want to simplify with excel.

The best example I can find it the link below on Google. It is called a Project Status Spectrum chart. The chart is great for what I need, but I want to include multiple points along the chart, not just 1 on each line.

https://goo.gl/images/C6Zaqb

Chart making is not my strength and I need any help I can get. I’d like upwards of 7 markers on each line. Is this possible??

Thanks!
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

thisoldman

Well-known Member
Joined
Jan 5, 2014
Messages
1,074
The original example is from exceldashboardtemplates.com, in a blog post by SteveEqualsTrue: http://www.exceldashboardtemplates.com/how-to-make-an-excel-project-status-spectrum-chart/

I came up with this, with up to four markers per category:

8sTzgON.png


The howto with images is way too long for me to post here. The file may be downloaded from Dropbox: https://www.dropbox.com/s/z6vh89zta81bl4f/sliders_on_a_bar.xlsx?dl=0

I setup the data like this. The top left cell, with the word "Setup", is C25:

EkimSop.png


The charted data was setup like this, with the words "Charted data" in I25:

TgdShrW.png


The Slider 1 left formula:
=(D30 <> "") * (D30 - 0.5 * SliderWidth)

This formula is equivalent to:
=IF(D30<>"", D30-0.5*SliderWidth, 0)

Slider 2 left formula:
=(E30 <> "") * (E30 - SUM(K28:L28) - 0.5 * SliderWidth)

Slider 3 and Slider 4 left formulas are similar to the Slider 2 left formula.

The Slider N formulas merely copy the Slider width from cell D26.


This is the howto, without images:

  1. Determine full-scale maximum for the bars and enter that value in Charted Data: Gradient, J25:J30. I used a zero to eight scale, just as in the original.
  2. Enter a value for the slider width. You may change this value at any time. Good starting values seem to be in the 0.15 to 0.25 range. Higher values give thicker sliders.
  3. Enter the center positions for the sliders in D27:G32. If you don't want a particular slider, leave that cell blank. The Charted data cells will fill by formula. The formulas calculate where the left and right edges of each thumb should appear.
  4. Select I24:R30 and insert a stacked bar chart. With the chart selected, go to Chart Tools >> Design and click on Switch Row / Column.
  5. From the Plus-icon next to the chart, click on the right pointing arrow next to "Axes". Place a checkmark in all four checkboxes. All these axes will probably need to be reformatted.
  6. The primary horizontal axis is at the top. Change the maximum bounds of both axes to equal the value entered in Charted data: Gradient, cells J25:J30. Format both vertical axes to "Categories in reverse order".
  7. Delete the right axis and one of the horizontal axes. I chose to delete the top axis. The legend was annoying me—I removed it in this step. You may find it useful to keep the legend for now, postponing its removal till later.
  8. Format all "Slider Left" series to no fill, no line. These are the orange, yellow, green, and brown series in the images above. Format all the "Slider right series to the color or colors of your choice. I chose a dark gray.
  9. For the Gradient series, we want a gradient fill. In the formatting pane, set the fill to "Gradient", and the "Type" to "Radial". We use three gradient stops, with the center stop positioned at 50%.
  10. The stops are in reverse order from the display of our chart. The left stop controls the right-hand color, the right stop controls the left color.
  11. Adjust the gap widths. I set the gap for the Gradient series to 125%, the sliders gap to 70%.
  12. Add your finishing touches. I found that deleting the remaining, the bottom, horizontal axis messed up the whole chart's scale. Perhaps because it's a secondary axis? I ended up giving it a custom number format to hide it.

I hope I've been clear with the instructions, with images, included in the Excel file. I'll gladly answer any questions.
 

Harlow

New Member
Joined
Sep 10, 2017
Messages
14
Fantastic. I will play with the file this week. Thank you for the help!
 

brunogbm

New Member
Joined
Sep 3, 2020
Messages
3
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Hi thisoldman, any chance you could share the file again?
The dropbox link is no longer working.
Thank you in advance!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top