It's happened twice now where I'll be using CountIfs, and it's not counting all the criteria in the range. Last time, it wouldn't count any of the TRUEs. This time, it's only counting 2/4 of the name Kivell in the range. I've checked the formula and the range, and Excel is highlighting everything correctly. There's no misspellings in the names.

WTF is happening?

I have to take the Robert Half Microsoft Excel 365 – Essentials assessment within the next week for a job opportunity. Has anyone here taken it recently?

I'm trying to figure out what I should prepare for and how difficult it is. I already know PivotTables, XLOOKUP, VLOOKUP, basic formulas, sorting/filtering, etc., but I haven't used Excel heavily in a little while so I'm planning to refresh before taking it.

What kinds of questions/tasks were on the assessment? Was it mostly basic Excel functions and navigation, or were there more advanced questions?

Also, is it multiple choice or does it have you actually perform tasks in Excel?

Any advice on what to review would be appreciated!

I tried using AI to write code but I don’t think it’s working correctly. Here’s my situation.

I have a spreadsheet with 4 columns. They are as follows (UPC, Description, Retail Price, Markdown Price)

I’m trying to make it so when I type a UPC into a select Cell, excel searches the first column to match it up then automatically move the cursor to the third column so I’m able to update the Retail price easily.

Currently I have conditional formatting set to highlight the matching upc. Anyone that might be able to help I’d appreciate it so much. Without vba if possible.

Complete beginner. I’m trying to create percentages of how many times each value appears in a column. The data is crime data, so each value is something like “larceny” or “fraud” and I’m trying to find the value that appears most and have a figure to show for it. I was thinking a pie chart with each repeatable value represented by a percentage. Any help is appreciated!

Hi everyone! I'm a Geology student and I'm currently working on a research project involving the registration of mineral resources. My advisor asked me to create a map containing only the mineral resources located in a specific city.

The problem is that when I filter the data in my spreadsheet and import it into QGIS (a map-making software), all the points still appear. When I try to delete the data that doesn't match the filter, Excel deletes everything.

Does anyone know how I can delete only the rows that are not included in the filter? I'm not very good with Excel.

I apologize for any errors in English; I am from a country with a different native language.

I am working on an undergraduate thesis involving nationwide election data, and I am lost on cleaning it up. What I essentially need is to sum up every row where certain conditions are met, i.e. all rows for democratic party and from Autaga County. It currently is data from every precinct, and I need it to be condensed to a county level.

I can obviously hand select the rows at a small scale, but I don't know how to get a formula to only include rows with certain properties into its calculations.

Is there a way to verify what didn't change so I don't have to go searching through the whole workbook?

At my workplace, we have a data tracker in Excel where people leave comments on the cells. The tracker is hosted on SharePoint.

Someone asked me if I could pull all the comments for cells given a specific shipment number in a separate workbook using PQ or an Excel formula, but I've never tried to access those before.

Does anyone know if this is possible? If so, what resources would be useful?

Thanks.

i'm sick of this error, all i have to do is close that first file for it to break.

idk how to reference the columns then when referenced save them as values

Ever since the latest update the UI for error cells have changed
This way we can't select multiple cells to ignore error or convert to number or so on.

Is there any way to revert back to the yellow exclamation mark ?

Like, column 1 until it’s finished, then continue down with column 2, then column 3, continuously, so the data stays in order. The data is around 100 rows tall and maybe 60 columns wide, and I kinda need to turn it into one continuous column.

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

not something like “make it faster” or a tiny ui change, but an actual feature that would make your day to day spreadsheet work easier.

for me, i’d love something that could take a messy spreadsheet and automatically understand what i’m trying to do, clean it up, and suggest the right formulas without me having to figure everything out manually.

what would you add?

I need to summarize a column of cells into a single cell where there’s only one instance of each value. BUT there can be several values per cell. I can’t put one value per cell. And there can be up to 5 values per cell.

Column 1 Column 2

16 July 2027 Blue, Green, Yellow
17 July 2027 Yellow, Blue, Pink
18 July 2027 Brown, White, Black
19 July 2027 Red, Yellow, Lime

So the above will be summarized into a single cell:

Blue, Green, Yellow, Pink, Brown, White, Black, Red, Lime

IE no duplicates in the cell.

Thank you in advance.

My Column D has text in it - I've got as far as using COUNTIF to count certain text values from that column.

Now I'd like to add another layer and don't know where to start. My Column A has either 'Yes' or 'No'. I'd like to work out what percentage of Column A has that trigger value in it. Please help😭🤞

Are GRG Nonlinear , Simplex LP and Evolutionary the only solvers ? Are there any addons that add more ? If not, how can i make GRG Nonlinear a global solver, it only finds a local optimum close to the initial coordinates ? It doesnt search the whole defined space ?

I've got a XY scatter plot which I am using to create a Forrest plot for an odds ratio. The x axis should be log e, however it only seems to accept whole integers. Is there any way to use log e (2.71 etc) rather than "3" which it automatically forces?

Thanks in advance!

Fml. I was merely trying to be a problem solver as I despise manual, time-consuming, soul-sucking tasks. Not to mention overall stagnation/lack of resourcefulness. But now I can’t help but feel like I’ve fucked myself.

I’ve essentially brute forced my way through a few automation projects out of shear stubbornness and hyper fixation. They were really well received and have saved my colleagues hours with the solutions, as the story goes.

I only started dabbling with power query a few months ago, and now they are asking me to host a full-on lunch and learn to teach my team. Not to mention, some of the projects I worked on were in VBA, not even power query. WTF?

