Pivot Format

**trosasco23** · 06-20-2022, 12:22 PM

Hello,

I have a huge report that I'm trying to format to look like my screenshot attached. I don't know if I'm able to do this with a pivot table. I'm not able to send my complete report to explain what I need since its full of actual data.

I'll try to explain it for now. When I create my pivots, I need the actual data (that I would normally add to the values box) to go across but it only shows numbers As you can see from my first screenshot.. (light blue headings).

I need it show the actual data like on my 2nd screenshot (purple headings). Is it possible to show the actual data as shown on the purple screenshot?

**trosasco23** · 06-28-2022, 08:57 AM

Hello, Can anyone help me with this one please? I posted it on 6/20. Is there a way to make a pivot look like the purple list above? If you notice the purple one is not a pivot and the info goes into more than one row per group.

**trosasco23** · 08-11-2022, 04:21 PM

Is this format from the purple sheet not possible?

**The_Snook** · 08-11-2022, 04:32 PM

Hi trosasco23,

You'll probably have more success if you upload a workbook with examples of what you're working with and looking to achieve. I can't make out anything from those images.

Regards,

Snook

**trosasco23** · 08-19-2022, 12:07 PM

Ok, I just a couple of sample reports and attached the one with the pivot that I need to reformat and the purple sheet which is how I need the pivot to look., Let me know if you can help. Thank you.

**trosasco23** · 08-22-2022, 08:29 AM

Hi Snook, I submitted some samples. Would you be able to help?

**trosasco23** · 08-23-2022, 09:21 AM

Hello,

I have a huge report that I'm trying to format to look like my screenshot attached. I don't know if I'm able to do this with a pivot table. I'm not able to send my complete report to explain what I need since its full of actual data.

I'll try to explain it for now. When I create my pivots, I need the actual data (that I would normally add to the values box) to go across but it only shows numbers As you can see from my first screenshot.. (light blue headings).

I need it show the actual data like on my 2nd screenshot (purple headings). Is it possible to show the actual data as shown on the purple screenshot?

I attached the files samples above this post. Thank you so much...

**JeteMc** · 08-25-2022, 06:38 PM

I know this isn't exactly what you asked for, however it may give someone inspiration think of how to get there.
1. Added a column to the Input Data sheet using: =CHAR(10)&N2&CHAR(10)&O2&CHAR(10)&P2&CHAR(10)&Q2&CHAR(10)
2. Converted the data on that sheet to an Excel table: tblDataInput
3. Used the following measure in Power Pivot: =CONCATENATEX(tblDataInput,tblDataInput[Title, Name, Phone and Email])
4. Produced the pivot table
I hope that this will be of some help, let us know if you have any questions.

**trosasco23** · 08-29-2022, 02:23 PM

Hello Jete, Part of it works but I need the titles to be a heading on top so for example - if there is a Project exec - all Project Execs would be in the same row going downwards.. like the purple and white sheet. is it possible?

**JeteMc** · 08-29-2022, 07:03 PM

I don't want to say that it's not possible, but I don't know how.
I will ask the other contributors to take a look, however I'll point out that this thread has gotten 370 views, so I imagine most of them have seen it already.

**trosasco23** · 08-29-2022, 07:09 PM

Originally Posted by JeteMc

I don't want to say that it's not possible, but I don't know how.
I will ask the other contributors to take a look, however I'll point out that this thread has gotten 370 views, so I imagine most of them have seen it already.

Wow I guess the challenge is real this time. I?ll wait a little longer and see if someone can come up with something. Thank you though.

**JeteMc** · 08-30-2022, 02:58 PM

This is closer to what you showed us in the purple/white sheet.
1. Added a column to the Input Data sheet using: =CHAR(10)&O2&CHAR(10)&P2&CHAR(10)&Q2&CHAR(10)
2. Converted the data on that sheet to an Excel table: tblDataInput
3. Used the following measure in Power Pivot: =CONCATENATEX(tblDataInput,tblDataInput[Name, Phone and Email])
4. Produced the pivot table
I hope that this will be of some help, let us know if you have any questions.

**trosasco23** · 08-31-2022, 01:41 PM

Wow, you did it!!! This is awesome..

What is a power pivot? Is it created the same as a regular pivot?

Can I just convert my current pivot to a power pivot and leave the other files as is or do I have to recreate the whole file?

and how can I get the phone number to look like a phone number format in that CHAR formula?

I tried to follow some videos on how to do this and created the table as you said.. Opened a new sheet, inserted the external data called "DATA INPUT" and started creating the pivot. I got all the way up to the fx Show text part in the PivotTable Fields. How did you get that to work?

**JeteMc** · 08-31-2022, 11:02 PM

As for the phone number, the formula in column R is modified to read:

Formula:

Please Login or Register  to view this content.

Once the table is set up like shown on the Data Input sheet then you would add the table to the data model from the Power Pivot tab.
I believe Power Pivot is standard in the 2016 version of Excel.
As to the fx Show text measure, once you have opened the file select the Power Pivot tab (or Data tab) then select Manage Data Model and then scroll to the right and find cell with the text "Show text" which should be just below the "Name, Phone and Email" column.
Once the measure is added to the data model you can select the pivot table icon on the power pivot Home tab ribbon.
There are a number of tutorials on use of the data model and pivot tables that are produced using it, such as: https://www.youtube.com/watch?v=Rbkbr89cuHo
Let us know if you have any questions.

**trosasco23** · 09-01-2022, 09:47 AM

Oh wow. I have so many more questions. I will watch the video you sent for sure. I really want to figure this out.

I just have a couple more questions before I get out of your hair...

My real report has the INPUT SCREEN and a bunch of other normal pivots showing data in different reports. One of those pivots is the one I'm trying to do this "power pivot" to. Am I able to just convert that normal one into the power pivot or do I have to create a whole separate workbook just for that pivot?

Also, when I added the table to the data model, it created a new separate file. Do I have to save that file somewhere? And is that where I would need to add new data to or can I still use my original INPUT screen?

I will mark this as resolved after your reply.. As you did achieve what I needed. Thank you so much by the way. I really appreciate all the time you spent on this.

**JeteMc** · 09-01-2022, 10:22 AM

You do not have to create a new workbook.
You can add new data on the Input Data Here sheet and then select Refresh All for the pivot table that we have been working on.
Let us know if you have any questions.

**trosasco23** · 09-01-2022, 11:27 AM

Thanks again. This is awesome.

**JeteMc** · 09-01-2022, 02:35 PM

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Pivot Format

LinkBack

Thread Tools

Rate This Thread

Display

Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Re: Pivot Format

Thread Information

Users Browsing this Thread

Similar Threads

Retain Pivot Table Date Format After Pivot Refresh

Format a pivot table based on values of a pivot column

[SOLVED] Convert Pivot Table Format to Normal Format

Pasting Pivot Chart Format or using slicers Changes Pivot Table data Reference

reorganizing data from pivot table format to a format more suitable for presentations

[SOLVED] Macro to update pivot item (date format) in pivot table to latest date from data source.

setting up default format settings w/pivot tables so graphical format doesn't change?

Bookmarks

Bookmarks

Posting Permissions