Dashboard Objects are Randomly Resizing on a Data Refresh!

[xHenry95]

Hey everyone,

I don't use Excel a lot, so I am quite proud with what I have come up with. Wish I could share it but it has all my financial data and crypto holdings to create a dashboard. Power queries, just trying to solve a different issue at the moment with VBA, the lot!

Besides the point... On the main dashboard sheet (out of 20 or so sheets) I have probably about 30-40 objects displaying Pivot Charts, a Pivot table, shapes, text boxes, slicers. You name it, it's probably on the dashboard! haha

I want to set this dashboard to auto update (which I can do)... BUT - Every time I refresh my data (refresh all, auto refresh, refresh on open, refresh with macro), the objects on the bottom (say 70%) of the sheet are affected; they are resized ever-so-slightly on each refresh, maybe about 1 or 2 pixels higher than they were before the refresh, pushing everything down. This causes my chart on the bottom edge to move out of the view gradually (trying to make it so I don't have to scroll down or sideways on my dashboard).

Every single object on the sheet is set to not move or resize with cells. I have tried locking everything but only just found out this only takes affect when the sheet is protected, and when that is done refreshing data is not possible.

I have tried to research this problem but I can't see anyone else that has the same issue. I have a feeling it may have something to do with the Pivot Table placed onto the sheet (also set to not resize cells on update), as all the data that is affected is underneath this placement (row 15 and beyond).

I hope I have explained well enough for you to understand as I cannot upload screenshots and a sample file will take just as long to create (Days!)

I have recorded an uploaded a Gif below of the far left of my spreadsheet, while I run three refreshes, hopefully this works and displays the issue I am having. The donut chart is highlighted on each refresh, and you can see how some of the objects are being reformatted below the pivot table(A3:A14) after each refresh):

Thank you so much for any advice in advance!

Hoping someone can save my project

Have a great day.
Henry

P.s I am using Excel with O365 (cant tell which year but I think it's the most recent) - build details below:
Microsoft® Excel® for Microsoft 365 MSO (Version 2112 Build 16.0.14729.20254) 64-bit
Operating system - Windows 10

 

[xHenry95]

Sorry - I forgot to actually ask a question!!

Does anyone have any experience of this happening, or know anything I can try to stop my objects from being 'stretched' and moving down the page?

Thanks again

 

[JEC]

There are settings for objects. Search for a checkbox which says not to rescale.

 

[xHenry95]

There are settings for objects. Search for a checkbox which says not to rescale.

I love a quick reply, thank you very much.

However, I have searched for these settings, and every object on my dashboard already has these two options ticked:
1. Format Shape > Size > Lock Aspect Ratio - and:
2. Format Shape > Properties > Don't move or size with cells

Screenshot shows my settings for one object on my sheet (as states is the same for the lock and moving):

 

[JEC]

That should actually work

 

[xHenry95]

That should actually work

That's what I thought! So frustrating

 

[JEC]

Do you have pivottables in that sheet too?
They have to be changed in the pivot table settings, uncheck: automatically rescale columns after refresh or something like that.

 

[xHenry95]

Do you have pivottables in that sheet too?
They have to be changed in the pivot table settings, uncheck: automatically rescale columns after refresh or something like that.

Yes that’s also ticked. Away from my laptop right now but that’s another one I have double and triple. Checked

 

[xHenry95]

I

Yes that’s also ticked. Away from my laptop right now but that’s another one I have double and triple. Checked

only have 1 pivot table

 

[JEC]

Weird.. I can't think of anything else