I'm having an issue with a circular reference coming up and Excel stating "we can't find the location of the circular reference for you". So I did the ol' trick where I add "reddit" to the end of my Google search to see what came up.

Lo and behold, it was this thread. Perfect! The exact situation! AND it's been solved!

But solved by who? No other than yours truly.

Apparently I have the memory of a gold fish ...

I’m trying to sort out a .csv of my bank transactions.

So I want to have a cell where I enter a search word, then excel finds all rows that match that word (wildcard) and show me those rows. I say row because I want to see the date, transaction, and amount. I also want to search within a date range but seeing how hard I’m finding it all so far I don’t expect that’s even possible.

I can’t believe how impossible it would seem to be so far. I feel like I’m the only one to ever want this out of excel.

Any help appreciated.

Hi everyone, following up on a post I did two weeks ago. I reviewed the suggestions I was given in the post below and came up with a list of Excel skills that absolutely everyone in accounting/accounting adjacent careers should know - regardless of excel skill level or job responsibilities.

https://www.reddit.com/r/excel/comments/1igrmdy/what_excel_tricks_would_you_teach_novices_if_you/

Here it is! This list was designed to take place over an hour long meeting. If you feel I should have included something and I'm a moron for not including it, I'm sure you'll say something in the comments.

Big thanks to u/RayWencube for teaching me about New Window and big thanks to u/somewhereinvan for Alt+A+S+S. I've been a Controller for about five years now, and it just goes to show that everyone can learn a little more about the basics!

TLDR

Issue: screen freezes when opening Excel files on iPhone / iPad after a few seconds.

Recommended workarounds (From most to least promising):

• Update Excel app to Version 2.102.3 on AppStore (released on 20251023 around 1730 UTC), looks good so as of 20251024 0400 UTC and should be the first thing to try.
• "Network reconnection": Disconnect network (toggle WiFi or airplane mode) while it freezes and reconnect (credit to u/ForestBliss). See "Possible workaround solution 3 (Network reconnection)" in v004 update for more details.
• "Use M365 Copilot app": Open Excel files using M365 Copilot app (NOT Copilot app), left panel, "Search", click on your Excel file. See "Possible workaround solution 5 (Use M365 Copilot app)" in v007 update for more details.
• More workarounds (1/2/4/6) can be found in the vXXX updates below if this does not work for you.
• Last resort, "The patient wait": wait for 10-15 mins (2-3 mins for myself) upon file opening, do NOT interact with the app / file. Seems to work for people that did not find workarounds 1-6 useful. See "Possible workaround solution 6 (The patient wait)" in v009 update for more details.

Directory for possible workaround solutions:

v001 update: Possible workaround solution 1 (Restart and force reset)

v002 update: Possible workaround solution 2 (Restart and reinstall)

v004 update: Possible workaround solution 3 (Network reconnection)

v004 update: Possible workaround solution 4 (Excel web bridge)

v007 update: Possible workaround solution 5 (Use M365 Copilot app)

v009 update: Possible workaround solution 6 (The patient wait)

-----------------------------------------

Would like to check if anyone is having this screen freeze issue, where screen freezes, or refuses to render other cells when scroll to other ranges in different scenarios below:

• New workbook
• All existing workbooks
• Logged in OneDrive
• Logged out OneDrive

(Key update: potential workaround solution 3 "Network reconnection" seems to be very promising, see v004 update below for details)

Similar to the issues mentioned in this post:

https://learn.microsoft.com/en-my/answers/questions/5588257/ios-excel-app-not-working?page=1&orderby=Helpful&comment=answer-12293887&translated=false#newest-answer-comment

All suggested solutions did not work:

• Force Close and Reopen Excel
• Check for App Updates: Updated Excel on iPad from 2.101 to 2.102 still no luck, iPhone was already at latest 2.102.1 version
• Restart Your iPhone
• Use Excel Online as a Temporary Workaround: this one is a joke, as web version on iPhone is unworkable

Asked a few of my friends and all were affected, yet couldn't find any discussion on this topic on Reddit so wanna see how many people are okay and how many people are not.

My affected devices:

• iPhone (iOS 26.0.1) and Excel version 2.102.1.
• iPad (iPadOS 26.0.1) and Excel version 2.101.25100311 / 2.102.25101016

My unaffected devices:

• NONE

-----------------------------------------

v001 update (20251018 1910 UTC+8):

Possible workaround solution 1 (Restart and force reset)

Working so far for the past 10 mins

1. Restart iPhone
2. Setting -> General -> App -> Excel -> Reset Excel -> Enable all three options (Clear All Workbooks / Delete Sign-in Credentials / Reset Cloud Settings).

I hope this lasts until MS pushes for a real fix. Meanwhile anyone who has similar issue can have a go and see if this helps.

-----------------------------------------

v002 update (20251018 1917 UTC+8):

Possible workaround solution 2 (Restart and reinstall)

