•3 min read•from Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community
Workbook from Microsoft Form encountering very long load times from excessive complex formulas
Our take
Managing complex data in food production can be a daunting task, especially when legacy spreadsheets slow you down. If you're experiencing frustrating load times due to excessive formulas in your Excel workbook, you're not alone. Fortunately, there's a path forward. By leveraging AI-native spreadsheet technology, you can streamline your data handling without sacrificing functionality. In this exploration, we'll uncover innovative strategies to optimize your data flow, ensuring your reporting is not only efficient but also effortlessly integrated with your existing systems.
Good evening,
I work in a food production plant in Shipping and Receiving.
We have had Microsoft Forms for entering in daily cases produced, cases shipped, and a separate form for doing time studies on trucks that come in, how long to load or unload said truck, and when they leave.
I have had a manual workbook to fill in all of this data basically again (this information gets entered into these daily reports we fill out in our Microsoft forms) but to organize it into an easy daily report to give us truck In to Out averages, loading time averages, cases produced vs what was scheduled to produce, etc.. A big issue I have had with this manual data entry workbook, which are done month by month, is the amount of formulas which I have in it..(multiplying cases by item number to give us weight and how many skids, calculating our scheduled amount to produce against what's actually produced, giving percentages, many conditional formatted cells to easily show if we are in the green or red, etc.)
Now my boss has always wanted a workbook to do what my manual workbook does but to grab the data from the Excel workbook that these Microsoft forms load the data into. The problem before was we had two separate Microsoft forms for daily cases produced/shipped and the one for our time studies.
But I went ahead and made one form which would do both.
I was able to copy over many sheets and formulas from my manual workbook into the Excel spreadsheet that loads in the data from this Microsoft Form. My boss really wants it to work indefinitely..
The problem I am encountering which I was afraid of, is the amount of formulas in this one workbook is way too much for a computer to handle. Changing 1 thing results in it needing to calculate a thread for like 20-30 minutes (like with the manual excel spreadsheet, the manual processor has been set to 1).
Am I just going about this all wrong? Is there a better way to grab the data from this form that isn't going to overload a computer? Do I make separate workbooks pulling from this form's Excel workbook and just keep the daily report with the initial Microsoft Form workbook (but then would those workbooks update automatically as well?)
I imagine there is a way to achieve what my boss is wanting, but my experience with Excel is only so advanced. I'm aware there are other programs or other tools of excel, and that is why I came onto this subreddit for advice. Please help me 🙇🏻♂️
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- How to deal with a bulky spreadsheet that is starting to hit the limits of Excel?Hello all, I have been venturing on quite the Excel journey the past year or so. I made a corporate spreadsheet that is approaching 500k formulas and that is starting to get serious speed issues at this point. It is 2026, so I conversed with ChatGPT several times regarding the speed issue, but realized I am way better off asking the experts here anyways. What is the problem So, my spreadsheet imports flat databases with specific information regarding objects that need further analysing. The imported flat databases run from say A tot CC or something, from which I probably draw about 12-15 datafields that are used for further analysis. It 'may' be more in the future. Afterwards, said data gets 'enriched' (manually) by things that aren't in the database, also because said data needs a human eye that cannot be automated. So far, so good. Right now, each object gets analysed from several different angles. As it stands, my spreadsheet runs from A until NA or something on the Formula Page. Many columns receive data from preceding columns, that are in the turn the result of many (slightly complex) logical IF or IFS tests, many of which are nested 3 or 4 deep. Often, they work in conjunction with X.LOOKUP to retrieve values, as the columns on the formula page are not equal. For example: A until BC on the Formula Page may analyze 150 objects, BD until DD may analyse 100 objects (from the same dataset, so narrower), and so forths. Thus a lot of X.LOOKUP is required, also because the first 'block' comes up with values that need to be found with X.LOOKUP. Also, values need to be retrieved from the flat database 'import' page with X.LOOKUP. Finally, X.LOOKUP is an insurance compared to FILTER, as I am not fully convinced that empty values in the flat database always contain a space (" "). To get to the point I use many IF, IFS, AND, and if need be, OR, formulas. Thinks: tens of thousands, probably in excess of 100k. These are compounded with X.LOOKUP, or X.LOOKUP gets used copiously without those. Here too, think tens of thousands. These formulas are - as much as possible - in array format, even though I find it controversial to do that as I consider how it can create a chain of updates throughout the spreadsheet. 'Dependencies' is the name of the game, with one object receiving many possible alterations / adjustments due to manual input data, for which the spreadsheet needs to provide. Right now, when I update a value, it may take up to 4 seconds to update the spreadsheet, which is already beyond the annoyance point for me. This leads me to these (hopefully) simple questions: Is it smart to use array formulas, knowing that each thing I change should only impact that one object line (for example, row 488) and none other? It is important to mention that object 1 does not influence object 488, or any other. Any manual data field only effects the object in the row it is in. In my mind, array formulas do not make sense in that regard, as it can result in a cascade of updates, but apparantly array formulas are 'way more efficient'. Is use of a VBA library the way to go to reduce lag and create more of an instant spreadsheet again? I am not able to code in VBA yet, but I am in the slow process of learning it regardless. Alternatively: should I use LET whenever a repeated lookup is needed in the same formula? Really looking for to your answers! submitted by /u/EvolvedRevolution [link] [comments]
- using MS forms with excelHello! I have a massive spreadsheet that I need to use for lots of reporting. I've tried to pare it down as much as possible for those who have to do the actual data entry, but they are still very overwhelmed by it (they really don't understand how to even use filters to narrow down the scope) and so the data coming out is not reliable. I've never used MS forms in conjunction with excel, but I'm thinking that if I create a form for them just to enter the basics, I would at least have correct data for my analysis piece. The only thing I'm concerned about is that there are updates to entries that need to be made (ie- someone earns an additional level of certification or they drop out of the program). Can this be accomplished with forms? Or am I barking up the wrong tree. I was hoping to get some advice before I start really teaching myself how to use MS forms (any suggestions for good tutorials would be great too). Thank you in advance! submitted by /u/bluellama314 [link] [comments]
- My Workbook Has Too Much Going On And Keeps Freezingtl;dr I have my workbook doing too much across several sheets and it freezes for too long, anything I can do to fix it? I am a Customer Services manager for a local pest control company, so I am in charge of the team that makes the schedules. In order to check for scheduling errors, I have found a way to generate reports from our CRM and use Excel to find errors. In the past, I simply generated these reports, sorted through the data myself and gotten the results I wanted. It was great proof of concept, but I wanted to automate it for my convenience and so others who don't know the process can use it when I am unavailable. I was able to make a workbook that would take a list of services due in a month and a list of services scheduled for a day, sort, combine, filter, etc. to produce a list of services scheduled that need another service with it, or where a reservice (extra service requested by a client) is scheduled when a maintenance (services automatically due periodically) is due, so the reservices should just be changed to the maintenance. The problem is that whenever I add/remove the data from those reports, Excel freezes for like 5 minutes while it tried to do all that work. It's better than nothing, but I'd like to find a way for it to not do that, at least not for more than a minute or so. Here is are some details of my Workbook: From our CRM, I generate a report of all services due this month and a report of all services scheduled for the next day. I delete irrelevant columns until I am left with these, in this order: For services due: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Subscription Status (will always be "Active") Subscription type (text) Service Due (current due date for the service) For services scheduled: Customer ID (number unique to each account) Last Name (customer name) First Name (customer name) Serviced By (technician assigned to appointment) Service type (text) Scheduled for (date appointment is for) I take each report and put them each into their own sheet in my work book ("SubsDue" and "Scheduled"). Both sheets will have the same number of columns and are in this order to match up intentionally. There can be over 15000 total services due in a month, so I have the sheet work with A2:F200000 to ensure it all fits. Row 1 is always just column headers that do not change. There's a helper column on 2-3 sheets as well, uses COUNTIF to check for duplicate Customer ID's, which is important. I then have it sorting through the data for these sheets by using VSTACK and FILTER to add the data to a new sheet, which will then be used again by even more sheets. Ultimately, I will have 2 sheets at the end that will only have the erroneous services I'm looking for. The sheet does this just like I expect it to, it just freezes up for 5 minutes or so while doing it. Here are a couple examples of the formulas my sheets are using: =FILTER(VSTACK(Scheduled!$A$2:$F$200000),NOT(VSTACK(Scheduled!$E$2:$E$200000)="Reservice")) (Takes the data from another sheet and shows only the Reservices included in it) =FILTER(VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:F200000),VSTACK(SubsDue!A2:A200000,ReservicesScheduled!A2:A200000)>0) (Combines the data from 2 sheets and excludes the empty rows form each of them) I've been using Excel at a basic level for years and have learned a little more about it recently, but I don't know what to do to help with this. I can only imagine that having a chain of multiple sheets simultaneously using these formulas on up to 400000 rows at a time is just too much for Excel to handle without freezing up. Any recommendations for how I can improve this? I'd appreciate any help and would love to learn from this. Excel version is 16.0.19725.20152 submitted by /u/Royal-Advantage2706 [link] [comments]
- NEED ADVICE re serious issues on collaborative company workbook. Appealing to Reddit's Excel wizards as a last resort.I work as part of a small team sharing an Excel workbook with my associates. The document is integral to company operations and requires continuous updating on multiple fronts. It is very large (7MB) and extremely complex, with about a dozen sheets using complex, interrelated formulas. There are up to four people on the document at any one time, with two of them making constant edits and using heavy filters throughout the day and the other two primarily just filtering for key items to inform business decisions. All four usually operate from different locations, sometimes across the country. We recently expanded the operations team from one to two people, since which time the document has been experiencing significant issues—none of which occurred prior to the new associate joining. These issues appear to stem from the new situation of having two people editing simultaneously for the first time, and include: Merging problems Regular crashes Filtering issues, such as when we are both filtered within the document and one person uses control+D to fill down in a filtered column, other rows that the one person’s filters do NOT show in that moment have been filled in as well. The workbook is stored on OneDrive but accessed and edited through the desktop Excel application with AutoSave enabled. Surely we are not the first business to face this issue. Are there any solutions we may be overlooking? How have other businesses approached such matters? It is imperative that the workbook remain updated in real-time for all involved for business purposes. Finally, please let me know if any other subreddits or communities (perhaps discord?) come to mind where I may find answers to the above. submitted by /u/LingeringOdor [link] [comments]
Tagged with
#Excel alternatives for data analysis#generative AI for data analysis#real-time data collaboration#natural language processing for spreadsheets#big data management in spreadsheets#Excel compatibility#conversational data analysis#intelligent data visualization#financial modeling with spreadsheets#collaborative spreadsheet tools#modern spreadsheet innovations#machine learning in spreadsheet applications#enterprise-level spreadsheet solutions#digital transformation in spreadsheet software#AI-driven spreadsheet solutions#cloud-based spreadsheet applications#automation in spreadsheet workflows#no-code spreadsheet solutions#rows.com#self-service analytics tools