Autocomplete not working for data validation
At work we have a shared excel sheet (we open it on the web) and we use it to store data. We had it set up with data validation list's and when we typed into the boxes, it would autocomplete one of the options. Now, you have to click the down box then start typing because autocomplete has stopped working. Autocomplete is still checked in options, and everything is still the same setting that it was previously as far as anyone can tell. Please help!
Excel Online - All Sheets Menu Option Greyed out.
Hello - I have an issue where the Excel Online is showing the All Sheets Menu greyed out for all excel sheets. This was not an issue yesterday. The only way Ive found to turn it back on was to Activate Viewing Mode, then flip it back to editing mode which should not be the way its done, if yesterday it worked just fine when i opened the file. How do i get the All Sheets button to turn back on permentantly?How to optimize a large XLOOKUP table with volatile data sources?
I'm working with a fairly large Excel workbook (around 100,000 rows) that relies heavily on XLOOKUP functions to fetch data from multiple sheets. The problem is that performance has started to slow down significantly, especially when I open or save the file, or make changes that trigger recalculations. To envision it : Each lookup table ranges from 10k to 30k rows. Some of the source data is linked to external workbooks or updated via Power Query. I've disable automatic calculation for now but that's not ideal for the final users. I'm not using any array formulas or dynamic arrays intentionally, but the sluggishness persists. is there a best practice for optimizing XLOOKUP performance in large datasets ? Are there alternatives (like INDEX/MATCH) or Power Query Joins) that perform better in these situations ?
Disable Auto-Fit/Auto-Wrap on Web?
About a week ago Excel rolled out a new feature to me that offered to auto-fit my cells whenever needed. Not knowing what it was, I accepted it and turned it on. Now, I want to turn it off and cannot find it anywhere. I've tried following https://support.microsoft.com/en-us/office/undo-automatic-formatting-in-excel-54eba206-110c-445a-89f1-c4eb67a36bd4?showContactUsNav=False&ns=EXCEL&version=90instructions, but these instructions are for the app, not web, and the issue doesn't replicate on the app. Below in E46 is an example of the issue. I want to paste links without them auto-wrapping my text or auto-fitting the column. I have tried turning wrapping on and off again in this column, but it doesn't work. I could manually disable the wrapping every time I enter a link, but I would much rather just turn this feature off.
Fill BLANKs down, up or both in column(s) - similar to Power Query
Hi Looked at +/- replicating Fill Down and Fill Up functionnalities in Power Query with a LAMBDA. My attempt below: // FillDownUp: =LAMBDA(array, LET( Data, DROP(array, 1), DataCols, COLUMNS(Data), FillCol, LAMBDA(array, SCAN( IF( ISBLANK( TAKE(array, 1) ), XLOOKUP(FALSE, ISBLANK(array), array, ""), "" ), array, LAMBDA(seed,x, IF( ISBLANK(x), seed, x)) ) ), FillStack, LAMBDA(seed,col, HSTACK( seed, FillCol( CHOOSECOLS(Data, col) ) )), DataFilled, IF(DataCols = 1, FillCol(Data), DROP( REDUCE(0, SEQUENCE(DataCols), FillStack),, 1) ), VSTACK(CHOOSEROWS(array, 1), DataFilled) ) ) Open to alternatives...
External Links in shared file (Excel for Web)
Hi, I have a master spreadsheet with external links to other spreadsheets. The master spreadsheet is shared with several users including some external users. The users within my organisation have no issues to enable the external links and update the values, however the external users are not able to enable the links. Instead they see this message and the values remain the same: "Links Disabled: Links to external workbooks are not supported and have been disabled." I have further tested the above by sharing the master spreadsheet with a (free) Microsoft outlook account and I was actually able to enable the external links. Is it possible that non-Microsoft accounts are not permitted to enable external links in Excel Online? Does anyone know a workaround for this? BenExcel Tools for Network & Windows
Excel Tools for Network & Windows Some time ago I already shared an earlier version of this project. Since then, I have added several new features. These tools are based on functionalities that already exist in Windows and its associated software. I have consolidated them into a single Excel-based interface, allowing all tools to be executed directly from Excel. The files are free for private use. For business or enterprise environments, a more comprehensive toolbox can be developed, enabling direct support, repair, management, monitoring, and control of users and systems. Everything can be customized according to specific requirements — the scope depends solely on the desired functionality, time investment, and budget. I appreciate any positive feedback, suggestions, or constructive tips. If this project is not of interest to you, please feel free to ignore it. Thank you, and I wish everyone happy holidays.Power Query Formula to Calculate Current Count From Previous Count per Name using Date
Hi everyone! I have a dataset with the following columns: Point Name, Pump Count, Record Date, and Index. My goal is to create a calculated column in Power Query that computes the following for each Point Name, based on the Record Date: If (Current Pump Count - Previous Pump Count) >= 0, apply: (Current Pump Count - Previous Pump Count) * 0.3 If the result is negative, use this formula instead: ((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3 Problem: I tried duplicating the Pump Count column and creating a custom column for the previous count using Table.Buffer, but it didn’t align correctly by Point Name and Record Date. Indices may not be sequential, so referencing the immediate index won’t work. I considered grouping by Point Name and sorting by Record Date to dynamically calculate the Previous Pump Count, but was unsure. I tried this formula for Previous Pump Count but it doesn’t align correctly for Point Name or handle skipped indices. My Current Formula: Table.Buffer(#"Duplicated Column3")[Pump Cycle Count]{[Index] - 1} otherwise null Desired Process: Group by Point Name. Sort within each group by Record Date Use the formula to compute the difference: If (Current Pump Count - Previous Pump Count) >= 0, apply: (Current Pump Count - Previous Pump Count) * 0.3. Else, apply: ((1,000,000 - Current Pump Count) + Previous Pump Count) * 0.3. How can I implement this in Power Query, ensuring it dynamically references the Previous Pump Count for the same Point Name, even if indices are skipped or Record Dates are close?
