Avatar of pbissegger
pbissegger
Flag for Canada asked on

Excel Pivottable non-calculated fields

I am trying to solve a problem with Excel (2016 Mac). Or see if it an be solved.

Just took a Udemy course on Pivottables, because although I consider myself very strong in Excel (used to do VBA programming in Excel, and VLookup is my favorite formula), I realize I haven't really learned anything new in Excel in the last 15+ years.  

I am in sales and have to do a lot of reporting and found that Pivottables could save me a ton of time - so I thought.

I am struggling with the issue that there can only be 1 column which is NOT calculated in a Pivottable - everything else needs to be calculated. This is an issue because I have 'index numbers' that I need also to display.

Think of a sales opportunity. I need the Pivottable to display the description or title of the deal. But I also need to display the Opportunity Number for people to find it  - it's the unique ID (no, I do not want to display the 'Sum of the Opportunity Number', or the 'Count of Opportunity number' beside the deal title)...

I can imagine there are other people who have to display Jira tickets in a month (with the ticket ID), or database issues during the month (with an issue number, or a timestamp), or logistics shipments to a person (with the package ID/barcode).

Is there no way to also include a second NON CALCULATED field in Pivottables, which is directly related to the primary field ? Or is there a work-around rather than having to concatenate 2 fields in my raw data since Pivottable cannot handle it)?

It's like I found a pot of gold, but the gold is worthless...


* Excel Table* Pivot TablesMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Subodh Tiwari (Neeraj)

8/22/2022 - Mon
Norie

What makes you think you can only have one non-calculated field?

You can add the description and opportunity number, or any other column, as column/row fields, or even filter fields, to the pivot table.
pbissegger

ASKER
Ok, here is my sample file. In the Pivot tab, what I want is a column to the left of my Opportunity name, which has my Opp ID. A one-to-one link (every Opportunity Name has an Opp ID)


Sample file:
211001 sample.xlsx

But if I pull Opp ID into 'columns' of Pivot table, it creates a column for each Opp ID...

and if I pull it into 'Rows' of Pivot table, it creates a nested table (no choice) ....

and if I put it in Values of Pivot table, it creates a 'count of' the Opp IDs.

I just want to display my Opp ID beside my Opportunity name - without calculations, without nesting. Is this something that is possible?

Thanks, Pete


Norie

Why are you putting Opp ID in the Columns section?

Try adding it in the Rows or Filters section.
Your help has saved me hundreds of hours of internet surfing.
fblack61
pbissegger

ASKER
I just explained above why I could not use the Rows and Filters buttons, in the explanation above.

I am looking for a view that provides me this (Opp ID beside the Opportunity Name). Please let me know if there is a way to do it with a Pivot table.

Thanks, Pete
ASKER CERTIFIED SOLUTION
Subodh Tiwari (Neeraj)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Norie

Pete

Try changing the Report Layout to Show in Tabular Form.
pbissegger

ASKER
Neeraj,

Thanks you gave me the understanding I needed. When I looked at your output, your Tabular form was much different that I saw from my side. In iterating it, I realized that you had also changed a few settings to customize a much nicer layout than I was getting. In comparing what you sent me, I realized the following additional changes you madeL
1.  In 'Options', you had turned off the 'Show expand/collapse' buttons
2.  In 'Field Settings', you set the Opp ID 'Subtotals' to 'None'

Both these changes together made the look and feel now acceptable for me to move forward. Thanks so much !
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Subodh Tiwari (Neeraj)

Pete,

You correctly figured out all the changes I made in the Pivot Table. Glad it produced the desired output.
Thanks for the feedback and testimonial.
Robert Berke

Just to be clear, I believe the show expand/collapse buttons were turned off by using Plus/Minus buttons.


Subodh Tiwari (Neeraj)

That's correct rberke!
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes