Share via

Excel Pivot Table: When Showing Values As, Keep $ and % adjacent

Excel Class 0 Reputation points
2026-05-12T19:34:14.3333333+00:00

In a pivottable, i show the sum of the total, and i add the total field again. Then I show values as pct diff from, and i want the dollar amount to keep across columns, and the percent diff to also stay adjacent across multiple columns. What setting should i use?

Microsoft 365 and Office | Excel | For business | Other
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Hendrix-C 16,725 Reputation points Microsoft External Staff Moderator
    2026-05-12T22:03:24.2+00:00

    Hi @Excel Class,

    Please notify me if I have misunderstood anything about your request. If you want to show $ and % adjacent in the pivot table, you can try this setup:

    • First, I created a sample data table like this

    User's image

    • Then go to Insert > Pivot Table and I build a pivot table like this with two Total fields in Values

    User's image

    • Click the first Sum of Total > Value Fields Settings > Rename it to different name (e.g. Total Amount) and set the Number Format to Currency > OK to close the popup
    • Click the second Sum of Total2 > Value Fields Settings > rename it to a different name (e.g. %Change) and set the Number Format to Percentage
    • Then go to Show Value As > choose %Difference From in the drop down and set Base field = Month and Base item = (previous) then OK to close the popup. You will have the output pivot table like this:

    User's image

    Please try these steps and let me know if it works for your concern. If not, please notify me and we can work together to figure out a solution.

    If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.  

    Thank you for your patience and your understanding. Looking forward to hearing from you.

    If the answer is helpful, please click "Accept Answer" and kindly upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread. 

    Was this answer helpful?

  2. AI answer

    2026-05-12T19:34:24.2666667+00:00

    Use separate value fields and arrange them manually; there is no special setting that auto-groups $ and % columns.

    1. In the PivotTable Field List, drag the same data field into the Values area twice (or once for each base column you need). This creates parallel value fields (for example, Sum of Total and Sum of Total2).
    2. For each “dollar” field:
      • Right-click the field in the PivotTable → Value Field Settings (or Field Settings).
      • On Summarize Values By, keep it as Sum (or your desired aggregation).
      • Set Number Format to a currency format.
    3. For each adjacent “percent diff” field:
      • Right-click the duplicate field → Value Field Settings.
      • Go to the Show Values As (or Show data as) tab.
      • Choose the desired option (for example, % Difference From or another % calculation).
      • Set Number Format to Percentage.
    4. In the Values area of the Field List, drag the fields into the order you want so that each currency field is immediately followed by its corresponding % field. Repeat for each column group.

    This approach (duplicating the value field and using Summarize Values By for the dollar column and Show Values As for the adjacent % column) is the supported way to keep $ and % columns side by side across multiple PivotTable columns.

    References:

    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.