Short notes on excel chart

[sharshra]

I have an excel chart where I want to add short notes. See the attached dummy sample. Now I'm adding text boxes where required. It is not dynamic & needs lot of manual work when changes are made. Is there a better way to do this?

 

[QuestionBaker]

Try doing the following.
Write your notes below the headings where you want it to go
So, Notes - mno below 20211007, Notes - xys below 20211013
Note that all the notes should be in same row (assuming your 202110xx are your column headings)

1. Click on the red data in your chart, add data labels to it
2. In Label Options, select Value From Cells, and select the row with notes
3. Deselect Value
4. Go to size and Properties,
5. Under Alignment, set Text direction as -90°

 

[sharshra]

Thanks, QuestionBaker. Not sure if I got it right, had trouble in getting this work.

It is a pivot chart. I'm not clear how to insert the data for notes for specific dates in the source table so that it appears correctly in the pivot table & pivot chart. Probably, some more information may really help. Or, am I missing anything? ?

 

[QuestionBaker]

Unfortunately, I have no experience with pivot tables.
So, I am not sure my method is applicable or not.
Here is what I did
The data

I wish to add notes to B, D and G
Add the notes below data column and enable data lables

double click any of the data label to show its option

When prompted for cell range, select the notes cell

You will get something like this

Change the custom angle to -90 to get the following result

Please try and let me know it works with Pivot tables

 

[sharshra]

Thank you very much for the detailed response, QuestionBaker. It is really helpful

As a workaround, I copied the data from pivot table & followed your steps to create chart with notes. Though it is not an ideal solution, it saved lot of time for me & partially solved my problem. I'm happy to mark it as a 'solution'. It may help other as well.

However, curious to know how it works on pivot table / pivot chart. Does anyone know how to make it work on pivot table / pivot chart?

I observed an interesting thing. Consider the following example. Note that what is shown here is a simplified dummy example table for illustration purpose only. With this, I'm getting the chart with notes as suggested by you.

Date	A	B	C	D	E	F	G	H	Notes
20211010	14	4	4	1		1		1
20211011	15	16	16	555		12	2	4	Notes - abc
20211012	14	18	11	500		18	3
20211013	15	19	11	514		11	3
20211014		24	14	1	3	21	4	1	Notes - xyz
20211015		11	18	466		14	2
20211016	14		2	2					Notes - mno
20211017	14	1	1			1			Notes - pqr
20211018	15	10	15	538		16	4	1

I observe that if there is no value in any cells in the column with highest values (column D in this case), data label notes is not displayed. Not sure why it happens. Does anyone know?

For example, look at the cell highlighted in yellow in the table. Data label (Notes - pqr) is not displayed in the graph even though we expect it to be there. If I just add any value to yellow cell, data label (Notes - pqr) appears ?. See the updated table & chart below ??‍?

Date	A	B	C	D	E	F	G	H	Notes
20211010	14	4	4	1		1		1
20211011	15	16	16	555		12	2	4	Notes - abc
20211012	14	18	11	500		18	3
20211013	15	19	11	514		11	3
20211014		24	14	1	3	21	4	1	Notes - xyz
20211015		11	18	466		14	2
20211016	14		2	2					Notes - mno
20211017	14	1	1	1		1			Notes - pqr
20211018	15	10	15	538		16	4	1

 

[QuestionBaker]

Since there is no, data in the highlighted cell, no data label is applicable.
Alternativley, you can add a dummy Column (try playnig around with its position, before A or after H) you can set its value as max(A:H) of that row.
Set the data label for that data set and just change fill colour as no fill to hide it from your chart.

 

[sharshra]

I don't think that is correct. There is no data in few other cells as well, but data label is displayed. Also, data label is not against any particular column, but is specific to the row.

For example, row 20211017 doesn't have data under E, G & H. Why not having data under D only impacts the data label? Also, data label 'Notes - pqr' should be against 20211017, but not against D.

 

[QuestionBaker]

You have 8 data independent data series, 1st one is called A, 2nd one is called B and so on...
Each series has data points at various dates.
Think it as independent sets of X-Y points. One series is called A, another is called B and so on
So, when you enable data labels, you have to enable it for a particular series.
If a series has a point missing, then the data label can't be displayed.

As per your requirement, you may want the labels for dates and not for a particular data series, but the method I employed shows labels for a particular series.
We can, as humans look at it and say, it applies for the date, and that is what we are doing.

To answer your question, why it applies only series D, because you selected series D and applied data labels to it.
Had you selected any other series, then labels would have been applied to it.

That is why I asked you to add one more column (thus adding a series) and setting it as max(A:H) for that particular row. This will ensure that a data point is always applicable. If you want to make it foolproof, then your formula should read something like
if(isnumber(max(A:H)), max(A:H), 0)
First check if max(A:H) returns a number, if it doesn't then simply insert 0, if it does, then use that number.

Try changing your D values and you will notice that the height from which the notes are displayed will change.

Try changing the series to which data label is set to, you will notice the notes will move slightly to left or slightly to right.

Alternatively, you can just set new series as a constant and not bother with the max formula,

but make sure you set the fill option as none for that data series, as that is not something you want to show it your chart.

 

[sharshra]

Thanks, QuestionBaker. Now the notes are getting displayed. I see the chart now as follows.

Now, notes are appearing as bars. Though fill option is selected as none, it still shows the borders. If I select border as none, it obviously disappears. Some more fine tuning to be done.
1. How can I get rid of 'series for notes' in legend?
2. Instead of bars, I would like to have a line (dotted / dashed line) where notes are displayed. I have put the notes in secondary axis so that only that can be changed. Other bars are on primary axis & can remain as bars. I searched internet to see how bars can be displayed as lines, but couldn't find the right solution. I have changed the width to minimum using 'Gap width' option, but it is still a bar, not a line.

 

[QuestionBaker]

I've found a few ways, but I am not so happy with the solution.
set the Series for notes values as 0 instead of 555.
Manually drag the labels up and then format Leader Line.
You can get a result something like this

Deleting legends is easy.
Just don't write series for notes or second method (but I do not recommend it, but you should still know about)

1. Click on Legend
2. Wait for a second
3. Click on series for notes
4. press del key from your keyboard

The problem with this method is, you disable and enable legends once again, you will have to redo the 4 steps once again.