Excel topics

Please allow this foundational concept for algorithms

JuanjoB — Wed, 20 May 2026 18:25:46 GMT

Hello dear friends of Excel.

Please let me begin by thanking you all for giving us the excellent tool that Ms Excel is.

I've being working practically my whole Computer Engineer career (~18 years) with this awesome tool, and I cannot imagine doing my work without it.
It really is excellent, and has been enhancing a lot in the last years.

In this occasion I would like to ask you to consider including in Excel, native support for "empty arrays", that is, arrays that have "0" (zero) items.

This feature would help a lot in the following type of algorithms:

When you have to scan a range of cells that hold the items of a set named "S", and
from that set you have to select only the items that satisfy a given condition { ALL i | such that CONDITION(i) = TRUE }, and
with those selected items (i) you have to construct a new vector named "V"
In this case you usually need to begin with an "empty array", that is the initial value before adding any selected item, and
at some point as you process the algorithm, maybe you will find the 1st. item to add to the "empty array"
Maybe you won't find any item that satisfy the condition, and at the end of the algorithm, you will end up with an "empty array" as a result.

To illustrate this type of algorithm, let me put an example:

=LET(
vector; SEQUENCE(10);
new_vector; REDUCE(
0;
   vector;
   LAMBDA(building_vector;vector_i;
IF(RESIDUO(vector_i;2)=1;
VSTACK(building_vector; vector_i);
building_vector
   )
   )
);
INDEX(new_vector;SEQUENCE(ROWS(new_vector) - 1) + 1)
)

This "algorithm", scans a set S = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 }
from S, it selects only the "odd numbers" { ALL i | such that "i is odd" },
it takes every odd number and adds it to the array named "building_vector",
"building_vector" begins as a non-empty-array, holding "1 sentinel item" at the beginning of the algorithm (the number "0"),
at the end of the algorithm, I have to do a work-around, to remove manually the 1st. sentinel item from the array

The cons of this approach are:

sometimes the resulting array will not have any selected item, that is, it will be an "empty array", and as such, Excel will generate an error showing #CALC!
as Excel doesn't currently support empty arrays, I have to do this workaround to add an initial sentinel item at the beginning of the algorithm, and then I have to remember to remove it at the end of it

Dear friends of Excel, I consider empty arrays to be a "foundational concept for algorithms". It's like the "zero" for numbers, but for arrays.

It would be nice to be able to write this kind of algorithms with the aid of Excel, being able to natively support "empty arrays" without me having to do the extra work of handling a simulated situation of an empty array.

I present you this request, and I ask you to please consider the importance of this feature.

Thanks in advance!

Forcing Gridlines to print through shaded cell formatting

NastyMcSnert — Tue, 19 May 2026 20:22:57 GMT

I know how to force gridlines to print but if any cells are formatted with a fill shading in them, the gridlines will not print through them. Is there a way to print gridlines in formatted cells which are filled?

No gridlines will print through the shaded cells.

Marie annecy casimir

Ade_Blo825 — Tue, 19 May 2026 17:13:17 GMT

Why isn't AI responding?

RH

RH_ — Tue, 19 May 2026 13:55:05 GMT

Is it possible to select a color in Excel and highlight different cell by only clicking on them and not selecting fill the whole time?

How to create a 2 line graph of paired values with a bar linking each pair?

Modanistas — Tue, 19 May 2026 08:36:30 GMT

I've create such a graph by accident. It's described as a "Line Graph with Markers".

How can I create a new one?

Logic Explanation

ExcelNewBie — Mon, 18 May 2026 19:00:21 GMT

=IF( AND( [@[Group(without DM Converted/Created Contact)]] = "Marketing Lead", OR( A10739 = "John Doe", A10739 = "Jane Smith", A10739 = "Michael Johnson", A10739 = "Emily Davis" ) ), IF( AND( XLOOKUP( [@[Converted Contact: Contact Id Safe]], 'DM with AddOn Created COntacts'!E:E, 'DM with AddOn Created COntacts'!F:F, "Sales Lead" ) = "Sales Lead", [@[First Form Fill Detail]] = "", [@[Last Form Fill Detail]] = "" ), "Sales Lead", "Marketing Lead" ), [@[Group(without DM Converted/Created Contact)]] )

Hi Excel Experts. I came across this formula from one of our resigned team members. can somebody explain how the logic works? I am just a simple photoshop user and was assigned to check this.