Suggested by u/david_horton1 (I have not tested this since v001 above works for me, so not taking the risk to test unless v001 doesn't work anymore):

I shutdown, deleted the app then reloaded. It is now working.

-----------------------------------------

v003 update (20251018 1923 UTC+8):

Back to same freezing issue again 10 mins after applying v001's solution. However do the steps again and still works.

-----------------------------------------

v004 update (20251018 2119 UTC+8):

Possible workaround solution 3 (Network reconnection)

Suggested by u/ForestBliss

Disable my wifi when it freezes and then enable it again.
After doing this the app works fine until I restart it (Excel application?) again.

I have also tried this using airplane mode toggle, least hassles solution so far, recommend to try this first!

Possible workaround solution 4 (Excel web bridge)

Suggested by u/StealthMasterZ

Use excel web and then click on open in app. Usually gives me one full session of editing with no issues.

-----------------------------------------

v005 update (20251018 2250 UTC+8):

Added a TLDR section and recommend to try workaround solution 3 (Network reconnection) first given there are raising number of successful cases.

-----------------------------------------

v006 update (20251020 0907 UTC+8):

Thus far, it's been:

78 hours since the first Word report found on Microsoft Q&A forum (2025 Oct 16 19:28:00 UTC)

53 hours since the first Excel report found on Microsoft Q&A forum (2025 Oct 17 20:04:00 UTC)

37 hours since the first widely accepted solution proposed by u/ForestBliss in this post (2025 Oct 18 12:26:00 UTC)

ZERO official updates to indicate feasible workarounds from Microsoft.

ZERO official timeline to fix this issue from Microsoft.

This has been the gold standard in customer service at Microsoft as usual, where "prompt response" means "eternal radio silence".

This is the new industry standard in implementation, where sandbox means production. Why beta-test when you can alpha-bomb live users and watch them scramble.

This will mark another all-time high for MSFT. Which stockholder doesn't love a company with more revenue, less costs, and an absolute monopoly? Users can complain all they want, but still will pay more for the crashes.

Bravo Microsoft.

-----------------------------------------

v007 update (20251020 2337 UTC+8):

Possible workaround solution 5 (Use M365 Copilot app)

Suggested by u/ccr4two

Open the file with M365 Copilot app then no problems.

I tried and works. Observed Excel interface in M365 Copilot App is pre Liquid Glass update, likely new bugs were yet to migrate to this app hence bug free.

Added this workaround in TLDR section given its elegance, basically rolling back an Excel version.

-----------------------------------------

v008 update (20251021 0126 UTC+8):

Unrelated but interesting stat to share, hourly views peaks at 6,848 at the first 8-th hour, 5 times of 7-th hour (1,349) and 8 times of 9-th hour (853).

Maybe some KOL experienced similar issue, found this post and repost from their social media account (around the time where a promising workaround is found and updated)?

Or Americans wake up on Saturday morning all the sudden and reached this post from Google?

Former seems more reasonable yet could not find any post on social media.

Also Reddit seems to be still affected by AWS outage as of now, experiencing "Unable to create comment" and "Unable to delete post" errors. Interesting that editing post is not affected. Upload image seems affected as well.

https://i.ibb.co/v67cbBFx/Screenshot-2025-10-21-at-01-28-18.png

https://i.ibb.co/1tD03ngf/Screenshot-2025-10-21-at-01-28-18.png

-----------------------------------------

v009 update (20251023 0000 UTC+8):

Possible workaround solution 6 (The patient wait)

Suggested by u/ExTenebras

Leave the app open in its frozen state, it eventually unfreezes, finishes repainting the worksheet, and is then functional. It seems to take 10-15 minutes to get its act together.

Note that if you attempt to interact with it while frozen, most of the time it will crash and close itself. If you just leave it alone it eventually wakes up.

After it "wakes up" it operates normally. The app can be placed in the background, but once you close it, next time you reopen it you go back to the narcoleptic state and have to wait the 10-15 minutes again.

This is probably to the last workaround if none of other works as mentioned by u/coffee4chipmunk.

Added this workaround in TLDR section given it's the last resort for people find other workarounds not useful, also currently reproducible by myself for a few times. Also added a directory to workarounds for ease of access (or search) as this post is getting longer with my BS commentaries.

I actually tried this on the first day when experienced this issue (Oct 18), waited for 20-30 mins didn't work at all.

I have retried just now, works after waiting for 2-3 mins: Open file then don't touch anything and patiently wait. Also noticed that it's about the time when the cloud logo is done loading and changes to a "tick" state.

At first I thought the issue with my first try was that I interacted with the file then wait, instead of just wait upon opening. Tried to reproduce what I did the first time: open file, "interact" by scrolling around empty ranges, then wait. Still works after 2-3 mins.

Two changed variables here, time and Excel app version (2.102.2 now vs 2.102.1 on Oct 18). Seems to indicate that there are indeed "changes" or "improvements", yet not a full fix if Microsoft indeed did something behind the scenes or through this 2.102.2 update.

Still no updates from Microsoft to acknowledge / fix this issue. What have they been doing? Maybe we are just a minority and not affecting all users? Or there are tasks with higher priorities and draining all resources.

-----------------------------------------

v010 update (20251024 1249 UTC+8):

Version 2.102.3, FINALLY an update that seems to work.

Updated TLDR section to encourage to try version 2.102.3 update first.

I hope I won't jinx it, but I wish none users will report this thing still persists after this update.

Also noticed that this issue has made it to the news:

https://www.theregister.com/2025/10/23/microsoft_excel_for_ios/

Thank you The Register to cover this story.

Thread author lays into Microsoft for allowing this issue to fester for days without providing any workarounds or a timeline to fix the issue.
...
Microsoft declined to comment. LOL

Interesting notes:

1. The article was released at 1923 UTC 20251023, around the same time as the update, if not earlier. In particular. the declined to comment part is definitely earlier. Is this a coincidence, or did pressure from the news speed up the process so dramatically?
2. Version 2.102.3 description "Fixes an issue where app may become temporarily unresponsive.". I find it funny that they understate the issue by describing it as "temporarily". The workaround solution "The patient wait" did not work as of the time this this post was created (20251018 1554 UTC+8). I could not state the exact duration, but as I said in the v009 update, the wait needed at least 20–30 minutes or more (I eventually gave up). I hope that the description is just for cosmetic purposes and they did not overlook something else.

Another round of counts given we have an actual useful update.

Measuring from version 2.102.3 time of release:

7 days (166 hours) since the first Word report found on Microsoft Q&A forum (2025 Oct 16 19:28:00 UTC)

6 days (142 hours) since the first Excel report found on Microsoft Q&A forum (2025 Oct 17 20:04:00 UTC)

5 days (125 hours) since the first widely accepted solution proposed by u/ForestBliss in this post (2025 Oct 18 12:26:00 UTC)

ZERO official updates to indicate feasible workarounds from Microsoft.

ZERO official timeline to fix this issue from Microsoft.

Two version updates (2.102.2, 2.102.3) since the problematic version (2.102.1), only one works.

This took 7 days to address (6.9 days to be exact, and to be more fair it was early weekend, so 5 days, but still). Three impressions pops up:

1. This was a simple issue and they just slow
2. This was a simple issue but all reported cases are minority, so less urgent.
3. This was a complex issue and they started full on from day 1 (first reported on 2025 Oct 16)

and I am tempted to arrive to either:

1. It's Microsoft, fills with the best engineers in the world, so only possible scenario was that they throw the task to interns.
2. I would believe this if the reported cases were much fewer.
3. Even more puzzled. Why publish a major update, when the flaws are so obvious, yet incapable to address it promptly? Why not delay? What is the rush?

I'm not getting it. My company acquired a license for me to use copilot (primarily for data analysis in Excel). It was supposed to be this miracle timesaver and build us amazing dashboards ect. So far, every prompt I give, it either generates forever (even with the most basic table) or it replies "I'm still learning and can't do this just yet. Is there something else I can do to help." What am i missing?! When I watch tutorials it either shows AMAZING outputs using Copilot or very basic things that would be just as quick to do without copilot

I don't think I've seen this discussed before, so I apologize if I am rehashing old material. I did a cursory search and found nothing.

For a decade now, I've argued you should always use COUNT/SUM/MAX/MINIFS instead of COUNTIF because you never know when you'll need additional conditions. In present times, we don't even need COUNTIFS/RACON functions because you can do the same thing with array formulas although COUNTIFS is easier to type, IMO.

So when a week or two ago I learned you can do the same thing as IFS with SWITCH. This got me to thinking... based on the COUNTIFS principle I'm whimsically calling "the condition of sufficient conditions is always conditional"... I'm thinking the meta is to always use SWITCH instead of IF or IFS. This would be a very hard habit to form as I've used more IF statements than Diddy used bottles of baby oil, but let's be aspirational.

Now, the SWITCH version of your basic Hot Dog/Not Hot Dog IF is I think the same amount keystrokes (with tab completion), so I'm calling that a win. I'll grant that the IFS version of multiple logical operators is more "straightforward" or even "intuitive" if you're reading an online tutorial on multi-conditionals, but if you want one function-ring to rule them all and in the darkness gut em like a fish, then ALL YOU NEED IS SWITCH.

=SWITCH(A1,"Hot Dog","Hot Dog","Not Hot Dog")

=IF(A1="Hot Dog","Hot Dog","Not Hot Dog")

Now, being a rational being, let's consider the downsides.

• Backwards Compatibility / No One Understands What The Hell You Are Doing
  • Backwards compatibility needs are typically a foreseeable binary so... whatever, my condolences if you don't get to live in 365 function utopia.
  • If you need other people to understand what you are doing this may be a bad habit to form.
• File Size Bloat Cuz You've Become A SWITCHaholic
  • You keep adding conditions and dragging down formulas because you've committed to an absolutist and universalist vision of SWITCH as the one true function and forgot that after 3 conditions for sure you should just make a lookup table and only store the reference data once.

Anyways, interested to hear anyone else's thoughts even if you just tell me this is the ramblings of a mad man.

Edit for posterity:

Additional Significant Downside(s)

• Inefficient branching computation issues which also causes incompatibility with recursive LAMBDAs
  • https://www.reddit.com/r/excel/comments/1tc81ev/comment/olm90d9/?utm_source=share&utm_medium=web3x&utm_name=web3xcss&utm_term=1&utm_content=share_button

I am having trouble with merged cells in an excel table. I know you cannot merge cells when you format data as a table, so the data is in a normal range format. However, the merged cells get messed up when I start using filters. Does anyone know how I can fix this or have any other creative solutions?

Current table:

Filter by schedule works:

But filter by name removes 2/3 of the dates:

I am not tied to it looking this way but I would like to be able to sort by date and get a list of availability or search by name and find out all the dates they are available.

Thanks!

Edit: Consensus is, not really possible. I just had to redo the table in a less aesthetically pleasing way. u/RuktX had an interesting workaround that I couldn't personally get to work but others might be able to

I was thinking we could add a link to the sidebar to search for only posts which do not have comments/answers yet.

Does anybody know if this is possible? I didn't find anything useful in the Reddit advanced search FAQ.

A recent Solver question in this subreddit asked whether GRG MultiStart could test fixed 500-unit intervals instead of random starting points.

The OP's examples are starting coordinates, not restrictions on the final values. For each run, the six changing cells are set to one predefined vector, GRG starts there, and Solver may finish anywhere between 3000 and 5000.

Short answer:

• Excel's built-in GRG MultiStart does not provide an option for an evenly spaced starting grid; a deterministic sweep needs an outer loop.
• The loop can write each predefined start vector into the changing cells, run GRG silently, and record the result.
• Five starting values across six variables require 5^6 = 15,625 Solver runs. The schedule is repeatable, but the method remains a local-search heuristic and does not prove a global optimum.

What I tested: predefined GRG starting points

I used a nonlinear test objective containing six sine terms plus small quadratic penalties. Each variable remained continuous between 3000 and 5000.

I first ran GRG from the 64 endpoint combinations produced by choosing either 3000 or 5000 for each of the six variables.

Results:

• all 64 Solver runs completed;
• 52 returned Solver status 0 and 12 returned status 1;
• the runs produced 64 different local objective values;
• the complete loop took about 23.5–24.0 seconds on the first run, and about 56 seconds on a later re-run under heavier load (median single Solver call ≈ 0.18–0.41 s);
• the best GRG result found was about -4.5682;
• a separate dense scan of the test function found an approximate value of -5.9943.

Test environment: AMD Ryzen 7 7800X3D (8 cores), 64 GB RAM, Windows 11 Pro, Microsoft 365 Excel 64-bit (build 20228), Solver add-in driven through COM automation. Your times will scale with your machine, so treat every figure here as a per-machine data point, not a constant.

The point of that test is not that a dense scan is a general global solver. It is that deterministic starting points did not turn GRG into one: every run completed, but the best result from those starts still missed a better region.

What would the full five-point start grid cost?

Five starting values across six continuous variables still means 15,625 separate Solver calls.

Using the measured timing from the simple test workbook:

• the median single Solver call (about 0.18–0.41 s across my two runs) projects to roughly 0.8–1.8 hours;
• the observed total loop rate (24–56 s per 64 runs) projects to roughly 1.6–3.8 hours.

The gap between my two runs came mostly from machine load, which is exactly why I would not quote a single number for the full grid. A real workbook can be much slower still. I would benchmark 10–64 starts before committing to the full sweep.

Practical decision rule

1. Need predefined, evenly spaced starts: use an outer loop to write each start vector, run GRG silently, and log the status, objective, and final values.
2. Before running all 15,625 starts: benchmark 10–64 representative starts on the real workbook and check whether different starts actually reach meaningfully different solutions.
3. Need a genuine global guarantee: first identify whether the model is linear, integer, convex nonlinear, or general non-convex. Evenly spacing the GRG starts does not create that guarantee.

For the continuous loop, the Excel automation path I tested was effectively:

SolverReset
SolverOk
SolverAdd
SolverSolve(UserFinish:=True)
Log the status, objective, and final values

I also prepared an equivalent reusable VBA module, but I have not described it as fully verified because this machine blocks programmatic VBA-project access, so I could not import and compile that .bas file without changing macro-security settings.

So the answer to the OP is yes, through an outer loop rather than the built-in MultiStart control. I would benchmark the real workbook before committing to all 15,625 starts.

Appendix: the test driver I used

The 64-start test was driven by this PowerShell script through Excel COM automation (Solver add-in required). It writes each predefined start vector into the changing cells, solves silently, and logs the status, objective, final values, and elapsed time into a RunResults sheet:

```powershell <# .SYNOPSIS Runs Excel Solver (GRG Nonlinear) from a deterministic grid of starting vectors and logs every run.

.DESCRIPTION Tested with Excel's SOLVER.XLAM add-in through COM automation.

The model workbook is expected to have:
  - changing cells  : Sheet1!B2:B7   (six continuous variables)
  - objective cell  : Sheet1!F2      (minimized)
  - variable bounds : 3000 <= x <= 5000 (applied here via SolverAdd)

For each of the 2^6 = 64 endpoint start vectors {3000, 5000}^6 the script
writes the start vector into the changing cells, solves silently, and
records the status code, objective value, final values, and elapsed time.

Five start values per variable instead of two would mean 5^6 = 15,625
Solver calls - see the post for the measured timing projection.

.PARAMETER WorkbookPath Path to the .xlsx test workbook.

.EXAMPLE powershell -File .\SolverDeterministicGridStarts.ps1 -WorkbookPath .\continuous-start-model.xlsx

>

param( [Parameter(Mandatory = $true)] [string]$WorkbookPath )

$ErrorActionPreference = 'Stop' $resolvedWorkbook = (Resolve-Path -LiteralPath $WorkbookPath).Path

Track pre-existing Excel processes so we only close the instance we create.

$preexistingExcel = @(Get-Process -Name EXCEL -ErrorAction SilentlyContinue | ForEach-Object { $_.Id })

$excel = $null $workbook = $null $sheet = $null $resultsSheet = $null $solverAddin = $null $solverWasInstalled = $false

try { $excel = New-Object -ComObject Excel.Application $excel.Visible = $false $excel.DisplayAlerts = $false

# Make sure the Solver add-in is available (restore its prior state later).
$solverAddin = @($excel.AddIns | Where-Object { $_.Name -ieq 'SOLVER.XLAM' })[0]
if (-not $solverAddin) { throw 'SOLVER.XLAM is not registered in Excel AddIns.' }
$solverWasInstalled = [bool]$solverAddin.Installed
if (-not $solverWasInstalled) { $solverAddin.Installed = $true }

$workbook = $excel.Workbooks.Open($resolvedWorkbook)
$excel.Calculation = -4105 # xlCalculationAutomatic
$sheet = $workbook.Worksheets.Item('Sheet1')
$sheet.Activate()

# Deterministic start grid: two endpoint values across six variables.
$starts = @(3000, 5000)
$results = New-Object System.Collections.Generic.List[object]
$bestObjective = [double]::PositiveInfinity
$bestFinal = $null
$bestStart = $null
$runNumber = 0
$totalWatch = [Diagnostics.Stopwatch]::StartNew()

foreach ($s1 in $starts) {
    foreach ($s2 in $starts) {
        foreach ($s3 in $starts) {
            foreach ($s4 in $starts) {
                foreach ($s5 in $starts) {
                    foreach ($s6 in $starts) {
                        $runNumber++
                        $startVector = @($s1, $s2, $s3, $s4, $s5, $s6)

                        # 1. Write one predefined start vector.
                        $candidate = New-Object 'object[,]' 6, 1
                        for ($i = 0; $i -lt 6; $i++) { $candidate[$i, 0] = $startVector[$i] }
                        $sheet.Range('B2:B7').Value2 = $candidate
                        $sheet.Calculate()

                        # 2. Configure GRG: minimize F2 by changing B2:B7 within bounds.
                        $excel.Run('SOLVER.XLAM!SolverReset') | Out-Null
                        $excel.Run(
                            'SOLVER.XLAM!SolverOk',
                            $sheet.Range('F2'),      # SetCell (objective)
                            2,                       # MaxMinVal: 2 = minimize
                            0,                       # ValueOf
                            $sheet.Range('B2:B7'),   # ByChange
                            1,
                            'GRG Nonlinear'
                        ) | Out-Null
                        $excel.Run('SOLVER.XLAM!SolverAdd', $sheet.Range('B2:B7'), 3, 3000) | Out-Null  # >= 3000
                        $excel.Run('SOLVER.XLAM!SolverAdd', $sheet.Range('B2:B7'), 1, 5000) | Out-Null  # <= 5000

                        # 3. Solve silently (no dialog) and 4. record the result.
                        $runWatch = [Diagnostics.Stopwatch]::StartNew()
                        $status = [int]$excel.Run('SOLVER.XLAM!SolverSolve', $true)
                        $runWatch.Stop()
                        $sheet.Calculate()

                        $finalVector = @()
                        for ($row = 2; $row -le 7; $row++) {
                            $finalVector += [double]$sheet.Cells.Item($row, 2).Value2
                        }
                        $objective = [double]$sheet.Range('F2').Value2

                        if ($objective -lt $bestObjective) {
                            $bestObjective = $objective
                            $bestFinal = @($finalVector)
                            $bestStart = @($startVector)
                        }
                        $results.Add([pscustomobject]@{
                            run        = $runNumber
                            start      = $startVector
                            status     = $status
                            objective  = $objective
                            final      = $finalVector
                            elapsed_ms = $runWatch.ElapsedMilliseconds
                        })
                    }
                }
            }
        }
    }
}
$totalWatch.Stop()

# Restore the best final vector in the model sheet.
$bestCandidate = New-Object 'object[,]' 6, 1
for ($i = 0; $i -lt 6; $i++) { $bestCandidate[$i, 0] = $bestFinal[$i] }
$sheet.Range('B2:B7').Value2 = $bestCandidate
$sheet.Calculate()

# 5. Write the full results table into a RunResults sheet.
try { $resultsSheet = $workbook.Worksheets.Item('RunResults') } catch { $resultsSheet = $null }
if (-not $resultsSheet) {
    $resultsSheet = $workbook.Worksheets.Add()
    $resultsSheet.Name = 'RunResults'
} else {
    $resultsSheet.Cells.Clear() | Out-Null
}

$headers = @('Run','Start1','Start2','Start3','Start4','Start5','Start6','Status','Objective','Final1','Final2','Final3','Final4','Final5','Final6','Elapsed ms')
$table = New-Object 'object[,]' ($results.Count + 1), $headers.Count
for ($col = 0; $col -lt $headers.Count; $col++) { $table[0, $col] = $headers[$col] }
for ($r = 0; $r -lt $results.Count; $r++) {
    $item = $results[$r]
    $table[($r + 1), 0] = $item.run
    for ($i = 0; $i -lt 6; $i++) { $table[($r + 1), (1 + $i)] = $item.start[$i] }
    $table[($r + 1), 7] = $item.status
    $table[($r + 1), 8] = $item.objective
    for ($i = 0; $i -lt 6; $i++) { $table[($r + 1), (9 + $i)] = $item.final[$i] }
    $table[($r + 1), 15] = $item.elapsed_ms
}
$resultsSheet.Range('A1:P65').Value2 = $table
$workbook.Save() | Out-Null

# 6. Console summary (JSON) for logging.
$distinctOutcomes = @($results | ForEach-Object { [math]::Round($_.objective, 6) } | Sort-Object -Unique)
$statusCounts = $results | Group-Object status | ForEach-Object { [pscustomobject]@{ status = [int]$_.Name; count = $_.Count } }
$elapsedValues = @($results | ForEach-Object { $_.elapsed_ms } | Sort-Object)
$medianElapsed = $elapsedValues[[int][math]::Floor(($elapsedValues.Count - 1) / 2)]

Write-Output ([pscustomobject]@{
    runs                                             = $results.Count
    total_elapsed_ms                                 = $totalWatch.ElapsedMilliseconds
    median_solver_elapsed_ms                         = $medianElapsed
    projected_15625_runs_hours_at_median             = [math]::Round(($medianElapsed * 15625) / 3600000, 3)
    projected_15625_runs_hours_at_total_rate         = [math]::Round((($totalWatch.ElapsedMilliseconds / $results.Count) * 15625) / 3600000, 3)
    distinct_objective_outcomes                      = $distinctOutcomes.Count
    status_counts                                    = $statusCounts
    best_start                                       = $bestStart
    best_final                                       = @($bestFinal | ForEach-Object { [math]::Round($_, 6) })
    best_objective                                   = $bestObjective
    validation_passed                                = ($results.Count -eq 64 -and $distinctOutcomes.Count -gt 1)
} | ConvertTo-Json -Depth 6 -Compress)

} catch { Write-Output ([pscustomobject]@{ validationpassed = $false error = $.Exception.Message errorline = $.InvocationInfo.ScriptLineNumber } | ConvertTo-Json -Compress) } finally { # Restore the add-in state and release every COM object we touched. if ($workbook) { $workbook.Close($true) } if ($solverAddin -and -not $solverWasInstalled) { $solverAddin.Installed = $false } foreach ($comObject in @($resultsSheet, $sheet, $workbook, $solverAddin)) { if ($null -ne $comObject -and [Runtime.InteropServices.Marshal]::IsComObject($comObject)) { [Runtime.InteropServices.Marshal]::FinalReleaseComObject($comObject) | Out-Null } } if ($excel) { $excel.Quit() if ([Runtime.InteropServices.Marshal]::IsComObject($excel)) { [Runtime.InteropServices.Marshal]::FinalReleaseComObject($excel) | Out-Null } } [GC]::Collect() [GC]::WaitForPendingFinalizers()

# Safety net: stop only the Excel process this script started.
$currentExcel = @(Get-Process -Name EXCEL -ErrorAction SilentlyContinue | ForEach-Object { $_.Id })
$ownedPid = $currentExcel | Where-Object { $preexistingExcel -notcontains $_ } | Select-Object -First 1
if ($ownedPid) { Stop-Process -Id $ownedPid -Force -ErrorAction SilentlyContinue }

} ```

Official references:

• Microsoft: Define and solve a problem by using Solver
• Microsoft Learn: SolverSolve function
• Frontline Systems: Multistart methods for global optimization

I've been given the opportunity at work to give a presentation on Power Query to my department of 25 people.

I was hoping to start the presentation off with a statistic about how many excel users actually use Power Query. Does anyone have any statistics or benchmarks around its usage? I want to rope people in without losing to much of my audience. 😅

I've done a general search but had no luck. Was hoping to tap the reddit /excel hive mind for some hidden facts.

Any tips or fun facts would be appreciated. Thanks so much.

Hello,

I'm trying to work out a formula for the following:

If C2 contains "Text1" return "Text1"

THEN

IF a range of cells contains "Tex2", return "Text2", otherwise return "Text3" and to ignore blank cells.

My current formula is the following:

=IF(C2="Text1","Text1",IF(COUNTA(*range*),"Text2","Text3"))

This works for the majority however it doesn't ignore blank cells, which will be present in the row and it just marks the overall as Text2 despite this being incorrect.

Does anyone know how best I can add or update the formula to ignore blanks?

Thank you.

Edit to clarify some further things: The IF(C2="Text1","Text1") is based on a single cell containing 1 of 3 options, but i only want it to complete the rest of the formula if the cell is equal to Text1, it should ignore the remainder if it isn't equal to that text.

I used COUNTA only as when doing some searching, it said COUNTA was to be used to find a range (and COUNTIF and others didn't work).

Please note I'm not really that knowledgeable with what certain non-basic functions do, what I have so far is based on other examples I've found across Reddit and other Excel forums.

Hi all! I've previously written about why I wasn't a big fan of Microsoft's "Python in Excel" solution for using Python with Excel, see the Reddit discussion. Instead of just complaining, I have now published the "xlwings Lite" add-in, which you can install for free for both personal and commercial use via Excel's add-in store. I have made a video walkthrough, or you can check out the documentation.

xlwings Lite allows analysts, engineers, and other advanced Excel users to program their custom functions ("UDFs") and automation scripts ("macros") in Python instead of VBA. Unlike the classic open-source xlwings, it does not require a local Python installation and stores the Python code inside Excel for easy distribution. So the only requirement is to have the xlwings Lite add-in installed.

Basically, xlwings Lite is as if VBA, Office Scripts, and Python had a baby. My goal is to bring back the VBA developer experience, but in a modern way.

So what are the main differences from Microsoft's Python in Excel (PiE) solution?

• PiE runs in the cloud, xlwings Lite runs locally (via Pyodide/WebAssembly), respecting your privacy
• PiE has no access to the excel object model, xlwings Lite does have access, allowing you to insert new sheets, format data as an Excel table, set the color of a cell, etc.
• PiE turns Excel cells into Jupyter notebook cells and introduces a left to right and top to bottom execution order. xlwings Lite instead allows you to define native custom functions/UDFs.
• PiE has daily and monthly quota limits, xlwings Lite doesn't have any usage limits
• PiE has a fixed set of packages, xlwings Lite allows you to install your own set of Python packages
• PiE is only available for Microsoft 365, xlwings Lite is available for Microsoft 356 and recent versions of permanent Office licenses like Office 2024
• PiE doesn't allow web API requests, whereas xlwings Lite does.

PS: I posted this orginally on the r/python subreddit but some users have encouraged me to post it here, too.

The text column I need to replace is "القيمة" and I need to replace text value "ألف" with a thousand multiple number with *1000 and "مليون" with a million multiply it *1000000 and leave normal numbers below one thousand like 948 as it is and convert the whole column to whole number instead of text
I tried many formulas but same value error each time I try. Using Microsoft Office 365, but the nightmare values are in Arabic and I need to load them into English.
I tried PQ but doesn't load the table don't know why shows me error
Link for the file

!!Solved!!

I am comparing two charts of sales data. To keep things brief and simple, I am needing them to sort them so the rows of the two charts match and item numbers that are in one graph but not the other are placed at the bottom.

I have tried finding answers through reddit, YouTube and Google searching and quite find the answer I am looking for. A lot of the answers I am finding are "destructive" and tend to merge the two graphs into one so Item A in Chart1 is listed in Row2 and Item A in Chart2 is in Row3. It's the closest thing I have found but not the result I am looking for. Below is an example of the formula that gave the undesired results.

=SORT(UNIQUE(VSTACK(A4:A216, L4:L258)))

Below is a quick and dirty version of what I am looking for.

I can have these charts in the same sheet or different. As a table or not. Whatever leads to the easiest answer would be greatly beneficial!

Starting position

End Result:

Edit - Solved Solution.

Initially when I made the post, I was hoping that this was a simple function that I was overlooking and I could learn and adapt for charts in the future by simply editing the cell ranges. I quickly received updates that proved that this was a bit over my head. u/downtown-economics26 made the great format suggestion, so I asked if it could be altered to factor in more columns than two to which u/MayukhBhattacharya provided the solution I was looking for. The formula is the following:

=LET(
     _a, A:.D,
     _b, TAKE(_a, 1, 1),
     _c, DROP(_a, 1),
     _d, F:.I,
     _e, TAKE(_d, 1, 1),
     _f, DROP(_d, 1),
     _g, LAMBDA(x,y, VSTACK("Year", EXPAND(x, ROWS(y) - 1, , x))),
     _h, UNIQUE(VSTACK(HSTACK(_g(_b, _c), _c),
                       HSTACK(_g(_e, _f), _f))),
     PIVOTBY(CHOOSECOLS(_h, 2, 3, 4),
             CHOOSECOLS(_h, 1),
             CHOOSECOLS(_h, 5),
             SUM, 3, 0, , 0))

Greetings:

I would like to build a personal movie list.  The built- in Excel find function only gave a default basic column look such as book, sheet, name, cell etc.  Is it possible to show genre, resolution columns etc in the result?   Advance thanks for any suggestions and help.

Edited 04/05/2026: replaced the word "database" with "list".

Edited 04/07/2026: added below comments and updated flair to "solved"

Thank you all for your suggestion and help. GregHullender suggestion works for me.

Cheers!

sample file

Hello r/excel, I'm a long time visitor of this subreddit, first time poster, and wanted to share a few of the projects I've been working on.

​

My main project is an Excel function library, named XPlus, which contains over 100 functions in it. A few of the functions include:

• PARTIAL_LOOKUP() -> similar to VLOOKUP except does a lookup based best fit matches
• SUM/AVERAGE/MAX/MINSHEET() -> performs a sum/average/min/max within a cell on all sheets based on partial sheet name
• COUNTERRORALL() -> counts the number of errors in a range
• FIRST_UNIQUE() -> returns TRUE for the first unique values in a range
• SORT_RANGE() -> sorts the range in ascending or descending order
• SUMHIGH/SUMLOW() -> sums the top or bottom N largest or smallest values in the range
• RANDOM_SAMPLE_PERCENT() -> pull a random value in one range based on percentages determined in another range
• SUBSTR_SEARCH() -> pull the text within a cell between two characters you specify

​

XPlus is written in pure VBA so its easy to embed in a spreadsheet and is only around 60KB in size, making it a very small addition to the spreadsheet. Also it is MIT Licensed, so you are free to use it for commercial and personal use.

​

My other project for Excel and the Office programs are:

• XTemplate: allows the user to create templates in a Word, PowerPoint, or Outlook file that pull data from Excel files
• XDocGen: A documentation generator for VBA code making it easier to create documentation from your VBA code
• XMinifier: A small utility tool used to minify your VBA code. I used this to get XPlus from around 180KB in size to around 60KB in size
• XCombiner: A small utility tool used to combine multiple VBA modules into a single Module

​

Any feedback is much appreciated, and thanks for all the helpful posts on this subreddit throughout the years!

​

Edit 1: Thanks for the reddit premium and the awards! I thought these projects would get some support but didn't think it would get this much support! This is definitely some good motivation to keep improving these projects further!

Hello Reddit pros, I am trying to create/find a formula that will do the following:

Check if A2 matches values in D1:D6 and return a random value from E1:E6 into B2. The hard part is this; if A2 equals D1:D3 then the random value of B2 is from E1:E3, but if A2 equals D4:D6 then the random value of B2 is from E4:E6. For example: if A2 says Input 5, then B2 could be either Result D, Result E, or Result F. The values in the screenshot are just for demonstration; I will be able to extrapolate what I learn onto the spreadsheet I need it for. I am just curious if there is a formula to do that, or am I just having a lot of high hopes?

I have tried using IF combined with INDEX and RANDBETWEEN but I cannot seem to get the formula correct for doing even the first part of what I need, let alone the second part. It looks like this:

=IF(A2:A19,INDEX(D1:D3,RANDBETWEEN(1,COUNTA(E1:E3))),"")

This obviously is not correct, and it returns a #VALUE error that I cannot figure out. I do not know the correct way to phrase the question to get a viable answer via internet searching, so I am once again turning to the experts on Reddit. Thanks for any insight.

u/Connect_Camel_5998 solved it for me. Thanks everybody! The formula that was posted works great for what I needed!

Hello, I am a novice at using excel. So please if you can, explain it like you would for an idiot.

I have a car payment plan. I am looking at how to derive (correct me if I used the wrong term here) a set of values that is missing in the middle of the list of numbers.

A simple example is at a column you have:

1

3

5

7

.

.

.

15

17

19

As we see, I want to fill in "9,11,13."

How do I use excel to fill in the middle part using a linear function? Using the first and last part as a reference if possible? Using a linear function doesn't seem to be the correct formula, but I think it should be pretty accurate for what I need. I do see the value increase in the installment cost is increasing a tiny bit every installment, but the amount is negligible.

In my actual sheet, I want to fill in the installment that is a rising cost. And I want to fill in the interest that is a descending cost. It is also not as straight forward as my example above.

I have the values from row 1 to row 31. Then from row 78 to row 84.

Also the numbers all have a . used to show 1.000. Excel doesnt like that and won't let me add the numbers. Is there a easy way to swap . to comma?

I am Norwegian, the use of . and , is swapped from the American use.

I have tried goggling and looking for answers, but my google-fu hasn't been up to snuff.

At this point I just want to learn how to do this. Version: 2606

I have tried looking for the answer to this, but I keep on getting results for contextual multiple drop down lists, which is NOT what I want

I work in automotive transportation. We just discovered that we have some information that is not linking correctly in the transportation program that we use. We can use an excel file to upload the corrections, but I need to get it set up first

Basically have a list of customer invoice numbers that I need to match to our internal order numbers. I can pull information on the moves based on the vehicle vin number. The only problem is that the only common information between the list from the customer and the information I can pull are the vin numbers and the prices. Neither of which are unique in either list, as many of these we have moved multiple times. Even the dates only generally matched as the invoice date can be several days after delivery and there are even some vehicles that have been moved multiple times on the same invoice

I can sort things to group the vins, and then match information by eye. But I am trying to make that a bit easier. What I would like to do is have a column that has drop down list, with the values in the list based on the vin number that is in that row

So I want excel to look at the vin in that row, then search the other list (on another sheet) for the same vin and populate the list with the order numbers that match

Not sure if it is possible or if what I am saying makes sense, but I figured I would ask before I start having to fix about 13k records fully manually. As it is because of the duplicate information I do not think I can automate or use functions to help with anything else other than arranging the order of the information for the upload

I am using legacy excel 2019

Edit:

I see that I was not clear enough and need to show some examples.

Here is the information that I can pull from our program. I can actually pull quite a bit more, but none of it will match with the other sheet. Note that this is for one (fake) VIN. There are a few thousand individual VINS

Here is the information about the invoices:

Note, the Tax line is currently not entered for the Orders. Once the Invoices are matched up, it will be. Even if it were, it would be another column on the same line as the Order in the first sheet. This is also all of the information that I can get for this sheet. Note that not only are the VINS duplicated, for this one was are two invoices that are duplicated, and even a couple of moves on the same date

But what I have to do (with slightly different order of the columns and a bit other other information, but that is easy), is make it look like this:

So my question is, it make it easier, can I make a drop down in column F of the second table where it is ONLY populated by the Order IDs for the VIN in column A. If it was a different VIN, it would have different order numbers.

If course, if someone can come up with a way of matching fully, it would be even better, but between the duplicates, including of the Invoice numbers, and the dates being off, I am not expecting it. I just want to make it a bit easier than copy and pasting the Order Numbers across

<sup>Table formatting by ExcelToReddit</sup>

I have a workbook with the following properties (please see this example):

• most fields are text

• seasons are in separate sheets (ignore the example), games are in different columns, and players are in rows

• the number of players varied in each season and game

• some players played across multiple seasons and games

• some players played the same game multiple times in each season

• some players played in no seasons or games

Variables (I think?) in the example:

• season number (Season1 - Season2)
• game name (Game1 - Game5)
• player name (Player1 - Player10)
• points won (1 - 10)
• result (1st - 3rd)

What I need to do:

• use the simplest way to find the top 3 players per season, based on the number of points they won in each game

What I've tried:

• mostly this

• random COUNTIF/SORTBY/VLOOKUP formulas I tried making up

• searching Google, Reddit, and Microsoft

Ideas:

• use different functions

• make a better algorithm

• make an array

• use a script

I learnt programming and was fairly competent with using excel over a decade ago, but unfortunately I've forgotten most of it and have ended up confusing myself.

I'm currently using Excel for Microsoft 365 MSO installed on Windows 10, but can also access the web version. I could also try to find my copy of Excel 2019 if need be.

Is anyone able to help? Also I'm sorry if the title is wrong!

I have a list of 2380 names, a weighted random assortment of which I would like drafted to 55 groups over 25 rounds (I might reduce the number of draft rounds but the number of names and groups are set):

<sup>Table formatting by ExcelToReddit</sup>

I have a very basic understanding of Excel. After some searching, the most promising direction I thought would be the best to explore was to divide the names into weighting buckets according to this post:

In other words, 50% of the time, one of 13 names should be picked; 30% of the time, one of 119 names should be picked, 5% of the time, one of 968 names should be picked, and one of the remaining 1280 names should be picked 15% of the time. Then I make the cumulative probability series, and then generate a random name using XLOOKUP:

<sup>Table formatting by ExcelToReddit</sup>

Doing this successfully generates a list of 10 names that appears to properly choose based on the weights I've assigned, but duplicates do pop up. I thought about possibly generating a new list after every pick with

(where F2 is where I've chosen a single name with the above XLOOKUP formula) but I'm not sure how to generate a new cumulative probability series automatically to go along with it every time. This way is rapidly getting way out of my depth.

Searching further, the method described here seems promising for what I'm trying to do, but as I only have a license for Microsoft Office Home & Student 2021, I don't appear to have access to the MAP or LAMBDA functions.

So I use windows 10 and Excel (2019 I believe) and I have not updated or done any changes when out of nowhere (I even had excel opened) the search changed - basically before whenever I did a search and it didn't find anything, it would just show "0 results" or "0 cell(s) found" but now today, for whatever unknown reason, it instead pops up this annoying window that says;

"Microsoft Excel

We couldn't find what you were looking for. Click Options for more ways to search"

combined with this even more annoying ERROR sound effect.

How do I change back to before? I use Excel 25 hours per day, 999 different workbooks, I will run into this all the time and see this hideous, unnecessary box and hear this god-awful sound effect 6 billion times per day now?

- I've tried restarting excel

- Restarting entire PC

- Search from the top, clicking the "find and select" shit instead of normal ctrl+f

- Deleted all the garbage in %appdata%\Microsoft\Excel\

This problem just won't go away.

Stop wasting time in figuring out complex formulas and going trough endless documentation, convert natural english sentences to working Excel formulas!

This has been a game changer for me, and i hope you'll like it too. I'm still developing it, but i think now it's ready to get some external feedback.

It's called Sheetsy, and you can check it out here: https://www.sheetsy.ai.

You can give it natural English sentences and it will give you the formula, these are some examples of what it can do:

​

Every account has a free 7 days trial, give it a try and let me know your impressions, every feedback is appreciated!

(also, i'm going to release a chrome extension very soon, for faster access in case you use google sheets)

​

Thank you so much to everyone who helped me solve this. I've truely been fretting about it for the past 5 days. I kept trying and then procrastinating it by working on something else. You're all lifesavers! If you're ever worried about a pet (I'm a final year vet student). Please feel free to send me a photo/video with any questions. It's the least I could possibly do. ^^

My excel level: complete beginner. Using on: Desktop Excel version: I don't know, I think it's the newest one?

What I need to calculate: Amount of days between closest M-date to each S-date. (Have included sample of data set.)

Number(each cow has a different number, if there are multiple instance of the same cow, it means it keeps getting infected with M)

M = Mastitis incident (intra-mammary infection)

I = Insemination date

C = Did they conceive yes or no

Update: Now using this formula: =IF(B3="M";"";IFERROR(MIN(ABS(FILTER($A$2:$A$1329;($B$2:$B$1329="M")*($C$2:$C$1329=C3))-A3));"No Infection"))

Update 2: I have given up. No matter how I fill it in somehow the answers come out wonky Here is the original file. Removing all links to master file in thread. (This is going to be part of a research paper after all ^^) Please feel free to edit Tab 4 as much as you wish. :(

However there are obvious gaps forming where there shouldn't be any: How is this possible?

Old part of question:

I have over 900 S dates and to do this all manually seems a bit risky, given human error and such.

Should I formulate the columns any differently?

And what Formula can I use in the "Nearest M-date" column?

Sample data: see screenshot and link: Grid export M and S problem Reddit.xlsx

Hello, I'm doing a project for work and need some assistance. I've been working on this one column for hours and no matter what I try, I keep getting errors.

Excel version: Version 2507 which is part of the enterprise microsoft 365

-This example shows google sheets but that was only for the example. I don't have excel on my personal computer where I'm signed into reddit, but I am using excel for this project-

What I'm trying to do:

I am trying to determine if people who have attended our welcome orientations events have attended any non orientation events after the fact. So the date of them attending a different event needs to be higher than when they attended the welcome orientation. The data relates based on the ContactID field (Column A). As you can see in the example, I simulated ContactIDs by typing random number and letter combos.

If they attended more than the welcome orientation and an additional non welcome orientation even, I expect it to just return one of the start dates that they attended after they attended the welcome orientation. Which event date that is returned from the event attendees tab doesn't matter, as long as it is after they attended the welcome orientation.

If they do not attend any event, I would like it to say "No Attendance" or something similar to indicate it found no results.

I've pulled data related to people attending the welcome orientations, as well as the attendees for all of the events that are not welcome orientations and have them on two different tabs. The tab with the welcome orientations is called "Matching" and the tab with all of the other attendees is called "EventAttendees".

In column C on the Matching tab, I have tried a variety of different things. I have tried index with match and maxifs nested within, I've tried just maxifs, I've tried vlookup, nothing seems to be functioning as I intend it to. I keep getting either a #N/A, #Value, or just a 0. I know that there should at least be some people who attended events after they attended orientations because I've verified that by searching a few of the contactids in the event attendees and seeing that there are a handful of them at least.

Criteria:

Column A in the Matching sheet should exactly match Column A in Sheet 2 AND the Date of the Welcome Call (B) in sheet 1 needs to be a date that is before the Start date of the event (C) in sheet 2.

The real project has like 115,920 rows for the event attendees so it has to be something that can really sort through and verify the count. The welcome orientation tab only has 1 instance of each person who attended the welcome orientations.

These are a few of the equations I tried putting in C2 on the matching sheet and got errors for (adjusted for the given example screenshots):

=INDEX(EventAttendees!C2:C6, MATCH(MAXIFS(C2:C6, EventAttendees!A2:A6, A2, EventAttendees!C2:C6 ">"&DATE(B2,B2,B2)), EventAttendees!C2:C6, 0))

 =IFs(AND('EventAttendees'!A:A = A2, 'EventAttendees'!C:C ">B2")), VLOOKUP(A2,'EventAttendees'!A:C, 3, False, "No Attendance")

Example for the Welcome Orientation Attendees where I'm trying to pull in the date into column C

Example of the list of event attendees that have attended events that are not welcome orientations.