Display A Custom Benchmark Line Across Graph Plot Area

[regc]

Is there anyway of displaying a permanent line across the graph area to mark a benchmark of 80% ?

Can the line remain visible at all times even when all scores are deleted from the cell range for scores

Cheers

Reg


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[regc]

bump


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[LalitPandey87]

Can be done by using VBA or else you need to do it manually every time.

[regc]

Could you please post a code.Thanking you

[regc]

Hi LalitPandey87

Would you please have VBA code for permanent line across the graph area to mark a benchmark of 80%

Thank you

Reg

[Excel Fox]

Here's a way to do it without VBA. The original chart on top shows a color gradient towards the 100% mark. This is done using a picture inserted within the plot area. Note that this is only done by fixing the maximum axis value to 100, and using an appropriate picture.

What you've asked for though is the second chart. Here it is hard coded, to have a line at 80% (well, absolute 80 actually). If you want to increase the number of points, you'll have to increase it manually within the series by selecting the series and going to the formula bar. And of course you can see that that I've used a Line Chart

[regc]

Hello ExcelFox

I just created this chart as you posted your attempt.I will post it anyway as I look at your file.I have a cell which I place what percentage marker line I require and somehow the Lines would adjust....I suspect VBA for this trick

Cheers

Regc

[Excel Fox]

Here's a non-VBA version. Note that I've used named ranges, to make it easier. You can delete the Score range, that is not needed. Again, I've fixed this to 15 data points. So if you need more or less, you have to make the adjustments.



https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[regc]

Again thank you Excel Fox

All works very good.That is exactly what I wanted

This is a final request on this post....is it possible that I can hide columns within the score/graph column range D to R and the Bars and Scores also hide for the selected columns....at present if I hide say 4 columns ( I to L) within the score range the Bars in the Graph do not line up to there score and there are 4 scores at the end with no Bars......can the Graph adjust accordingly when Columns are hiiden

Thanks

[Excel Fox]

For that you'll need to use another range of data.

[regc]

It works well but the Bars and score cells do not line up when hiding columns....I attached a Graph area....is it possible to get bars to appear in each column from the score without using a selected chart from the ribbon....This way the Bar will always stay about the score.....I do not know but is this only possible with code or could CF work....I would want to to use cell B3 to place the marker line as well

What do you think

Thanks

regc

[Excel Fox]

I don't really think there's anything wrong with the alignment if you just set it once. What's wrong with this for instance?

[regc]

Yes the sample is exactly what I wanted..you are correct it looks very good..how did you align this please?

What is your recommendations on locking the sheet except for score cells and percentage cell which can be changed,,,

Regards

Regc

[Excel Fox]

I can't think of a way to protect the sheet and allow hiding and unhiding of columns or rows. The recommendation there would be to use VBA macro, but that's a different topic.