+ Reply to Thread
Results 1 to 4 of 4

Maintaining number format when adding an existing field back into a pivot table

  1. #1
    Registered User
    Join Date
    05-01-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    16

    Maintaining number format when adding an existing field back into a pivot table

    Hopefully I can explain this in a way that makes sense... if not I will upload a sample.

    I have a pivot table that includes countries as rows & fields for multiple years as values. I would like to be able to select a specific year (or years) by toggling them on & off in the Pivot Table Fields side bar. I would also like to maintain the number format for the values associated with those years.

    The problem I'm having is that when I deselect a year & add it back in, the number formatting is lost (in this case, it's simply a number with a single decimal place). I've made sure to check the "Preserve cell formatting on update" box in Pivot Table Options, but the issue with this is that I'm not updating the table per se. I am only deselecting a year (or years), and then selecting it/them again to add them back into the table. I am not refreshing the table.

    I could, of course, reformat the values each time, but that is tedious and inefficient so I'm hoping there is a simple solution.

    Please let me know if a sample sheet would be beneficial & I will upload one.

    Thanks in advance!

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,168

    Re: Maintaining number format when adding an existing field back into a pivot table

    Hi Capt,

    Yep, a sample workbook with a Pivot on it and show us what you want is always a plus. That way we are all on the same page with your problem.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    05-01-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    16

    Re: Maintaining number format when adding an existing field back into a pivot table

    Thanks Marvin.

    Here's a sample. I haven't included a desired results example as it wouldn't be be possible, afaik.

    Hope it helps.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-01-2023
    Location
    Canada
    MS-Off Ver
    365
    Posts
    16

    Re: Maintaining number format when adding an existing field back into a pivot table

    Hmmm... looking at my sample, the issue does not seem to be replicating itself. I do still encounter it in the file I am working on so I will have to investigate further.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 01-15-2019, 08:54 PM
  2. [SOLVED] maintaining Number format when adding text
    By TWallachy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-02-2018, 01:04 PM
  3. [SOLVED] Pivot Table Creation maintaining source data format
    By egotrich in forum Excel General
    Replies: 1
    Last Post: 03-26-2018, 09:53 AM
  4. Replies: 1
    Last Post: 01-18-2013, 03:06 PM
  5. Replies: 3
    Last Post: 03-03-2012, 12:16 PM
  6. Adding another worksheet to an existing pivot table
    By kmlloyd in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 12-04-2009, 11:28 AM
  7. Pivot table maintaining cell format
    By shaunburke in forum Excel General
    Replies: 1
    Last Post: 11-27-2007, 03:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1