Line Chart Label

Renier

New Member
Joined
Dec 12, 2019
Messages
25
Office Version
  1. 365
Platform
  1. Windows
Good day,

I figured out how get a line chart to only show the values I want using the NA() feature. Now I'd like the last point on the blue line to have a label with its value, tomorrow when I enter a new day's data the line chart will extend by one and i'd like the label to move from the previous point to the new point.

I hope this makes sense...

1678707238663.png
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Good day,

I figured out how get a line chart to only show the values I want using the NA() feature. Now I'd like the last point on the blue line to have a label with its value, tomorrow when I enter a new day's data the line chart will extend by one and i'd like the label to move from the previous point to the new point.

I hope this makes sense...

View attachment 87397
nevermind i figured it out.
 
Upvote 0
Care to share in case it helps someone else?
 
Upvote 0
Care to share in case it helps someone else?

Sure,

I'm sure there must be a better way, but for my limited skill level, this worked.


1678771284446.png


For the table above the chart will look something like this if I put a label on value 6:

1678771379809.png


But if I add another value to H3 the line goes to value 7 but the label stays with value 6.

1678771488555.png


I want the label to move to value 7, the last value in row 3.

To get around this I duplicated row 3 into row 4 with a simple = formula (B4=B3, C4=C3 etc).

1678771657074.png


I then used the following formula to hide the values I didn't want to see in row 4, starting in B4 with =IF(COUNTA($B$3:$M$3)=B1;B3;""). This formula counts the number of cells in B3 to M3 that are not empty. You can use this value with an IF formula to hide the cells that do not correspond the number of cells that are not empty, only showing the cell that is not empty.

1678772416949.png


Now you select the orange line, and select Format Data Labels and select Value From Cells.

1678772548062.png


This box will open. And you need to select you new data label from B4 to M4.

1678772602169.png


1678772700355.png



Click OK and your labels on the orange line will now move to the last value in row 3.

1678772785766.png



I hope this helps!
 
Upvote 0
Solution

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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
Back
Top