Pivot - non contiguous dates

Frequent Contributor



I am trying to make a pivot out of the attached. 

The data is dumbed down for ease and my my real data set is much larger and there are many rows with blanks. 

I can not delete the row. 

How to approach?


any assistance is appreciated. 

thank you

9 Replies


I have no problem deleting rows 2 and 3.

Apart from that your data is not suitable for pivot table because it is already in a crosstabular format.


Hi Detlef and thank you for the response. Yes, there is indeed no issue with deleting the rows but as stated this is dumbed down data and in my "real" data set I can not delete any rows.

Let me know if that changes something.


It is not clear to me. Please specify whether you are not able to delete rows (action throws an error or nothing happens) or your are not allowed to delete rows (boss says so),.



To answer your question, I do not have any issues deleting the rows. I can do this HOWEVER I can not delete any rows in my data set since my production data set will not allow that. I do not see how to create a pivot with the data as provided. Let me know if not clear still.


I still do not comprehend. However as I pointed out before the main obstacle is the crosstabular format.



got it. LIttle surprised that excel can not handle a pivot of crosstabular format. darn. OK thank you for the help.
best response confirmed by Tony2021 (Frequent Contributor)


Power Query is an option for such tasks.

this is exactly what I used. Worked well. Must unpivot which is a little odd to me.


Glad it helped. Unpivoting is quite standard and often use operation to normalize tables in data model.