This user has given permission to use the problem statement for this blog.
How can I create PIVOT Table for a large data having merged cell and free spaces, please
Solved by Z. L. in 29 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
04/04/2018 - 06:43
Hi. Is it okay to see the data you wish to work with for the pivot table?
04/04/2018 - 06:44
can I past data on your sheet
04/04/2018 - 06:45
sure. or you can send the file to me, if that seems easier
04/04/2018 - 06:46
04/04/2018 - 06:47
Okay, i understand. you could paste it on the sheet then
04/04/2018 - 06:49
okay, while you are at it. I'd like to know if you are willing to modify the existing table to unmerge the merged cells?
04/04/2018 - 06:53
I don want to change the existing settings, just want to create PIVIOT
04/04/2018 - 06:54
just see the data I have pasted on the sheet
04/04/2018 - 06:55
okay, got it. let me check the data first, then i'll get back to you
04/04/2018 - 06:56
can you tell me which part of the data you pasted shows merged cell from your original file
04/04/2018 - 07:00
Okay, the minimum requirement for a PIVOT TABLE is that your reference table must be organized in such a way that each column has identified header title. and merging cells will prevent you from doing that
04/04/2018 - 07:01
an alternative approach to this is you really have to make a bit of modification to fit this requirement
04/04/2018 - 07:02
Center Across Selection is a far better alternative to merging.
To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.
You will get the desired look you want but without the merged cell's problems
04/04/2018 - 07:05
Here I attached the file from which I tried to create a PIVOT table given the data at the existing format
[Uploaded an Excel file]
04/04/2018 - 07:06
you can see that when I transformed the data into a TABLE (a very good step before proceeding with PIVOT TABLE generation) it automatically elected header title for each columns
04/04/2018 - 07:07
are you still able to respond to my messages?
04/04/2018 - 07:08
My suggested edits are as follows:
04/04/2018 - 07:12
please see attached file
[Uploaded an Excel file]
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user information.