Excel - General Question on Pivot Table - Expert Solution

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
User 04/04/2018 - 06:43
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
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
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
User 04/04/2018 - 06:58
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc