Pivot to show table content in "Values" as-is & not perfrom any Calculation

**hemantkrishnan** · 05-30-2013, 04:15 PM

I have a 20,000 lines of data in roughly this format:
Data.png

For the past couple of days, I have been trying to get them to be displayed via Pivot (or any other tool) in the below format:
Required-Format.png

I think the bigger question is, can Pivot disallow the "Values" section in the Layout from doing any calculation & show the content in the data as-is?

If this is not at all possible, then is there any other solution for my requirement.

I really would not want to get back to the data source & have them restructure the output since it takes so much coding for them to generate the data & that they are reluctant to make any changes to aid me in presentation of this data.

I am not an XL program guru. So I may not be able to write vb scripts or macros. But I can run them if someone can help me with it.

**scott.s.fower** · 05-30-2013, 04:56 PM

required-format.xlsx

sure, to do it you need to remove all subtotals and grand totals, then put Location in the Column Labels section, then Cutsomer and Head in the Row Labels section and Sum of employees in the Values section (see attached)

**hemantkrishnan** · 05-30-2013, 06:08 PM

Thanks a lot Scott for responding. I downloaded ur XL & it is exactly what I need. And that is what I have been trying to achieve. The only problem is that it is not working for my dataset. I am attaching a sample data in XL format. May be you can take a look:sample-data.xlsx

**scott.s.fower** · 05-30-2013, 08:27 PM

something like this? your original example only had four columns, so it will look a bit different because now you have 5
sample-data.xlsx

**hemantkrishnan** · 05-30-2013, 08:45 PM

Scott .. thanks a lot .. this was my exact requirement. I rearranged the Location column as per my original requirement. I was still unable to do it on my dataset (however if you could let me know, then I can) .. but in the interim, I just used my dataset on the excel pivot that you created, moved to another page & changed the data source to include my entire 20k lines of data & after a refresh, I got exactly what I wanted.

BTW, I don't know if clicking on the star next to your post makes any addition to your reputation or not but I really want to thank you for helping me out.
Edit: I have added the reputation successfully!!

If you can please let me know how you did, I can do that myself.