Need better holding option for Excel Source Data

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows
Hello Mr Excel community,

I've built up a suite of Excel sales reports via:

Data Source --> Power Query --> Data Model --> Power Pivot table

I have so far been housing all the data sources (which are all just simple (sometimes sizable) Excel reports) in folders on my desktop. Now that I understand tools like Power Query & Pivot Tables so much better than I did (due in part to the amazing folks on this site), soon I am going to rebuild all reports from the ground up to make needed improvements /adjustments. In the meantime my problem is that periodically... using my reports causes issues in particular when I try to add a new query or pivot table. freeze up & red-line the CPU at 100% (preventing me from doing anything else) for extended periods of time if I try to add new things to the report. Sometimes I'll just be typing into a cell (for instance), and my screen freezes with the blue spinning wheel. Most of the time it's fine, but I seem to be periodically maxing out on something. I just don't know what. The 25MB report workbook itself has maybe 20 pivot tables fully expanded, 12 queries loaded correctly to the data model (except 4 or 5 that I saw which were incorrectly loaded to "Pivot Table and Data Model" which I didn't mean to do).

Some of this will correct when I make the queries faster when I rebuild, but I was already aware of that and pretty careful when I built my queries & formulas originally, so therefore I don't think that will play a large enough role as the main culprit here. I may have done a few things wrong that still need corrected, but I was pretty careful about that part of it.
Thanks for advice on what to look at or how to address!

My PC laptop currently has 6GB RAM installed (5.82GB usable) and uses only about 80 GB storage leaving 825 GB free.
The data source files themselves total roughly 400 MB, and will only grow larger each day. And again, the main spreadsheet itself is about 25MB.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
672
Hello ExcelAtEverything - Just a thought: Have you tried turning off Automatic Calculations so that the worksheet only recalculates manually? I thought that may stop some of the unnecessary lockups. Hope this helps get you started. (see below)

1625756164486.png
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
317
Office Version
  1. 2019
Platform
  1. Windows
Hello ExcelAtEverything - Just a thought: Have you tried turning off Automatic Calculations so that the worksheet only recalculates manually? I thought that may stop some of the unnecessary lockups. Hope this helps get you started. (see below)

View attachment 42453
I hadn't thought of that, no. I'll look into that though. I want to make sure I understand if turning that off is just isolated to "refresh"-related actions, or is there anything else it's doing.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,073
Office Version
  1. 365
Platform
  1. Windows
I have heard this may slow down Power Query but should help with the CPU maxing out.
Change Background Data to Never Allow Previews .....

1626083551131.png
 

Forum statistics

Threads
1,136,300
Messages
5,674,961
Members
419,536
Latest member
Mohammed Jaffer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top