ExcelAtEverything
Active Member
- Joined
- Jan 30, 2021
- Messages
- 351
- Office Version
- 2019
- Platform
- 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.
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.
