BigSpill: A 92‑Function Excel LAMBDA Library for Dynamic Arrays
What is BigSpill? BigSpill is an Excel LAMBDA library containing 92 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
Please Help_ Disappeared Excel Doc
Dear Expert, I just lost my Excel Document that improved after I have downloaded from the Chat GPT. I have improve many different sheets on the documents but it is now only see the excel sheets that I made yesterday. I can only see the last page I improved today. Please help me.Excel Copy & Paste / Clipboard
When I copy one or multiple cells from excel, and then go over to another program on my computer and use the "copy" function again, and then go BACK to excel, and paste in a different cell - the original copy from excel is the one that pastes. It's almost like the second copy function from another program on my computer does not slate clean the clipboard - and almost like the excel has its own clipboard that separates itself from the rest of the computer. If i copy a cell in excel, and then copy other stuff from a different program (non microsoft, microsoft, anything really) I want the original excel copy to CLEAR. This is something new and randomly starting occurring over the past year or so to me and all my colleagues. This problem is most prevalent with Bloomberg for me fyi. Thanks for any help!Welcome to the Excel Community
The Excel Community is a place we've built for all of you. You can learn more about how to do something with Excel, discuss your work, and connect with experts that build and use the product. With over half a billion Excel customers, we want to engage with you in fundamentally different ways and the community is a starting point for that. Our community helps answer your product questions with responses from other knowledgeable community members. We love hearing feedback and feature requests from you which helps us build the best version of Excel ever. If you have found an outage or a bug please post at our Answers forum. We look forward to getting to know you! Sangeeta Mudnal & Olaf Hubel on behalf of the Excel Team
When #VALUE! isn’t an error: The hidden reference behavior of Excel’s dynamic arrays
1. Mechanism Overview Functions such as TAKE, DROP, OFFSET, and INDIRECT normally produce a range when supplied with scalar parameters. When instead a 1-D or 2-D numeric array is passed to one of these parameters, Excel attempts to generate multiple range outputs (one per array element). Since Excel’s grid architecture does not allow a single function call to return multiple ranges in parallel, the function fails. The visible result is an array filled with #VALUE!. Example patterns: =TAKE(range, {1;2;3;4}) =DROP(range, {1;2;3;4}) =OFFSET(range, {1;2;3;4}, {1,2,3}) =INDIRECT("A1:B"&{1;2;3;4}) Excel returns: { #VALUE!; #VALUE!; #VALUE!; #VALUE! } Although the values appear identical, each #VALUE! is associated with a distinct internal range reference corresponding to the attempted slice, offset or address. This is a side effect of Excel’s reference-binding layer, which attaches metadata to #VALUE! propagation structures when a reference-producing function fails after partially resolving its operands. 2. Behavior Characterization 2.1 Reference Retention Each #VALUE! error produced by the above functions contains a deferred reference representing the range the function attempted to construct. The reference is not materialized because: a multi-range return is not allowed, and the evaluation is aborted at the final conversion stage. However, the binding phase (operand resolution) has already created a reference object. This object is preserved internally and passed forward along the calculation chain. 2.2 Deferred Evaluation Functions that inspect operand values (e.g., SUM, MIN, COUNT) cannot extract data from these encapsulated #VALUE! tokens and will propagate the error. Functions that inspect operand references — such as MAP, SCAN, REDUCE, MAKEARRAY, and aggregation functions that re-evaluate their inputs individually — trigger evaluation at the point of consumption. Example: =MAP( TAKE(range, {1;2;3;4}), SUM ) In each MAP iteration: ref is a #VALUE! carrying an unresolved range reference. SUM(ref) requests value-level evaluation of that range. The reference is resolved at that moment only. The resulting scalar is passed downstream. Thus, the #VALUE! error acts as a strict thunk. 3. Functional Implications for Formula Construction 3.1 Range-Object Semantics The technique enables modeling of ranges as first-class reference structures, not arrays of extracted values. This allows: passing range slices through pipelines keeping intermediate objects lightweight avoiding large intermediate spillage reducing recalculation costs for multi-slice operations 3.2 Lazy Slice Evaluation Using {n1; n2; …} arrays in index parameters creates a vector of deferred slices. Functions downstream resolve these slices on demand and only for the specific iteration that needs them. This is especially useful for: sliding-window logic multi-pass transformations tree/graph-like iterative algorithms in Excel dynamic partitioning of ranges recursive constructions implemented with SCAN/REDUCE
Help with a simple calculator for figuring a calculated cost
Hello, I am making a simple calculator for work. I enter square footage in cell E16 and have an equation to give a dollar value in cell K16. Trying to make things simple for others to price a job. This is my formula (=E16*0.45) in cell K16 that works perfect for my needs except for one thing. I need to set a minimum value for this equation. The formula needs to have an equal to or greater than 300 rule. I have a minimum price of 300 for any job done. So for example if I enter 500 square foot in E16. Cell K16 currently will have 225 as the value (500*0.45=225) but I need it to calculate 300. Any square footage over 667 will give me the value greater than 300 then. Please help me with this as I have looked over all the help topics and many forum threads but nothing seems to help. Using Excel with Office 365 on Windows 11 PC. Pictures below for reference. Thanks for your time - Chad
Excel Desktop Filter Checkboxes Not Visible – Windows 11 / Office Home & Student 2021
Excel Desktop Filter Checkboxes Not Visible – Windows 11 / Office Home & Student 2021 I am facing a persistent display/rendering issue in the Excel desktop application when using filters. When I apply a filter to a column, the filter dropdown opens correctly and the values are displayed. The filter functionality itself also works. However, the checkboxes beside the individual values are not visible properly. As shown in the attached screenshot, the values such as 11, 12, 22, and 34 are displayed in the filter list, but the checkbox controls beside them are either missing/not rendered correctly. Interestingly, the Select All checkbox area also appears abnormal. System details Operating System: Windows 11 Microsoft Office: Home & Student 2021 Application: Microsoft Excel Desktop Office is fully updated with the latest available updates. The same Excel file works correctly in Excel for the Web, where all filter checkboxes are displayed normally. Troubleshooting already performed by Microsoft Support Microsoft Support has already remotely checked the system and tried several troubleshooting steps, including: Reinstalling Microsoft Office Creating/modifying relevant Windows Registry entries Creating system restore/checkpoints during troubleshooting Creating and testing with a new Windows user profile Checking and installing all available Office updates Unfortunately, the issue still persists. Since the problem does not occur in Excel for the Web, it appears to be specific to the Excel desktop application's UI/rendering on my Windows 11 system. Has anyone experienced a similar issue with filter dropdown checkboxes not rendering or appearing invisible in Excel Desktop? Could this be related to: Excel hardware graphics acceleration/rendering Windows display scaling or DPI settings GPU/display drivers Office 2021 UI rendering Windows 11 accessibility/display settings A known Excel Desktop bug Any suggestions, known fixes, or confirmation of a similar issue would be appreciated. Screenshot attached for reference.Does a LET variable get computed even if it's only referenced inside IFNA's fallback argument?
In the formula below, expensiveMatch is defined in LET but only referenced as IFNA's fallback argument: =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), expensiveMatch, INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), IFNA(x, expensiveMatch) ) Question: When x resolves successfully (no #N/A), does expensiveMatch still get computed because it's a top-level LET variable - or does Excel skip it since IFNA's fallback argument is never reached? I know IFNA short-circuits its fallback argument, and LET avoids recomputing a variable if referenced multiple times - but I haven't found much documentation on the behavior of Let. Is the below a more efficient way to write the formula? =LET( matchRow, XMATCH(1, (A1=Sheet2!$A$1:$A$1000)*(B1=Sheet2!$B$1:$B$1000)), x, INDEX(Sheet2!$C$1:$C$1000, matchRow), IF(ISNA(x), INDEX(Sheet3!$C$1:$C$500, XMATCH(A1&B1, Sheet3!$A$1:$A$500&Sheet3!$B$1:$B$500)), x ) ) Is this restructuring necessary, or does the first version already skip the unused computation? Basically I'm wondering if Let computes lazily/defers calculation in the way SQL's optimizer works. Input from anyone with insight or knowledge into the calc engine would be greatly appreciated. Thanks (Currently on Microsoft Excel for Microsoft 365 MSO (Version 2607 Build 16.0.20228.20190) 64-bit)
