Advance Excel Graph Goal Line

New Contributor

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.


3 Replies
best response confirmed by erictribble (New Contributor)

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

Screenshot 2023-03-29 at 07.11.25.png

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?



@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.