Jul 14 2022 09:49 AM
I need some help modifying the plot line on a line chart. I am in Excel 2016 as a part of an Office 365 subscription.
I have a spreadsheet I use to record daily body weight measurements. Column A is the date, plotted on the horizontal axis of a line chart. Column B is the daily body weight in pounds, plotted on the vertical axis of the line chart.
The plot line on the chart is currently depicted in RED. I want to continue with the plot line, but have the color of the line change to BLUE as of a certain date, say 1 July, which represents the beginning of a weight loss program.
I have tried to edit the Series, cutting it off as of 30 June, then creating a new Series that begins 1July and continues to the end of the year, but test data entered for dates after 1 July do not plot on the chart. Perhaps I don't understand how to edit/create a Data Series.
My objective is to have a continuous line plot that simply refects a color change as of a certain date. Seems rather simple and straightforward, but I have thus far only come away confused and unsuccessful.
Can anyone help with this charting issue?
Thanks.
Jul 14 2022 11:11 AM
@Seeker336 I believe you need to have separate series for "before" and "after" with an over lap for the last and first date. An example is attached.
Jul 14 2022 11:42 AM - edited Jul 14 2022 11:49 AM
SolutionSo a) yes it is possible to selectively color segments of a line different colors. You can use VBA or manually you can click on the line (entire series is selected) then click on a it again and the nearest data point is selected. Right click and select 'Format data point'. Then click the paint bucket and under the 'Line' section change the color. You can then repeat for every data point/line segment you want to change. You can also use CTRL-arrow key to move point to point on the line.
That said, that is a lot of work and @Rine_van_Eekelen method is much easier. 1 tip to make it easier is that you can keep you existing columns and just copy the data UP TO 06-30 into a new column. Then add that new column as a new line on the graph and it can sit 'on top' of the existing line. In this way you can maintain the full column of data you have.
here is the previous example updated this way:
So the second column "Orig data" just needs to be copied once since it won't change.
I also show how I could change an individual segment inside the blue line to green.
Jul 14 2022 12:14 PM - edited Jul 14 2022 12:18 PM
I think I would use two series built within Name Manager from the original series
Sheet1!Before
= IF(date<=keyDate,weight,NA())
Sheet1!After
= IF(date>=keyDate,weight,NA())
=SERIES("Before",Sheet1!$D$5:$Q$5,Sheet1!Before,1)
=SERIES("After",Sheet1!$D$5:$Q$5,Sheet1!After,1)
Jul 14 2022 12:40 PM
@Riny_van_Eekelen Thansk for your response. Seems so simple, but I tried to create an additional column to record data after 1 July, entered string of test values, and cannot get any of them to post.
When I try to go to Select Data and create a new Series, the dialog box gives me an error saying I appear to be creating a formula that Excel cannot execute. I guess I don't understand enough about how to create a series -- apparently missing something in the syntax, although I have been trying to duplicate the existing series and simply replace references to the new series of cells.
Totally confused.
Jul 14 2022 01:35 PM
Jul 14 2022 11:42 AM - edited Jul 14 2022 11:49 AM
SolutionSo a) yes it is possible to selectively color segments of a line different colors. You can use VBA or manually you can click on the line (entire series is selected) then click on a it again and the nearest data point is selected. Right click and select 'Format data point'. Then click the paint bucket and under the 'Line' section change the color. You can then repeat for every data point/line segment you want to change. You can also use CTRL-arrow key to move point to point on the line.
That said, that is a lot of work and @Rine_van_Eekelen method is much easier. 1 tip to make it easier is that you can keep you existing columns and just copy the data UP TO 06-30 into a new column. Then add that new column as a new line on the graph and it can sit 'on top' of the existing line. In this way you can maintain the full column of data you have.
here is the previous example updated this way:
So the second column "Orig data" just needs to be copied once since it won't change.
I also show how I could change an individual segment inside the blue line to green.