• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Is there a way to format a number when it is placed in a row field in a pivot table?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Is there a way to format a number when it is placed in a row field in a pivot table?|General Excel Questions & Answers|Excel Forum|My Online Training Hub

vba course banner

Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Is there a way to format a number w…
sp_PrintTopic sp_TopicIcon
Is there a way to format a number when it is placed in a row field in a pivot table?
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
1
June 24, 2020 - 6:19 am
sp_Permalink sp_Print

OK, I feel like this is a stupid question, but here goes.  I certainly know how to format a pivot item (containing a number) when that number is placed in the values section of a pivot table.  But, what about when the item is placed in a row or column section?  For example, if I have an item with a value of 100%, in a table, and then create a pivot table from that, the item will be displayed with "1.", its natural value, vs. the 100% that I want displayed.  

A workaround that I found: to get around this, I created a dummy column in my raw table and used the TEXT function to format the percentage item as text, but with a format of %.  That results in my 100% in the table being displayed a 100% in the pivot table.  But, I have a lot of percent items to display in my pivot table and am looking for a more elegant solution than this.

You might ask why do I want to do this?  Why don't I just leave the pivot item in the values section along with the other values I want to display.  The reason is I'm showing allocation rollups and need preserve line of sight into the original data.  Here's a simple example:

Person A works in Group 1 and costs $100 K, Person B works in Group 2 and costs $80 K.  Total salary cost is $180 K.

60% of Group 1 rolls up to Division A; 40% of Group 1 rolls up to Division B

30% of Group 2 rolls up to Division C; 70% of Group 2 rolls up to Division D.

Organization 1 is comprised of Division A and C; Organization 2 is comprised of Division B and D.

So, how much are the salary costs for the 2 organizations.  A full line of sight to the raw costs shows:

Org    Div    Group %     Group    Person    Person Cost         Group Cost

1        A        60%            1           A            $100 K                 $60 K

2        B        40%            1           A            $100 K                 $40 K

1        C        30%            2           B            $80 K                  $24 K

2        D        70%            2           B            $80 K                  $56 K

==============================================

Total                                                                                    $180 K

with Org 1 costing $84 and Org 2 costing $96 K

This is just a silly, simple example, but you can see that the values for the Group % field need to be in the row section and not the value section.  That's how I came up with a need to have numbers being formatted as percentages and not fractions.

 

Thanks in advance for anyone with a solution to the above.  

Scotty81

sp_AnswersTopicSeeAnswer See Answer
Avatar
Velouria
London or thereabouts
Moderator
Members


Trusted Members

Moderators
Level 4
Forum Posts: 648
Member Since:
November 1, 2018
sp_UserOfflineSmall Offline
2
June 24, 2020 - 6:32 pm
sp_Permalink sp_Print

If it's a regular pivot table (non data model) and all the data in that field is numeric, then there should be a number format button at the bottom of the Field Settings dialog (right-click the field, choose Field Settings). If it's a data model pivot, then I suspect you will have to explicitly format the cells that the field is in.

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4515
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
3
June 25, 2020 - 9:31 am
sp_Permalink sp_Print

Hi Scotty81,

There are no formatting options for row and column fields as these are text by default. Your workaround by formatting the source data is the best solution.

Mynda

sp_AnswersTopicAnswer
Answers Post
Avatar
Scotty81
Member
Members
Level 0
Forum Posts: 33
Member Since:
April 25, 2017
sp_UserOfflineSmall Offline
4
June 25, 2020 - 11:46 pm
sp_Permalink sp_Print

Thanks Mynda and Velouria,

I did see that the pivot table formats things a bit differently, depending on whether it's a regular pivot table or from a data model.  At least I know not to keep searching for another work around.

Regards,

Scotty81

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: John Kobiela, Lia Ro, Malcolm Sawyer
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 873
Purfleet: 414
Frans Visser: 346
David_Ng: 306
lea cohen: 222
Jessica Stewart: 216
A.Maurizio: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Melanie Ford
Isaac Felbah
Adele Glover
Hitesh Asrani
Rohan Abraham
Anthony van Riessen
Erlinda Eloriaga
Abisola Ogundele
MARTYN STERRY
Rahim Lakhani
Forum Stats:
Groups: 3
Forums: 24
Topics: 6356
Posts: 27793

 

Member Stats:
Guest Posters: 49
Members: 32324
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.