+ Reply to Thread
Results 1 to 9 of 9

Prevent Pivot Table from moving when rows are added

  1. #1
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Prevent Pivot Table from moving when rows are added

    I have a pivot table that I want on the same sheet as the data. However when a user enters a new row on the top of the chart, which is where they need to add the row, the pivot table moves down.

    I will have users entering in a ton of new rows at top, and then the pivot table will be moved all the way down to the bottom--off the screen and won't be seen unless they scroll.

    pivot table.jpg

    So here the pivot table starts at G8. I want it to stay in G8 even when a row is added on the top of the chart, rather than moving down to G9.

    How can I do this?
    Last edited by dsrt16; 03-07-2018 at 01:52 PM.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Prevent Pivot Table from moving when rows are added

    try
    1. create your PT on another sheet
    2. select whole PT & copy
    3. go to your first sheet and paste linked picture
    4. click on the picture, format picture, properties, don't move or size with cells
    hope it will work for you

  3. #3
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Prevent Pivot Table from moving when rows are added

    That wouldn't work because the pivot table hasn't populated data yet. I can't have it be a stagnant object/picture since it doesn't have the data in it yet.
    I need it to be a Pivot Table, not a object or picture.

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: Prevent Pivot Table from moving when rows are added

    linked picture will show data from PT immediately (like camera tool)
    You need to plan how big will be your PT (size) then copy PT or bigger than PT range, and paste linked picture there where you need it

  5. #5
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Prevent Pivot Table from moving when rows are added

    Thanks that does work!

    The only problem is the linked picture image is a bit fuzzy. For comparison, I put the pivot table that is on a different sheet on the top, and the linked picture version on the bottom.
    You can see the linked picture version is a bit fuzzy. I imagine that can't be fixed?

    copy.jpg

    I tested it by adding numbers to the data chart. But when I add words, which is what the user will be adding to the chart, then the fuzziness makes it so some of the words are hard to read in the picture version of the Pivot Table, rendering it almost useless.
    Last edited by dsrt16; 03-06-2018 at 10:43 PM.

  6. #6
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Prevent Pivot Table from moving when rows are added

    If your data is in a Table, adding new rows should not affect the pivot table location.
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  7. #7
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Cool Re: Prevent Pivot Table from moving when rows are added

    I don't know what r u doing there but to me it looks fine, even with zoom 200%. (don't resize this picture! should be 1:1)

    linkedpicture.jpg

    or use Don's solution with Table and right click - Insert - Table Rows Above

    tablrowsabove.jpg
    Attached Files Attached Files
    Last edited by sandy666; 03-07-2018 at 05:46 AM.

  8. #8
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Prevent Pivot Table from moving when rows are added

    Thanks, Don!

    I was just using insert row. And it did move the Pivot Table. I needed to use Insert --table rows above.

    Such a simple solution. Thanks!

  9. #9
    Forum Contributor
    Join Date
    11-15-2008
    Location
    Arizona
    MS-Off Ver
    Excel 2013
    Posts
    179

    Re: Prevent Pivot Table from moving when rows are added

    [QUOTE=sandy666;4858764]I don't know what r u doing there but to me it looks fine, even with zoom 200%. (don't resize this picture! should be 1:1)

    Sandy, the Linked picture Pivot Table is definitely fuzzy--it is hard to read. So I am not sure why it looks fine to you. (The linked picture PT was the bottom one). But no worries because Don's solution works.

+ 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: 6
    Last Post: 02-23-2024, 02:11 PM
  2. Replies: 2
    Last Post: 09-03-2014, 10:51 AM
  3. Automatically moving data rows to other sheets as new entires are added.
    By yehuoy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2011, 05:11 PM
  4. Replies: 2
    Last Post: 11-03-2009, 03:30 PM
  5. How to prevent comments from moving when rows grouped?
    By droppingtodos in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-14-2008, 04:32 PM
  6. How to prevent comments from moving when rows grouped?
    By bristly in forum Excel General
    Replies: 1
    Last Post: 07-30-2008, 10:59 AM
  7. Replies: 5
    Last Post: 11-24-2005, 11:15 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