How to handle massive workbooks that freeze and choke Excel? Let's share tactics
Hi everyone, I wanted to open a practical discussion because I'm losing my patience with heavy spreadsheets lately, and I'm sure many of you deal with this daily. We’ve all inherited that one monster workbook built by someone else (or even ourselves) packed with volatile functions (TODAY, INDIRECT, OFFSET), massive VLOOKUP/XLOOKUP ranges across multiple sheets, and conditional formatting rules that somehow multiplied into the thousands. Now, every time you type a single value or change a filter, Excel freezes for 10 seconds, the CPU spikes, and you’re left staring at the dreaded "Calculating..." green bar at the bottom. Beyond the obvious advice (switching calculation to manual, breaking external links, or moving data to Power Query / Data Models), what are your absolute go-to, non-standard survival tactics when optimizing a sluggish workbook that management refuses to let you rebuild from scratch? How do you diagnose the exact bottleneck? Let's share what actually works in production.
Looking for Excel Function Advise
I need some guidance on what function to use to sum of a data range for criteria contained in another data range. Down in Cell E78, I would like to count the number of adults attending a wedding (range $E5:$E68) IF Dn in range $D5:$D68 = "Y". The Countif or countifs does not seem to be quite the right function. Please provide any ideas you may have. Thank you!
Return the Cell(s) with the Highest SUM() between groups of Cells
So, long story short, I've been banging my head against a wall with this one. I'm teaching myself Excel and I'm struggling to wrap my mind around this one. After trying combo, after combo, after combo, for two days now and every forum failing me. I would like to ask for help from the kind and intelligent internet! I feel like I must be missing something basic... Goals: Return the name/cell of the "Branch"/"Region" with the Highest SUM() between groups of Cells Return the SUM() of the "Branch"/"Region" with the Highest SUM() between groups of Cells Expected Output of Example: Top Region: "Name 1" Top Region Total: 15 Top Branch: "City 4" Top Branch Total: 9 Current Formulas: =IFERROR(INDEX(Table25[Branch], MODE(MATCH(Table25[Branch], Table25[Branch], 0))), "N/A") This successfully grabs the branch with the highest number of occurrences, but I need total points related to that branch, not just the most occurring one. My mind says I need to essentially have a running list of each Branches points, SUM() the points, and return the highest SUM() for Total and the Cell linked to the highest total. I'm assuming I'll have to use MAKEARRAY() and BYCOL(), but I'm not sure how to incorporate them. =SUMIF(Table25[Branch], "Name 1", Table25[Points]) This can manually grab the total of a specific name... But, I need something that can discover the highest name on its own and adapt if names change instead of static specifications.
Formula Help
I have created a spreadsheet for driver sign-on for morning and afternoon buses. I have route number, bus number, driver name, expected sign-on time, actual time signed on field, and a signed-on field. I have done conditional formatting so when a driver signs on, the user puts a Y in the signed-on field. That then changes from red to green to show that the driver is out on route. I want the actual time signed on field to automatically record the time that the user adds the Y to the signed-on field. I have the following formula =IF(F7="Y",MOD(NOW(),1),"") This inserts the time into E7. My issue is that if I then put Y into, say, F14 five minutes later, it changes the time in E7 to the current time. What should my formula be so that the E column times do not change when another driver is logged on? Thanks for your help and suggestions :)
Remove a comma from a number treated as text
Greetings! I'm new to this forum, and I don't know if this is the right place to ask. But, here it goes: I have a column of currency values which is a mess. I managed to clean it as much as possible using the functions SUBSTITUTE and TRIM. These functions transformed the unformated numbers into text, and now it looks something like this: 6,764,68 1,319 1,948 301,40 2848,08 The former is a sample from 165 entries. Is it possible to remove the first comma of the string, so the values look like this instead? 6764,68 1319,00 1948,00 301,40 2848,08 What I would like to do is to remove the second comma from right to left in each value, while keeping the first which marks the cents of the transaction. Can this be done? Thanks all in advance :D
BigSpill: A 97‑Function Excel LAMBDA Library for Dynamic Arrays
What is BigSpill? BigSpill is an Excel LAMBDA library containing 97 functions across 10 categories, built from extensive experimentation with dynamic arrays. The goal is to provide elegant, efficient tools that make sheet‑level formulas easier to read, more modular, and more dynamic. The library includes a mix of: quality‑of‑life helpers essential primitives mid‑level operators developer‑level tools a few functions you might not expect to see in Excel Who is it for? Everyone. BigSpill aims to make complex operations more approachable and expressive. A few examples from the library: Pairwiseλ Staircaseλ Grainλ PolarGridλ Convolveλ Foldλ Tessellateλ Revealλ Knapsackλ Magnifyλ Traverseλ The repository includes full documentation and 11 sample workbooks so you can explore the functions without setup. If you’re interested in experimenting with dynamic arrays, or LAMBDA workflows, feel free to take a look. Feedback and suggestions are always welcome. I've attached 1 onboarding workbook. There are 10 more at the link below. GitHub: BigSpill
