July 15, 2020
I have a simple line graph and have highlighted the period which contains the highest value. I used the formula, "=IF(MAX($B$5:$B$24)=B5,B5,NA())" to identify the maximum point, and the formula, "=ISNUMBER(C5)" to identify the period to shade.
How can I rewrite the 1st formula to identify the 3 highest numbers in column B, so that the shade helper column will then include all three periods?
Thank you.
Active Member
May 24, 2019
Hi Mynda and Tom,
This would be your workaround ( I am attaching the file also)
Basically, you have to add two columns, one for positioning the label and another one with blanks (double quotes) to just show that. So in the label it doesn´t show anything. Once the labels are put, at first you will see a 0 (cero) go to the label options and switch it off (value) but first, activate the value from cells option and then select the dummy column with blanks. Then, you resize the label area (or label box) with height: 0.66 and 0.81 width and lastly, apply the same color of the bars.
Hope it works for you.
The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:
Mynda TreacyActive Member
May 24, 2019
Hi Tom,
My workaround above is a bit "static", case in the future you need it to be more dynamic, scatter plots is the way to go.
with this type of chart, there are many possibilities for "hacking" it´s options. Below it´s something I recently developed where the requirement was a bit similar to yours. I will attach the file case you want to review it.
Regards,
At Mynda, I got this message: "015_highlighting_axis_item.xlsx: Error 106 - Upload file size exceeds maximum allowed size"
Would it be ok to share an external link for downloading the file ?
Active Member
May 24, 2019
Hi Mynda and everyone,
Sorry for the delay, here it´s the link to download the workbook: https://1drv.ms/x/s!Am3AC3QcIM.....A?e=SfUFZs
related to the Gif image above. Note: this workbook was done using Excel´s new functions: # dynamic arrays #. It can be done using Excel tables and cell ranges as well.
Take care everyone.
The following users say thank you to Microsoft Excel Recalc Or Die for this useful post:
Mynda TreacyJuly 15, 2020
Another follow-up: When I have the columns highlighted, and combine it with a line graph, it works great. But if I want to have one of the lines to be shown as a column chart, any columns in the shaded area are behind the shading so they are not visible and I cannot get them to display on top of the shading. Mynda, do you have a solution for that?
Thanks,
Tom
1 Guest(s)