Slicer

Lawrence_Lam_320 — Mon, 18 May 2026 13:40:47 GMT

Hi Expertises,

Need your help again. I have pivot table as below. I need to capture the Product A sales amount under the following scenarios.

Filter. Select any one country in slicer
NO Filter. Select all country

My problem is when I add formula in text box like =B2, under scenario 1, the answer is correct no matter I select which country in slicer. However, for scenario 2, when i did not use filter, the total of Product A Sales amount should refer to B6 instead of B2. So, i got incorrect answer.

May i ask your advice in this case? Thanks a lot

Regards
Lawrence

Country	Product A Sales	Product B Sales
AU	100	40
JP	80	60
HK	60	80
KR	40	100
Total	280	280

Excel can't open file from hyperlink after upgrade windows11

DavidTANG — Sun, 17 May 2026 19:30:40 GMT

Hello,

I met a strang issue.

After upgrade system from w10 to w11, my excel can't open linkage file which was working normal before.

Tested file and folder which located on local and SMB was not working, after click the hyper-lnik nothing happen and no pop-up just turn to mouse to loading icon then nothing happen how can I check which part cause this issue?

Also tested on word have same issue.

Further, url was working normal.

Excel Power Query: editing unloaded query refreshes dependent queries

ajlind25 — Sat, 16 May 2026 13:15:53 GMT

Hi all

There has been a change between version 2601 and 2602 on the Current channel which means that when editing a query in Power Query it will trigger a refresh for all dependent queries. This makes Power Query unusable for any complex queries or large data extraction.

Take the below example M code. SourceQuery is not loaded to the workbook or data model. LoadedQuery references SourceQuery and is loaded to the workbook.

Query: SourceQuery

let

Source = #table({"Col1"},{{"x"}})

Source

Query: LoadedQuery

let

Source = SourceQuery

Source

In version 2601 and previous versions, you could edit SourceQuery, 'close and load' Power Query and save your workbook almost instantly because no data was refreshed. In 2602 and later versions editing SourceQuery forces a refresh of LoadedQuery which can be very time consuming but more frustrating for all my complex/larger workbooks it triggers lots of refreshes which never finish and result in my having to end task on Excel (note: refreshing these queries takes less than 10 seconds). Doing an end task on all the mashup containers does not allow me to regain control of the Excel workbook. This means all changes to the workbook are lost. I have had to resort to using a VSCode extension to edit my Power Query without crashing Excel.

The builds I have tested are 2601 19628.20166 and 2602 19725.20190.

Are there any plans to rollback this change?

Other than rolling back my Outlook version or using VSCode are there any methods to edit my queries without crashing Excel?

It would also be helpful to understand why such a large change in functionality was not captured in the release notes. Per the release notes there were no changes to Excel in 2602, not even 'various fixes to functionality and performance'.

Many thanks, Andrew

Data from a web page - error is "Access to the resource is forbidden."

Drane21 — Sat, 16 May 2026 08:16:39 GMT

Details: "Access to the resource is forbidden.", can anyone let me know how to resolve this error

Stop Excel auto formating

Brett1 — Fri, 15 May 2026 21:13:29 GMT

How do I stop Excel from automatically formatting cells? I have three columns. The first column is formatted currency with fill. The second is text. The third is general. When tying a number in the general column Excel automatically formats the cell to currency with a fill matching the first column. How can I stop Excel from automatically changing the formatting of the cells in the third column?

locked out something is wrong

1967dunebuggy — Fri, 15 May 2026 14:46:07 GMT

locked out

something is wrong when i go to insert in to a cell it won;t let me and jumps to another cell but still cannot do an insert

Countif problem

Noyman — Fri, 15 May 2026 12:38:53 GMT

Hi - could someone advise please?

I have a workbook and am trying count the number of "Apples" in Col F, using $F$3:$F$81,"Apples" as the second criteria range and criteria. The first range and criteria is column B "Column1" is has mmm-yy and goes back a couple of years from Apr-24. I have tried COUNTIFS($B$3:$B$81,[@Column1],$f$3:$f$18,"Apples". Just doesnt seem to want to work. Any ideas? perhaps mmm-yy is an issue? thanks Noyman

Excel Functions

sethubalaji — Fri, 15 May 2026 11:28:02 GMT

Find attached a matrix, and picture below

I select Rare (0.5) in a cell and Modera (8) in another cell, i want function to return in a cell from matrix above. Like A (4) with green filled in. My inputs are Rare (0.5) and Moderate (8) and i want output cell to be Green filled in A (0.5) picked from matrix above. Like wise if the input cells are Possible (2) and Critical (50) then the output will be D (100) filled with light orange. I tried Index and Match function but failed.

I can send the excel if you want.

Please let me know what is the right function i have to use. Send the solution to mailto:email address removed for privacy reasons or email address removed for privacy reasons

Regards

How to split Excel file into multiple files?

kylen955 — Fri, 15 May 2026 05:12:57 GMT

I’m facing an issue with a very large Excel workbook and need some help. The file contains thousands of rows of data, and now it has become extremely slow to open, edit, and share through email. Sometimes Excel even freezes while working on it. Because of this, I want to split the Excel file into multiple smaller files, but I’m not sure how to do it properly without losing formatting or data.

I tried manually copying rows into separate files, but it is taking too much time and there are chances of missing important records. I also searched online for solutions, but most methods seem complicated or only work for small datasets.

This Excel file is very important for my office work, and I need a reliable way to divide it into multiple files based on rows or column values. If anyone knows an easy method, VBA solution, or any trustworthy tool that can split Excel files automatically, please share the steps. Any help would be greatly appreciated!

A little help on text grouping delimited by nulls in a data set in power query

Dan_TWE — Thu, 14 May 2026 19:52:01 GMT

A have a column in power query:

null
line of text
line of text
line of text
null

The number of rows with lines of text can vary, but each "text block" is delimited by column entries at the top and bottom that contain 'null'.

Ultimately, I would like to take each text block and populate a single cell with it. Probably in a new column in an excel table. Can I get a nudge in the right direction?

How to remove floating Copilot icon?

rcrafts — Thu, 14 May 2026 17:36:54 GMT

How do I remove this from Excel? There's no option to remove it in Excel and our IT Department can't find the setting on the Office 365 portal. I do not want to "dock" it, I want in permanently removed.

Problem: Excel opened the first file quickly, but the second and subsequent files very slowly

ENRIC121212 — Thu, 14 May 2026 07:59:26 GMT

Problem:
Excel opened the first file quickly, but the second and subsequent files opened very slowly when launched externally (double click from Explorer or using excel.exe "file.xlsx").

Environment:

Windows 11 Pro 25H2
Office 2021 Pro Plus
Clean Windows installation
Clean Office installation
Modern hardware (Intel 13th gen + NVIDIA RTX)

Symptoms:

First Excel file opened fast
Second and subsequent files became progressively slower
Opening files from inside Excel was always fast
Drag & drop from Explorer into an existing Excel window was fast
Word did NOT have the problem
Reinstalling Office did not solve it
Disabling Defender, Protected View, StartAllBack etc. did not solve it

Cause:
Registry DDE handler related to Excel shell open behavior.

Registry key:
HKEY_CLASSES_ROOT\Excel.Sheet.12\shell\Open\ddeexec

The (Default) value was empty.

Fix:
Set (Default) to:

[open("%1")]

Then reboot Windows.

After that:

multiple Explorer launches became instant again
no more progressive slowdown
Excel external opening worked normally again

Important note:
On other healthy machines the value may still appear empty, so the fix may actually force Windows/Excel to rebuild or refresh the COM/DDE association/cache rather than the value itself being universally required.

Line chart dynamic scaling of Y-axis

Chef2000 — Thu, 14 May 2026 07:18:40 GMT

Micro$oft, please, please, pretty please with sugar on top, END the ensh*ttification, and get the Y-axis of a line chart to dyanmically scale with the data range that is being plotted. The range defaults to 0 as the lowest Y-value. The only way to change it, is to manually set the ax range, or use a work-around involving VBA. So... NO! This is crazy!

It should be easy as apple pie for you lot to add an option to dynamically scale the Y-range in the chart, but I guess you are far too busy pushing people onto your crappy AI, which you can really stick some place the sun doesn't shine.

Thank you.

XIRR

gdegaude — Wed, 13 May 2026 17:01:08 GMT

I saw a tape from Excel University's trainer Jeff Lenning. I copied his data for irregular timing, but I get completely different results:

He gets 9.60%, I get 0.095736495.

Is the excel version a factor?

Any other explanation?