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? BenPower 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?Complex numbers in Excel
Did you know that Excel can handle complex mathematical operations with complex numbers? Complex numbers, expressed as "a + bi," where 'a' and 'b' are real numbers and 'i' is the imaginary unit, can be powerful tools in various scenarios. Think engineering, physics, signal processing, or even financial modelling. Excel offers native support for complex numbers, and you can perform basic operations like addition, subtraction, multiplication, and division just as easily as with real numbers. If you haven't explored this yet, give it a shot! It's as simple as entering a formula like =IMSUM(A1:B1) or =IMPRODUCT(A1:B1) One nifty feature is the ability to convert complex numbers from rectangular to polar form and vice versa. This can be a game-changer in certain calculations, especially when dealing with phase angles or magnitude-based analyses. For this you can make use of the =IMABS(A1) and =IMARGUMENT() functions. As complex numbers are considered to be text within Excel you will need special functions to perform arithmetic with them, such as IMSUM, IMSUB, IMPRODUCT, and IMDIV. Excel can help you plot complex numbers on the complex plane using the XY Scatter chart! you will need the IMREAL and IMAGINARY functions to extract the real and imaginary coefficients from the complex number to use them to plot the numbers. This feature can be invaluable when dealing with complex data sets or analysing the behaviour of complex functions. If you're interested in learning more about complex numbers, I made a video covering all functions that work with complex numbers in Excel: https://www.youtube.com/watch?v=_A2DIUibkmk Have you worked with complex numbers before in Excel? What specific use case did you have? Were there any obstacles you had to overcome?
