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...
ASKER
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
Try adding it in the Rows or Filters section.
ASKER
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
Try changing the Report Layout to Show in Tabular Form.
ASKER
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 !
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.
You can add the description and opportunity number, or any other column, as column/row fields, or even filter fields, to the pivot table.