•1 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
How do I Maximize File Efficiency
Our take
Maximizing file efficiency is essential when working with large data sets, especially those spanning multiple years and customers. If you're dealing with 1.4 million lines of data for 2026 and 2027, consolidating your approach can significantly improve performance. Consider storing all data in a single worksheet and linking a separate worksheet for your pivot tables. This strategy not only streamlines your file but also enhances accessibility.
I 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.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- Consolidate data from different worksheets with final information coming from a combination of the worksheetsHoping for some help on a problem I can't wrap my head around. I need to consolidate some information from 12 different tabs (one data pull per month) into one worksheet with some of the data needing to be overridden and some needing to stay. With the most recent pull of the data not necessarily being the information I want showing, I'm not sure how to proceed. I'm trying to find a way to create this and pass it along to someone else to just load data and it automatically puts out the result I'm looking for. For instance, I pull information from my system in January for the entire year (broken into monthly buckets) and all of that is perfect. My second data pull of the year in February isn't so clean. I need to see the numbers from January on any of the January data (because it zeros out in February in my system) but everything related to February through December needs to be from the most recent data pull. Third pull of the year in March, I need to see the numbers from January and February for the January and February data and then need the new info for March through December. Basically, by the time December rolls around, I need data from all 12 sheets. Hopefully this makes sense. Any thoughts on how I can accomplish this in Excel? submitted by /u/megd726 [link] [comments]
- Power query for a large datasetMy company uses a horrible format for its daily production sheets, but the data can be pulled through power query. I want to build a reporting tool for looking at any major trends that are currently missed. Ideally looking at part efficiency by machine type and some other descriptive data too like efficiency by shift manger etc. My problem is that even after cutting unnecessary columns and filtering unnecessary rows, it takes forever to load anything. ChatGPT isn’t all that helpful, I’d like some expert advice please! For info, rough number of rows of data is about 50,000 per year. I want to cover at least the last three years. Sheets are all saved into a folder by month, within a folder by year. submitted by /u/CanJesusSwimOnLand [link] [comments]
- I need a way to combine multiple workbooks to use in a pivot table that can be refreshed through sharepoint.I have 5 spreadsheets in a folder on sharepoint and one on a separate folder in the same team site. These track contacts made by clients. Each workbook is for a LGA (location) and have the same structure. they have a sheet for each month and a table for that month. I initially struggled to use the Sharepoint Folder to combine these so created queries in Power Query. for each workbook I created a query which combined the 12 tables and then created a query which appended them. I was able to put this in a workbook and a pivot table to summarise where we were up to. it worked really well. the permissions were set using organisational account. this lasted about a day and now I cannot refresh it. I can refresh in the power query editor but not in the spreadsheet. I have opened it in the desktop version and also the same problem. I am now getting errors about legacy connections. I then tried the Sharepoint folder which finally worked but seem I can combine the January tables for the five spreadsheets but then I would need to do the same for each month. I would love any suggestions for this to consistently work and refresh from our sharepoint site (excel online). submitted by /u/Eastern_Echidna5680 [link] [comments]
- Advise on Excel-One file vs multiple Files to use.I’m stuck with a small doubt and wanted to check what others usually do. I have dashboard picking data from 3 types of data (A, B, C), each of records, and I’m not sure how to store it — whether to keep everything in one single file or split it into 3 separate files (one per category). What I’m trying to understand is which option works better when data is this large in terms of storage space, speed (especially when I only need one category), and overall system load. I checked with two different tools and got opposite answers — one said keep everything in one file, another said split it — so now I’m not sure what makes more sense in real usage. In my case, I mostly work on one category at a time and usually from a local or shared drive. What would you suggest — one file or multiple files, and why? Also, any recommendation on format like Excel, CSV, or something else would really help. submitted by /u/Sun_n_Star [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#big data management in spreadsheets#conversational data analysis#real-time data collaboration#intelligent data visualization#data visualization tools#enterprise data management#big data performance#data analysis tools#data cleaning solutions#financial modeling with spreadsheets#rows.com#natural language processing for spreadsheets#Excel compatibility#Excel alternatives