Excel topics

Help with Formular to keep constants

Sneaky — Mon, 08 Jun 2026 03:46:39 GMT

I have 2 rows for percentage values as per this table. C3 will be the value of (C2 + B3) / 2. However I need to check for an empty value so my calculation is

=IF($B$3="",0, IF($C$2="",0,($C$2+$B$3)/2)) - I hope that is right,

	A	B	C	D	E	F	G
1			Name	Name	Name	Name	Name
2			50.25%	70.78%	60.14%	24.86%	53.64%
3	Name	60.12%	55.18%	65.45
4	Name	45.58%	47.91%

I then need to copy(drag) the cells across and down and provide the correct values, so it uses the same top value and left value. For example D3 will be =IF($B$3="",0, IF($D$2="",0,($D$2+$B$3)/2)). C4 will be =IF($B$4="",0, IF($C$2="",0,($C$2+$B$4)/2)) and so which will alter as I drag down or left and it keeps using the correct column value and row value based on what column/row it is in.

What I am doing now does not retain the right column or row value.

I hope I make sense.

Thanks.

LAMBDA : function generating #NAME error

Gilles_53 — Sun, 07 Jun 2026 10:18:54 GMT

Hie,

I juste discovered Lambda function, and i have a problem.

I created a lambda function that compute a cubiqual polynomial with the code :

Poly3:=LAMBDA(_a, _b, _c, _d, _x, ((_a*_x+_b)*_x+_c)*_x+_d)

Excel accepte de function, but when putting the function in a cell, it generate a #NAME error.

I Debug the Cell, and it seems that de fith argument is refused, with #NAME error !

but the data pasted to the function is a basic computed value !

I don't see anithing rong in my function.

Does anyone have an idear, or explaine me what rong in this code ?

Summarising a number of sheets to a front sheet.

Raykay99 — Sun, 07 Jun 2026 09:44:54 GMT

I have a simple spreadsheet with Sheets for individual investment details. The TABS contain the name of the investments eg BP

I am trying to put together the front sheet summarising certain data from the individual sheets, one row per sheet. Data such as Investment Name and Value.

How can I do this please?

Excel Labs : Grid editor open and stay blank

Gilles_53 — Sun, 07 Jun 2026 09:43:11 GMT

Hie,

I have juste installed Excel Lab on my machine : Microsoft® Excel® LTSC MSO (16.0.14334.20688) 64 bits

And i have never seen anything in the Grid editor.

Excell function problem

TrueNorth — Fri, 05 Jun 2026 07:09:58 GMT

I have been using Excel for a good few years, i have a spreadsheet that has been working fine until now.

My main problem is that when i copy & paste data everything works fine, i have been able to add cells to make a new total.

but when i cut & paste the value of cells i get a REF error.

so when i need to move a series of cells higher or lower any cells that contain word they move fine, the cells containing numbers move but the cell with the function just goes to #REF, the function was working okay with my previous versions of Excel but now it will not.

if anyone has got any idea what the problem is or any way of fixing it i would be most gratefull

MATCH formula anomaly?

kyyall — Thu, 04 Jun 2026 22:43:01 GMT

Can anyone explain this? If I simply type 490.44 and then calculate 490.44 as 490.44/40*40 (or even directly B3/40*40) excel recognizes both as equal through a simple A=B calculation yet not with a MATCH calculation. What is more odd is any other number I use does not cause this error...

Excel 365: Unexpected Predictive Behaviour Causing Retroactive Cell Changes in Structured Dataset

JRAJS — Thu, 04 Jun 2026 02:57:25 GMT

Hi everyone,

I’m posting this to share an unusual and potentially important issue I encountered in Excel 365 this morning. I’ve already submitted a detailed report to Microsoft via the Feedback Hub, but I felt it would be useful to document it here for visibility and to see whether anyone else has experienced something similar.

While entering data into a structured table (a parts‑catalogue workbook), Excel began exhibiting behaviour that did not match Flash Fill, AutoFill, or the standard predictive typing feature. Specifically:

Grey predictive text appeared beyond the cursor and extended into cells below the active cell

Excel began rewriting previously validated cells in the same column

Each time I pressed Enter, Excel updated earlier rows based on its latest inferred pattern

Attempts to correct earlier rows caused further cascading changes

Undo did not revert all changes

The behaviour resembled an AI‑style inference engine trying to “learn” my pattern, becoming progressively more confused

This was not the normal predictive typing behaviour (which only shows grey text inside the active cell and does not affect other cells). The behaviour stopped only after I clicked the Flash Fill button twice, which suggests an internal state reset.

I’m not seeing the issue now, and other workbooks behaved normally, so this appears to have been a transient internal mode rather than a persistent setting.

I’m sharing this here because:

it caused silent data corruption in a structured dataset

it may indicate an experimental feature or feature‑flag activation

others working with structured data may want to be aware

I’d be interested to know if anyone else has seen similar behaviour

If helpful, I can provide the reproduction steps, screenshots, and the engineering‑style report I submitted to Microsoft.

Thanks,

John

Power Query Editor - Unpivot Multiple Delimited Columns

TCatron18 — Wed, 03 Jun 2026 14:55:54 GMT

Hello-

I'm looking for the most efficient way to unpivot multiple columns that have delimited data.

For example, I have the following data:

Date	User	Product	Position	Job
1/1/2026	1234	ABC	Position 1 \| Position 2	Job 1 \| Job 2
1/1/2026	5678	DEF	Position 3	Job 3

And I'm looking for the data to be as follows:

Date	User	Product	Position	Job
1/1/2026	1234	ABC	Position 1	Job 1
1/1/2026	1234	ABC	Position 2	Job 2
1/1/2026	5678	DEF	Position 3	Job 3

Series fill a formula down a column automatically skipping a set number of rows

SteveK14 — Wed, 03 Jun 2026 14:25:10 GMT

I am trying to fill a formula down a column every 6th row but incrementing the variable in the formula ($A2) for each entry. The formula uses the Take command and inputs 5 rows of data, so to keep it from "spilling" I need the formula to increment every 6 rows, having one blank row between each section. I have been able to accomplish "copying" it each 6th row with VBA #1 and filling the series with VBA #2 but can't figure out how to combine the two. Any help would be appreciated.

VBA #1:

Sub FillEvery6thCell()

Dim ws As Worksheet

Dim startRow As Long, lastRow As Long, col As String

Dim formulaText As String

Dim r As Long

' Set your sheet and parameters

Set ws = ThisWorkbook.Sheets("Top 5 Employees")

col = "A" ' Column to fill

startRow = 10 ' First row to start filling

lastRow = 60000 ' Last row to fill

' Get the formula from the starting cell

formulaText = ws.Range(col & startRow).Formula

' Fill every 6th cell

For r = startRow + 6 To lastRow Step 6

ws.Range(col & r).Formula = formulaText

Next r

Range("A2:A60000" & iRow).Replace What:="@", Replacement:="", LookAt:=xlPart, _

SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

MsgBox "Formula copied to every 6th cell in column " & col

End Sub

VBA #2

Sub FillFormulasDown()

Dim sourceCell As Range

Dim fillRange As Range

' Define the cell containing the formula

Set sourceCell = Range("A2")

' Define the target range

Set fillRange = Range("A2:A5000")

' Fill formulas down

sourceCell.AutoFill Destination:=fillRange, Type:=xlFillSeries

End Sub

Excel table not using updated formula in new rows

anna8 — Wed, 03 Jun 2026 13:26:46 GMT

I have an Excel spreadsheet that keeps giving me the same issue with the formulas in column O.

Originally, column O contained the following formula:

Original formula:
=IF(R4<>"", R4, Q4) + IF(ABS(I5-I4) < 30, 25/1440, IF(ABS(I5-I4) <= 50, 40/1440, IF(ABS(I5-I4) <= 150, 60/1440, IF(ABS(I5-I4) <= 250, 80/1440, IF(ABS(I5-I4) <= 300, 100/1440)))))

The formula has since been updated to:

Current formula:
=IF(R4<>"", R4, Q4) + IF(ABS(I5-I4) < 30, 25/1440, IF(ABS(I5-I4) <= 50, 40/1440, IF(ABS(I5-I4) <= 150, 60/1440, IF(ABS(I5-I4) <= 250, 80/1440, IF(ABS(I5-I4) <= 300, 100/1440))))) + C5/24

However, whenever I insert a new row, Excel automatically fills it with the original formula instead of the updated one, even though I have been using the updated formula for quite some time now.

How can I make Excel recognize and use the updated formula when new rows are inserted?

XMATCH removed?

GRMat158 — Wed, 03 Jun 2026 09:53:10 GMT

When I opened a workbook today all references to XMATCH were prefixed by _xlfn. - (I use 2021)

Has it been removed or is this a temporary glitch?

vlookup error

tilakagrawal — Wed, 03 Jun 2026 06:36:03 GMT

i used vlookup in my spreadsheet, but doesnt work ...

case: i have a column a of 151 vehicle number , now i have to find principal outstanding from two column b & c table containing 196 vehicle number with principal outstanding , as i insert formula of =vlookup(a1,b1:c196,2,false) and it gives the desired result but then the problem occurs whhen i drageed formula, the dragged formula also dragged the table_array cell (like in a9,b9:c207,false), and when the vehicle number is store before the table_array it shows #N/A, i.e. if a9 contain vehicle number x and if x is at b4, then formula doesnt work...

pl help

Error on regression analysis

Charly3 — Tue, 02 Jun 2026 09:46:09 GMT

Hello everyone,

I want to perform a regression analysis on my survey data, but I keep getting this error message: “Regression - The RGP() function returns an error value. Check the input ranges.” I’ve since figured out that it’s due to a specific row, but I couldn’t find any incorrect numbers, letters, or anything else in it that would trigger this message. Does anyone know what might be causing this and how I can fix the problem?

Thanks in advance!

sort by column then by numbers in a row

pevenden — Sun, 31 May 2026 22:08:55 GMT

HI all

I have a table of results pictured here. Once the results are in, I sort the table by the Total Score Column. When we have a tie (as in line 3 & 4, I need to sort by who has the most highest score... so, who has the most 9 (equal here), then by most 8.5 (line 4 wins the tiebreaker, 4 over 2)

Can I do this so it doesn't affect the order of the columns but just the rows involved.

Dynamic Column cell reference with autofill

Andy2269 — Sun, 31 May 2026 12:22:07 GMT

Hi,

I want to increase the column reference in a formula by 2 columns as it is copied across columns.

So in Column A the formula is =SUM(D3:E3)

When I fill this across Column B I want the formula to be =SUM(F3:G3)

I have experimented with the INDEX and COLUMN functions but not been able to get the intended result.

Excel Find / Command-F crashing on Mac Tahoe (Office 2019 / Excel 16.78) – anyone else?

kevs44 — Sat, 30 May 2026 22:18:50 GMT

Excel Find / Command-F crashing on Mac Tahoe (Office 2019 / Excel 16.78) – anyone else?

Mac mini + Tahoe 26.5.

Excel 16.78 (Office Home & Student 2019).

Problem started after recent Tahoe update.

Classic Find is behaving badly and often crashes Excel.

Workflow:

click/select a column
Edit → Find → Find… or Command-F
search for text or email fragments (ex: "kristin" or part of an email)
Excel may crash or Find becomes unusable

Things already tried:

full Excel reinstall
blank workbook test
new macOS test user
Office container reset / plist tests
preference resets
same Tahoe version confirmed

Upper-right Microsoft Search appears, but that is not equivalent to classic worksheet Find for email-column workflow.

Question:

Is this a known Tahoe / Excel 16.78 Find bug or Search-routing issue? Has anyone fixed this or seen it resolved with a newer Excel build or update?

Copilot icon missing in Excel Ribbon

MissAB19 — Fri, 29 May 2026 18:55:18 GMT

Hello. I have a problem where the Copilot icon is missing from my ribbon in Excel. It showed up before on one of my accounts but then it disappeared the same day. I was wondering if anyone could please help me fix this and get the Copilot icon back in the ribbon. Thank you.

How to filter to include blanks without selecting blanks.....

Nelly3007 — Fri, 29 May 2026 11:45:24 GMT

Good Morning All,

I have a spreadsheet on Excel Online version that people fill in for requesting PPE and it has drop down boxes so they can select what they need. The people filling it in are not the most knowledgeable with computers never mind Excel just FYI for context.

When the spreadsheet gets filtered eg to show only the requests for Bob, it shows the items for Bob and then the row underneath is row 532 which is outside my data and therefore the drop down boxes do not show so when someone comes to fill it in they can't select what they want to order and just end up free typing which defeats the object and means things can get missed.

I want to be able to filter the spreadsheet in any column EG Bob so it shows the items for Bob and then the next row is the next 'available' row which in this example would be row 23 but also people are filling this in daily so row 23 is not set. Tomorrow it will have more data in and the next 'available' row could be row 30 so obviously tomorrow I would like to be able to filter to Bob, see all of Bobs items and then the next row showing is row 30.

This is a test doc:

Test doc

Hope that makes sense and hopefully I am not asking for the impossible!

Many Thanks,

Names in Excel Name Manager

aekbus — Fri, 29 May 2026 02:21:16 GMT

I have a spreadsheet ( I will call it spreadsheet "A") driven by macros that creates a new worksheet each month. I am developing a second spreadsheet I will call it spreadsheet "B") that links to it for a monthly summary. I have given all the cells names that are to be linked. The issue I am dealing with is when a new monthly tab is created on "A", I want the links from "B" to be updated to the new month. The approach I am taking is to delete all of the names in "A" and recreate them for the new month. (The reason I want to delete the older names is because over time, the Names list would grow into a very large list of obsolete names). The problem I am encountering is that when a name is deleted, a dialog box appears that the user has to acknowledge that, Yes, I want to delete this name. I want to make this process transparent to the user.

So, my question is: Can I delete a name without generating this dialog box?

BYROW/BYCOL/MAP Variants for Nested Arrays + BENCHMARK

Medohh2120 — Thu, 28 May 2026 20:42:20 GMT

Hey everyone!
I made some simple BYROW, BYCOL, and MAP variants that can return nested arrays, and I also made a BENCHMARK function for performance testing.

Here's some code for testing:

BYROW⊟ = LAMBDA(array, function, [orient], LET( me, LAMBDA(me, seg, LET( n, ROWS(seg), IF( n = 1, function(seg), IF( orient, HSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ), VSTACK( me(me, TAKE(seg, INT(n / 2))), me(me, DROP(seg, INT(n / 2))) ) ) ) ) ), IFNA(me(me, array), "") ) );

I didn’t put a huge amount of effort into polishing this but In my tests on my device, these performed a lot better than using REDUCE + VSTACK for the same kind of thing, so maybe it’ll be useful to someone.

Really curious to see how people use it, and if something looks like it should be optimized or changed, say so.

I'll update them regularly, fix bugs whenever I can.

You can find the rest of them on my Gist pages: BENCHMARK, SpillNest.