Advance Excel Graph Goal Line

[erictribble]

So I have a graph that my bosses want the days next to each other in order over a select time period, which I did. 
Week1-Mon, Week2-Mon, Week3-Mon | Week1-Tue, Week2-Tue, Week3-Tue | etc

But they also want a Goal reference line on the graph, and the only way I can get it to work is to add a row of 7 (mon-sun) cells with identical values to create a goal line in the graph. which is impractical, there has to be a better way.

I included the excel file with this post. Please let me know if there is an more efficient way to do this, because this is just for one part, I have to do this for 2 years of data and multiple milestones, so this way would just be impractical, there should be a better way to do it.

TestReport_3282023.xlsx

[#2 · Accepted solution]

@erictribble Not as colourful as yours but easier to maintain I believe. See attached.

[#3]

@Riny_van_Eekelen 
So you in a sense, you added the weekday name and target (goal) to the data table, then pivoted the data table, then graphed the pivot table.

Freaking genius, I overcomplicated the hell out of it lol.

Quick side question though, how can I keep the week day names in order in the graph?

[#4]

@erictribble Because the day names are in fact real dates, Excel will by default sort the in ascending order (Mon through Sun). I assumed you wanted the graph to show Thu through Wed, so I just manually moved the days around in the pivot table and then chart followed. 

If you want the charts to be in weekday order just sort the labels in ascending order.