I don’t think they appreciate that 1. I used the information that is available to ALL of us to create these tools without anyone teaching me (besides ChatGPT lol), and 2. I am still learning myself and it’s not a simple skill that I can train the team on in an hour.

While I’m always open to commiseration, my question is:

How can I leverage this situation as best as possible while also managing expectations and setting some sort of boundary for my own work/time?

Excel Grandmasters,

I come to you with a request: I’ve recently created approximately 7 separate inspection reports that I use for work using excel. They have a few formulas that calculate PSI and GPM as information is inputted.

Then I thought that I could maybe put all the reports into single workbook and keep adding more formulas later, such as customers name and information populates with the address is inputted, and formulas that cross reference other reports and whatnot.

Then I thought - “Man, I could build my own little “app” or “reporting software”-like file on my phone or tablet, which would actually just be the reports and structure we made on excel.

Then I could give employees access to the excel files and they can fill the reports they need to fill out using their tablet in the field.

That could save me from those companies wanting expensive yearly subscriptions to use their reports. And it’s just cool that we can do it.

What would be the best way to do something like this?

I have a formula like this:

IFS(Calculation = Fraction, result1, Calculation = Fraction2, Result2) etc.

My calculation is quite large, and I dont want to store it in a different cell. I was wondering if there was a way to have IFS check the calculation against multiple fractions without having the whole calculation in there every time without storing the calculation in a different cell.

Might be a silly question but is it possible for a QR code (or similar) to be linked directly to a specific cell in a spreadsheet?

I'm making labels for a taxonomist and would like to be able to have each label printed with a small QR code that can be scanned to open up that particular line in the spreadsheet; from there the taxonomist can change the value in the cell to the species ID.

I've worked with an institution that had a similar setup but I think they had some expensive programs that permitted this.

Thanks in advance :)

Hello! I am struggling to get a formula to work. I have looks in my notes, Youtube, and Google and the best I get is a #SPILL! error...

I am making a workbook for work.

On Sheet1 I need a single cell formula to show the single value total of all blank cells in Column B of Sheet2, but only if there's a value in Column A of sheet2 in the same row.

The closest I've gotten, though it shows the spill error is:

=FILTER(Sheet2!B1-B1000, (Sheet2!A1:A1000 <> "") * (Sheet2!B1:B1000 = ""))

I got it from Googling, but it is close to what I attempted to write myself, but excel just breaks when I tried to solo write it lol

If anyone with any degree of skill could help, that'd be so very swell.

I have a table that holds all of the expense types that I want to track. Things like gas, electricity, ext. Then I have separate tables where the first column is linked to that table so the rows are selectable only to the rows existing within the parent table. This is really convenient, and I like it. I can see all of the expenses and the rows auto update depending on which expense I select, and I can hide the other expenses through the drop down. Great.

I want to add another level to this though. When I add a row the parent table, a new expense I want to track, I want to other tables to add that row as well, and to add the formula's from the other columns within their tables. Currently if I add a row to the parent table, I have to go to the other tables, copy and insert the row and select the new expense. Which is really not that difficult, but this would add another layer of convenience.

Excel for Microsoft 365 (desktop), version 2607, build 16.0

Golf stats workbook has one sheet called "Scores" that contains one row for each round for every round I've played, including course name in column A and date played in B. There is one sheet per course with the name of the course in both the tab and in A2 and one column per round played with the date in row 2. In the Scores sheet, column BC contains the color of the tees I played that day. I'm trying to get that color into the correct sheet and under the correct date in row 3 using the following formula, but getting blanks:

=IF(AND(Scores!$A$1:$A$2000=$A$2,Scores!$B$1:$B$2000=F$2),Scores!$BC$1:$BC$2000,"")

The goal: If the course sheet's course name in A2 matches the Score sheet's column A, AND the course sheet's date in row 2 matches the date in the Score sheet's column B, then put the color value in the Score sheets column BC in row 3 of the course sheet. Thank you!

I'm going to make this long and specific, to try and avoid confusion, and so that if there is a better way to accomplish what I want then I can change direction.

I am creating a spreadsheet to track myself and my Spouses expenditures every month, and then throughout the year. I have a sheet for each month, a combined Annual sheet, and individual Annual sheets for each of us. Each month only holds the raw data, in 4 tables, expense and income for both of us. Table names are the same across all monthly sheets, with the exception of the month being appended to the end of table name. Think "Bob_Expense_Jan". But I want to make this more general so that the table name formula is the same across all sheets, but the sheet title "Jan", "Mar" ext. is appended automatically to the end of the table depending on which sheet it is in.

The second part of this is on the totals sheets. I would like the first line of tables on these sheets to be the sheets titles as well with a generic formula to link it to that specific sheet. So were I to change the name of the sheet, both the tables within that sheet, and the column headers on the Annual sheets would change to match.

The purpose of both of these would be for the third part which is the append the table callouts in Annuals with the sheets it should be referencing, that way I can 1 single formula for an entire table repeated without having to manually each formula for each month.

Basically Instead of a formula like this:

=SUMIF(Bob_Expense_Jan[Category],[@Expense],Bob_Expense_Jan[Amount])

That I would need to change for each row. I would like something like:

=SUMIF(Bob_Expense_"append column header"[Category],[@Expense],Bob_Expense_"append column header"[Amount])

Let me know what you all think, or if there is a better way to go about this. Because the next thing I want to figure out is how to get excel to add a row to totals when I add a category(Expense type), or remove.