Huge workbook, lots of tabs & macros--should I use something other than Excel?
Our take
In the fast-paced world of purchasing and logistics, many professionals find themselves grappling with the limitations of traditional tools like Excel. The case presented by the user who has developed a complex workbook with nearly 20 tabs and over 30 macros highlights a common challenge: as data grows in volume and complexity, existing systems can become unwieldy and inefficient. This situation resonates with others facing similar hurdles, as seen in discussions around transitioning from legacy systems, such as in the article "12 year analyst feeling like a dinosaur. Need advice on moving away from massive flat files without forcing Power BI on my team.." The need for more scalable, user-friendly solutions is evident.
The user's reluctance to trust AI recommendations, despite being encouraged to leverage SharePoint and Power BI to create a more robust web-based tool, underscores a critical point: the human factor in technology adoption cannot be overlooked. Many professionals are understandably cautious about automated suggestions, especially when they do not fully understand the underlying technology. This hesitance can stall innovation and maintain reliance on outdated systems. It’s a reminder that technology should empower users, not intimidate them. In contexts like these, it’s crucial to provide accessible education about new tools to ensure users feel confident in their choices, as discussed in articles like "Workbook from Microsoft Form encountering very long load times from excessive complex formulas."
As users consider moving away from Excel, they should evaluate not only the immediate needs of their data management tasks but also the long-term scalability of their solutions. The example of using XLOOKUP and macros highlights a level of sophistication that can become cumbersome when combined with a growing dataset. Professionals should seek tools that not only handle current demands but also adapt to future challenges. Solutions like SharePoint and Power BI offer functionalities that can streamline workflow and enhance collaboration, yet they require a mindset shift. Moving from a standalone Excel environment to a more integrated system can be daunting, but the potential for improved efficiency and data insight is worth the investment.
Moving forward, the question remains: how can organizations foster a culture of exploration and innovation among their teams? Creating an environment where users feel comfortable experimenting with new tools and methodologies is vital. This approach not only enhances productivity but also empowers users to take control of their data management processes. As the landscape of data management continues to evolve, those who embrace flexible, user-friendly technologies will likely find themselves at the forefront of efficiency and innovation. The journey from Excel to more advanced, AI-powered solutions is not just a technical transition; it represents a fundamental shift in how we view and interact with data in our daily tasks. As we look ahead, it's essential to consider how best to support these transitions and what frameworks will best enable users to thrive in a data-driven world.
I work in purchasing for a large company and I have been using an Excel based tool to manage order and logistics data for years. I started writing macros to help with my tasks and now the workbook has nearly 20 tabs and 30-odd macros...things are getting way too beefy and I am wondering if I need to use something else to manage it. The general layout is:
- 2 tabs with order data (one at PO level, one at line item level) using conditional formatting, XLOOKUP columns that reference other related data on other tabs, and macros for importing/processing new data.
- 2 tabs to track receiving--one is 2 years of history and the other is a working sheet w/macros to pull receiving for a given number of days, look at the line items against one of the first 2 tabs, and mark lines that are not full order qty (among other things). Also has a macro that 'steps' through each unique PO# in receiving record and filters the first 2 tabs + receiving history tab for that PO# so I can enter things into purchasing system more efficiently.
- Several tabs for reference data (logistics reports, truck schedules, etc.) which are either referenced manually as needed or actually feed into an XLOOKUP on the first tab.
- 2 tabs with a macro to pull certain PO data based on several parameters, which I manually review and then use another macro to automatically generate emails to vendors.
An AI told me I could use Sharepoint and Power BI to recreate the tool to be web-based and able to handle the volume of data I am working with, but I don't trust AIs lol.
Anyone have recommendations of programs or strategies? I have no help from my company so I am doing this on my own to help myself.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- 12 year analyst feeling like a dinosaur. Need advice on moving away from massive flat files without forcing Power BI on my team.I’ve been a Reddit user for a long time, but I’ve recently hunted and scoured the internet for communities to help me with this problem and I heard that this was possibly the place to be. I work as an Analyst in the CPG space (NWA area) and have been doing this for over a decade now. I know my way around a spreadsheet pretty well, but I feel like my technical skills kinda froze in time around 2020. Before COVID hit, I was finally learning proper Data Modeling and Power Pivot from a mentor, but then we got sent home, things changed and I’ve basically been surviving on VLOOKUPs and brute force ever since. I actually inherited some pretty advanced VBA tools from that mentor back in the day, but they were built for the old legacy system (DSS/Retail Link). When the retailer migrated everything over to the new platforms (Luminate/Scintilla and Madrid), all that old automation effectively died. The new export formats and cell limits broke the old code, so I never really rebuilt them and I'm back to manual stitching. I realized about 6 months ago that I am falling behind. I’m still building massive flat-file reports the hard way and it’s killing me. The situation is basically this: I pull data from a vendor portal (think Unify style system) that has a hard export limit of like 3M cells. If I want to pull 52 weeks of history for 500+ items across hundreds of locations, I get the "cell count exceeds limit" error. So I have to pull it in chunks—Dollars, Units, PODs separately—and stitch them together manually. It is a massive pain. To make it worse, my stakeholders are super old school. They want Excel files they can touch, pivot, and scribble on. If I send them a Power BI link, they won't even open it. So I need the flexibility of Excel, but the data volume is getting too big for the standard sheets I'm building. My goal is to build a "set it and forget it" system. I want to be able to just drop those raw data exports into a folder and have Excel just "eat" them. I know Power Query is probably the answer for the stitching, but is Power Pivot still the best way to handle the data model part? I need it to handle the heavy lifting (52 weeks, store level data) but output into standard Pivot Tables that my buyers can still play with. Also, side note on AI—everyone tells me to just use ChatGPT to write python scripts, but that’s not really what I need. I’m trying to get my data structure clean enough that I can feed the final Pivot Tables into an LLM to help me write the recaps/insights. Has anyone had luck with that workflow? Any advice on where to start or a specific course to bridge the gap between "VLOOKUP guy" and "Data Model guy" would be awesome. Thanks. submitted by /u/Excel_Dino_2026 [link] [comments]
- Workbook from Microsoft Form encountering very long load times from excessive complex formulasGood 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 🙇🏻♂️ submitted by /u/maverickrose [link] [comments]
- how did you improve your workplace's legacy vba macros?I recently transitioned to a non-clinical role in a public health care system. part of the on-boarding was a 12 page, 20 step tutorial on how to 'do the macros'. The workflow simplified is: - Get source data from EHR/BI - Open the excel online (microsoft 365) "Daily Review" workbook in the desktop ms excel. (hopes and prayers it doesn't crash) - copy data (columns of patient ID, demographics, medications... you get the idea) from EHR, paste into this Daily Review - run macro (click a button) which cleans, filters, applies conditional formatting i think - save - go back to excel online and resume editing there. The VBA code was created (not sure if it was written coz it has no documentation) by a colleague who is on extended mat leave. I can see a lot of 'modules'. Can't tell which is active. There are probably lots of historical decisions. The daily review file with its many many sheets is saved in multiple locations in case newcomers like me or others break it by accident. I am told we can't change anything like move a column closer to the beginning coz well we can't. I don't know VBA but could probably figure it out if I watch a tutorial on it. I am linux user and know basics of C, python and make good use of my claude code with the pro subscription but never really worked with spreadsheets. I am wondering if anyone was in a similar situation and how you managed it. Is moving to office scripts (typescript) a viable alternative? Any other life improving tips would be appreciate it. Or maybe I should just give up and focus the energy elsewhere? submitted by /u/Neat-Badger-5939 [link] [comments]
- 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]