+ Reply to Thread
Results 1 to 5 of 5

Formatting lost when pasting greater than 35 rows

  1. #1
    Registered User
    Join Date
    04-18-2011
    Location
    Manchester UK
    MS-Off Ver
    Excel 2007
    Posts
    3

    Formatting lost when pasting greater than 35 rows

    I usually want to paste pivots without the underlying data, so I paste from the clipboard as this does a nice job of preserving the formatting. Now I noticed, seemingly randomly, that sometimes the formatting was lost and just the raw text was pasted. I persevered to investigate why some tables pasted ok and others were just text and eventually discovered that any copied data that consists of 36 or more rows looses its formatting when pasted. I can get around this by cutting and pasting the pivots by cutting 35 rows at a time and pasting them separately but thought theres got to be a better solution.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,180

    Re: Formatting lost when pasting greater than 35 rows

    Hi sharpen and welcome to the forum,

    You have a very interesting question/problem. To further investigate it, I'd love an example workbook showing your results that I could duplicate. I used to test software for a job and wonder what version of Excel you are using. Is it on a PC or Mac? How much memory is in your system? Do you have a keyboard application installed enhancing the copy and paste functions? Are you using Ctrl-C and the Ctrl-V shortcut keys or using the right click dropdown menu? What other software might be limiting your number of rows formatting to be copied correctly?

    Perhaps this thread is the answer? https://www.excelforum.com/excel-gen...ormatting.html
    One test is worth a thousand opinions.
    Click the * below to say thanks.

  3. #3
    Registered User
    Join Date
    04-18-2011
    Location
    Manchester UK
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formatting lost when pasting greater than 35 rows

    I appreciate your interest here MarvinP and you are right to ask these questions to be able to have any chance of advising and solving this issue. Your questions got me thinking about this being a memory related issue as when I created a new test file, I discovered a different scenario and the statement I made about the formatting being lost above 36 lines was not repeatable. However the issue is still repeatable and I will explain and also try and answer your questions.
    Environment: Lenovo Thinkpad X1 Carbon running Windows10 64bit 8gb, Excel 365, no keyboard applications or enhancements running, using Ctrl-C to copy and selecting from the clipboard side panel.no other software is running.
    Recreating this issue is repeatable, I too the data attached and made a pivot large enough to play with enough rows and columns (A1:W147). (I tried to attach file Pivot format lost test.xls). I select all the cells by dragging around them and Ctrl-C, then select new sheet and open the clipboard sidebar (using the small corner arrow in the top toolbar).
    My first attempt with this newly created file yielded success - the data was pasted with all the formatting preserved - then I did the process again and the formatting was lost - as if the memory couldn't cope with another frame of copied data.
    So how can I check whats going on with the memory available to the clipboard, because I think this is where the issue lays.
    Attached Files Attached Files

  4. #4
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Seattle, WA, USA
    MS-Off Ver
    MS Office 365 Excel 2016
    Posts
    14,180

    Re: Formatting lost when pasting greater than 35 rows

    Hey Sharpen,

    Thanks for the explanation of hardware and software you are using. I think the answer lies in what you expect to happen based on a wrong assumption. This has happened to me many times and I end up understanding Excel better after I resolve my confusion. I think the answer lies in discovering the different types of Excel Formatting. We know that each individual cell has font and number formats that are tagged into the cell. I know this because the Copy and Paste dropdowns let me paste Formats Only or Values Only, from the range I've copied. I also know that Conditional Formatting goes beyond and gives rules to how the cell should be formatted. NOW - what is Pivot Table Formatting? Is it the same as Cell Formatting? In Pivots we can change the "design" of the table and include totals, subtotals, colors of alternate rows, Compact format, Outline Format, Tabular Format and repeat or not things above. Simply stated, Pivot Table formatting is beyond simple cell formats or even conditional formatting.

    When I copy your Pivot Table in the PIV tab and paste it to a new sheet what formats should go with it? If you were a programmer what decision would you make? NOW - if you close the workbook, I get a warning message "There is a large amount of info on the clipboard...". Should this be saved to paste into Word or another program? What format should come with that data if I say "Yes, save the data".

    Looking on the net I find a page showing differences between Excel versions at:
    https://support.office.com/en-us/art...AA=Office_2007

    Look to the bottom of the linked page above to see limit changes in Pivot Table features. I really wonder if your 2007 version was enhanced in newer versions.

    I hope this helps.
    I wonder if the same thing happens

  5. #5
    Registered User
    Join Date
    04-18-2011
    Location
    Manchester UK
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Formatting lost when pasting greater than 35 rows

    Great explanation MarvinP - I'm sure under the skin Excel is dealing with a lot of information and the formatting aspect is a 'nice to have preserved' but ultimately the cell data is the important thing for it to get right. I won't bore every reader with pursuing this further but for interest I went back to the file, copied the whole pivot on the PIV worksheet, pasted to a new worksheet (from the clipboard sidebar)and saw it successfully paste including the formatting (the nice blue title, the bold and indented text exactly reproducing the pivot table look). Then I immediately repeated it - copied the pivot, paste into new worksheet and found only plain text was pasted. (I attached the result)
    I conclude that there is a memory issue at play. Even clearing the clipboard or even restarting excel wouldn't reset the situation. I wonder if the clipboard memory can be somehow increased.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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