•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How to reduce Excel file size when using PP and PQ?
Our take
Managing large Excel files can be a challenge, especially when combining multiple sources like CSV and Excel files. If you're using Power Query (PQ) and Power Pivot (PP) to create a streamlined database, there are effective strategies to reduce file size and enhance performance. By optimizing your queries, limiting data retrieval, and leveraging efficient data connections, you can significantly decrease file size while maintaining functionality.
I am using excel to combine other excel files and to do calculations on them, including average, std, but also more personalised equations (hence PP). Specifically, the purpose is to have "database" because I work with a lot of csv and excle files that I constantly need too extract information from (e.g. 50 files in all of which I only need 2 columns in 4th sheet), pain without PQ.
Currently I am doing "create connection only" when loading queries, and I retrieve them using pivot table with filters, so at no point all of the information is retrieved. I am not sure what else I can do to reduce file size/improve performance. Current file size is about 55Mb, which is a lot.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How do I Maximize File EfficiencyI work with data sets that I typically look at forecast by year. Currently when I look at 2026 and 2027 it is rougly 1.4M lines of data. I have to put these in two separate data pulls and tables. Then I have six different customers included in this data. so I have to create 6 tabs with six diffrent pivot tables for them to look at. This has created a massive file that lags just to open, save or close so I really have two questions and am open to suggestions. Would it be better to store the data in one worksheet and then link a second worksheet that just has the pivot tables and separated look? If so how would I creat that link? Can you explain to me like I am 5 how I would use power query to combine the 26 and 27 table so that they could be in the same pivot table? Every column in both are identical. submitted by /u/dcal69 [link] [comments]
- Power Query - Manual Entry and Multiple UsersI have a file with details of open purchase orders. PO numbers are in the rows and there are several columns with various PO details. It is linked via PQ to two other files. It is used by multiple buyers and I'd like to dumb it down as much as possible. I need to be able to do two things that I haven't figured out yet: Each buyer is responsible for updating delivery ETAs on their open purchases. These are updated at different times for a each vendor throughout the week. In the shared file that we previously used, buyers had a sheet for each vendor and we used VLOOKUPS to pull data from these sheets. Obviously, that doesn't work with a PQ chart. Is there a solution to this that I'm not thinking of? I need a column where buyers can manually add notes to each cell. submitted by /u/free-range-human [link] [comments]
- How do I improve performance for a large Excel spreadsheet file on WPS Office?Running WPS Spreadsheets on an older PC and hitting some frustrating sluggishness on a particularly heavy spreadsheet. Lots of data, a fair number of formulas, and some pivot tables and the whole thing crawls when scrolling or making edits. Picked WPS Office specifically because of its reputation for being lightweight compared to MS Office but the performance on this file suggests lightweight doesn't mean fast when the document itself is demanding enough. In Excel there's a Performance tab in the options that gives you some control over calculation settings, hardware acceleration, and other performance related behavior that can make a meaningful difference on heavy files. I'm trying to figure out if WPS Spreadsheets has equivalent settings worth knowing about or if there are other approaches that help with performance on demanding files specifically. submitted by /u/metamorphoasis [link] [comments]