Question description:
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.
User
04/04/2018 - 06:42
yes
User
04/04/2018 - 06:43
new
Excelchat Expert
04/04/2018 - 06:43
Hi. Is it okay to see the data you wish to work with for the pivot table?
User
04/04/2018 - 06:44
can I past data on your sheet
Excelchat Expert
04/04/2018 - 06:45
sure. or you can send the file to me, if that seems easier
Excelchat Expert
04/04/2018 - 06:46
hi, are you still there?
User
04/04/2018 - 06:46
sorry not possible
Excelchat Expert
04/04/2018 - 06:47
Okay, i understand. you could paste it on the sheet then
User
04/04/2018 - 06:47
wait
Excelchat Expert
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?
User
04/04/2018 - 06:53
I don want to change the existing settings, just want to create PIVIOT
User
04/04/2018 - 06:54
just see the data I have pasted on the sheet
Excelchat Expert
04/04/2018 - 06:55
okay, got it. let me check the data first, then i'll get back to you
User
04/04/2018 - 06:56
fine
Excelchat Expert
04/04/2018 - 06:56
can you tell me which part of the data you pasted shows merged cell from your original file
User
04/04/2018 - 06:57
B17&18
User
04/04/2018 - 06:58
d17
Excelchat Expert
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
Excelchat Expert
04/04/2018 - 07:01
an alternative approach to this is you really have to make a bit of modification to fit this requirement
User
04/04/2018 - 07:02
!
Excelchat Expert
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
Excelchat Expert
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]
Excelchat Expert
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
Excelchat Expert
04/04/2018 - 07:07
are you still able to respond to my messages?
Excelchat Expert
04/04/2018 - 07:08
My suggested edits are as follows:
Excelchat Expert
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.
