Is it possible to create Pivot table columns same as Source data ??

**raju.r.083** · 12-03-2020, 10:49 AM

Hey all,
Believe you are doing great and safe at home!!

Could anyone please let me know if is it possible to create Pivot table columns same as Source data ??
Attached is the excel file for the Data to create Pivot table.

In the Source data, Column A / 1st column is "Name"; Column B / 2nd Column is "Used in"; Column C / 3rd column is "Unique ID".

My requirement is, in the Pivot table as well, I need the interface (Columns) in the same way same like source Data.
i.e. In the Pivot Table, Column A / 1st column should be "Name"; Column B / 2nd Column should be "Used in"; Column C / 3rd column should be "Unique ID".

Thanks in advance!!

**6StringJazzer** · 12-03-2020, 11:20 AM

You show your source data but do not show what you want the pivot table to look like. What analysis are you trying to do with the pivot table?

Also, you cannot create a pivot table at all from this source data. You are using merged cells in column A, which is a poor practice, and the pivot table will not recognize that "Vegetables" is the value in row 3 and 4.

**alansidman** · 12-03-2020, 11:33 AM

Not sure how you wanted to show your Pivot Table. Here is one example that I created with Power Query

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J
1	Used in	Vegetables	Fruits	Colour	Numbers	Alphabets	Continents	Names	Gender	Games
2	0				H01888
3	1				V23625
4	2				C56982
5	3				A12365
6	A					C56980
7	Africa						V23652
8	America						C36203
9	Apple		D63259
10	Asia						C56982
11	Australia						V23625
12	B					A12365
13	Black			C56982
14	C					H01888
15	Carrot	B56982
16	Cricket									C56982
17	Europe						C56982
18	Female								S56952
19	Football									C36200
20	Golf									H01888
21	Grapes		F23659
22	Green			V23625
23	Hockey									V21555
24	Male								A12365
25	Mango		C56982
26	Name1							A12365
27	Name2							V23625
28	Name3							H85989
29	Raddish	A12365
30	Red			V21583
31	Tennis									V23625
32	Tomato	B56982
33	Trans								V23602
34	White			C36259
35	Yellow			V23625

Sheet: Sheet2

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

**raju.r.083** · 12-03-2020, 02:03 PM

Hi 6Stringjazzer & Alansidman,
Thanks a lot for your quick response.

Please see attached excel file that I have modified now.

My requirement is that,
I have created 4 section (Vegetables, Fruits, Colour & Number).
I want to know in a tabular format, of what an all are there in each section without duplicating the value.
Example: Consolidated Vegetable section has,
Used in = Radish, Carrot, Tomato & Lemon
Unique ID = A12365, B56982, B56982 & A12365

Similarly, I want to know for consolidated values of each section in a single table for all the sections.
Pivot Table is the only option which I know as of now that eliminates / ignores the duplicate values.
Apart from Pivot Table option, if other options are there, please suggest me.

Thanks a lot for your time and Thank you in advance!!

**JeteMc** · 12-07-2020, 12:59 PM

The only problem that I had in producing a pivot table was that some of the values in column A seem to be mislabeled.
Example some of the values in Numbers were labeled Names (A26:A27) and some of the values in Fruits were labeled Alphabets (A18:A19).
My suggestion is to unmerge A2:A30 as merged cells in raw data is not a good practice. Next make corrections for the mislabeled cells listed above and then produce a pivot table.
Let us know if you have any questions.

**raju.r.083** · 12-08-2020, 11:19 AM

Hi JeteMc,
Thanks a lot for your time and suggestion.
Also, Thank you for your detailed explanation as well.

**JeteMc** · 12-08-2020, 05:17 PM

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Is it possible to create Pivot table columns same as Source data ??

LinkBack

Thread Tools

Rate This Thread

Display

Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Re: Is it possible to create Pivot table columns same as Source data ??

Thread Information

Users Browsing this Thread

Similar Threads

Cannot create a Pivot Table because source data has subtotals

Loss of pivot columns when using pivotcaches.create with new data source

Create Range from highlighted cells for Pivot Table Source Data

[SOLVED] create more than one pivot table from the same data source

Hidden columns in pivot table source data

[SOLVED] Access Create Excel Pivot Table Data Source

Create Pivot Table with 2 different Data source

Bookmarks

Bookmarks

Posting Permissions