Forum Discussion

erictribble's avatar
erictribble
Copper Contributor
Mar 28, 2023

Advance Excel Graph Goal Line

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

Charting
Excel
    • erictribble's avatar
      erictribble
      Copper Contributor
      Mar 29, 2023

      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?

       

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        Mar 30, 2023

        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.

Share

Resources