Need help with PIVOT

Occasional Contributor


I want to make a single measured value line diagram, but the problem is that with the PIVOT the measured values add up and if I display the measured values every second, the diagram is very confusing. Is there a way to hide the seconds, minutes, etc., but the diagram does not change.

Sorry for the English Use Google Translate
thanks in advance
5 Replies

@Felix285 You can use a index column for it if you can provide a excel file for an example then I can explain you far better


If you could help me with that it would be really nice of you. I have now done an example in the appendix.
To understand again, I want all the data in the diagram to appear, but I want to keep the diagram clean and at most see the day (display all values individually)
Thanks again many times, I have already wasted several hours on this!
best response confirmed by Felix285 (Occasional Contributor)


Linear chart doesn't work with hours and minutes, day is the minimum unit. What is on X-axis of your chart that's text which can't be transformed, it is as it is.


In such cases Scatter chart is used, but PivotTable doesn't work with such charts. The workaround is to generate data by other way to use Scatter chart.


In your sample I added column with averages and sorted source range in ascending order. However, better solution will be to Power Query the source, aggregate within it and return ready to chart table.


Please check in attached file.



Please check this image I think this was what you needed. Also if you want the file do mail me I will give you the solution file on mail



File is attached to previous posts