Tutorials

This is the full tutorial catalog. The homepage is a short starter set of common cases. Each page here is one Google Sheets error value or one formula that failed in a specific way. Match the value in the cell — #N/A, #REF!, #DIV/0!, #NAME?, #VALUE!, #NUM! — or the function that failed, then open that page.

The list is grouped by error family, in the same order as the sidebar. A page can appear under more than one heading when it belongs in both. Every published tutorial is included. You do not need an account. Pages are English, against the English Google Sheets UI. Each tutorial is independent: type the recreate on a blank sheet. The site is an independent library, not a Google product.

Every page follows recreate, cause, fix. You reproduce the error on a blank spreadsheet, read what is actually wrong — and what it is not — then type a formula. There are no draft pages in this list.

#N/A

What #N/A actually means in Google Sheets#N/A means the formula found no match. Confirm with a tiny recreate, then fix the key — wrap with IFNA only when an empty result is expected.

Fix VLOOKUP #N/A in Google Sheets (spaces and numbers stored as text)VLOOKUP says #N/A even though the value is on the sheet. Recreate number vs text and a trailing space, then VALUE / TRIM — this is not a missing row, not lookup-left, and not TRUE.

Fix HLOOKUP #N/A and #REF! in Google Sheets (no match vs row index)HLOOKUP is #N/A when the key is not in the first row, and #REF! when the row index is past the range. Same errors as VLOOKUP — the third argument counts rows.

Fix #N/A after a CSV import in Google Sheets (text vs number)After File → Import, VLOOKUP is #N/A because Sheets converted CSV ids to numbers (and stripped leading zeros). Recreate the type split, then VALUE / TO_TEXT — this is not a missing row.

A long CSV id becomes 1.23E+15 and the lost digits do not come backSheets stores numbers in binary floating point. An id longer than the exact integer range rounds. Scientific notation is the display. TEXT and a later apostrophe cannot restore digits the import already dropped.

Fix FILTER #N/A in Google Sheets (no matches and mismatched range sizes)FILTER returns #N/A for two different reasons: zero matching rows, or a condition range that is not the same height as the data. Recreate both, then IFNA only the empty result — mismatch needs equal heights, not a wrapper.

Fix FILTER #VALUE! or #N/A from mismatched range sizes in Google SheetsFILTER errors when the range and the condition are different heights. Recreate three rows of data with a four-row condition — not FILTER no-match, not COUNTIFS range size.

Fix REGEXEXTRACT #N/A in Google Sheets (pattern did not match)REGEXEXTRACT is #N/A when the regex does not match, including a capturing group that captures nothing. Recreate it, then fix the pattern or wrap with IFNA — REGEXMATCH on the same miss is FALSE.

Look left of the key in Google Sheets (INDEX/MATCH or XLOOKUP)VLOOKUP returns #N/A when the name sits left of sku. Recreate the left miss, then INDEX/MATCH and XLOOKUP — this is not a missing sku, not number-vs-text, and not TRUE approximate match.

Replace VLOOKUP with XLOOKUP in Google SheetsXLOOKUP looks left of the key, defaults to exact match, and takes a missing_value instead of wrapping IFNA. Recreate the three VLOOKUP traps, then swap the formula.

Fix XLOOKUP #N/A in Google Sheets (no match vs if_not_found)XLOOKUP returns #N/A when the search key is missing. Recreate an exact miss, then missing_value / if_not_found versus wrapping IFNA — not a VLOOKUP type miss and not a left-lookup.

XLOOKUP still shows #N/A when if_not_found is left emptyAn omitted missing_value still returns #N/A. A blank string in that slot looks empty. A trailing comma is not the same as quotes. Not a bad range, and not IFERROR.

Fix MATCH #N/A in Google Sheets (no match and match type)MATCH returns #N/A when the key is missing, or when match_type 1 / -1 is used on an unsorted range. Recreate an exact miss and a wrong match_type — this page is MATCH, not XLOOKUP.

Fix XMATCH #N/A in Google SheetsXMATCH returns #N/A when the key is missing, or when search_mode 2 / -2 is used on an unsorted range. Recreate an exact miss and a binary search — not MATCH #N/A, not XLOOKUP #N/A.

Fix IFS #N/A in Google Sheets (no condition was true)IFS returns #N/A when every condition is FALSE. Recreate a three-way IFS with no matching branch, then add a final TRUE default — not IFERROR vs IFNA, not FILTER empty.

Fix SWITCH #N/A in Google Sheets (no matching case)SWITCH returns #N/A when no case matches and you omitted the default. Recreate a status SWITCH with no fallback, then add a final default argument — not IFS #N/A.

Fix GOOGLEFINANCE #N/A in Google Sheets (symbol, attribute, or market closed)GOOGLEFINANCE returns #N/A for a bad ticker, an unsupported attribute, or a market the function cannot fetch. Recreate a currency pair and a fake symbol — not a lookup miss and not a broken spreadsheet.

GOOGLEFINANCE prices are delayed up to 20 minutes, and sometimes #N/AGoogle documents a delay of up to 20 minutes. datadelay returns that lag as minutes. A broker app can show a newer print. A stale number is not a bad ticker. Not investment advice.

GOOGLEFINANCE WEEKLY returns week buckets, and MONTHLY is #N/AThe fifth argument is DAILY or WEEKLY. WEEKLY spills fewer rows than the same close window with no interval. MONTHLY is #N/A. HOURLY and a reversed window are the other page. Not investment advice.

A bare GOOGLEFINANCE ticker can be the wrong listing, or #N/A, until you add the exchange7203 is #N/A. TYO:7203 is the Tokyo listing. A symbol with no prefix can resolve to a different exchange than NYSE: or LON:. A slash in a currency pair is the other page. Not investment advice.

Fix HSTACK / VSTACK errors in Google Sheets (mismatched sizes or #N/A padding)HSTACK and VSTACK pad missing cells with #N/A when array sizes differ. Recreate a short afternoon column next to a tall morning column — not FILTER range size.

Fix SORTN #N/A or #VALUE! in Google Sheets (n too small, n invalid, or ties mode)SORTN errors when n is 0 or negative, or a ties mode is illegal; ties mode 1 can return extra rows. Recreate n=0, mode 5, and a tied heat — not SORT/UNIQUE spill.

Fix FILTER + SEARCH / REGEXMATCH contains in Google Sheets (#N/A vs wrong function)FILTER(range, SEARCH(...)) blows up because SEARCH misses are #VALUE!, not FALSE. Recreate SEARCH in FILTER vs ISNUMBER(SEARCH) vs REGEXMATCH — not FILTER no-match 999, not FIND primer alone.

Fix INDEX / MATCH two-criteria #N/A in Google SheetsOne MATCH on sku ignores region. Recreate East+101 vs a single MATCH, then INDEX with a multiplied boolean MATCH — not two-way header lookup, not XLOOKUP exact miss.

Fix two-way INDEX / MATCH #N/A or #REF! in Google Sheets (row and column headers)INDEX(grid, MATCH(row), MATCH(col)) misses a header (#N/A) or asks INDEX for a column past the grid (#REF!). Recreate a stores×months matrix — not two-criteria fact-table MATCH.

Fix GOOGLETRANSLATE #N/A / #VALUE! / loading in Google SheetsGOOGLETRANSLATE fails on a bad language code, a non-text value, or a hung Loading…. Recreate xx as a language and a number without TEXT — not GOOGLEFINANCE, not IMPORTXML.

Fix FILTER + REGEXMATCH in Google Sheets (dot, class, and unescaped parentheses)REGEXMATCH inside FILTER treats . as any character, so N12.4 also keeps N12X4. An unescaped ( is an invalid pattern. Not FILTER of sku 999, not SEARCH #VALUE!.

Fix SORT #N/A in Google Sheets (sort column height does not match)SORT’s outside sort_column must be one column with the same number of rows as the data. A shorter key is #N/A, mismatched range sizes. Not a blocked spill, and not SORTBY.

Fix MAKEARRAY LAMBDA errors in Google Sheets (wrong arity or a nested array)MAKEARRAY needs a LAMBDA with exactly two names, row and column. One name is #N/A. Returning {r,c} is a nested array. Sheets does not use Excel’s #CALC!.

MATCH type 1 on an unsorted column returns #N/A or the wrong rowMATCH with search type 1, or with the third argument omitted, is approximate. On an unsorted list that is #N/A or not the exact position. A sorted band is the control.

XLOOKUP match_mode: wildcard and next larger are not exactXLOOKUP match_mode 0 is exact, 2 is wildcard, 1 is next larger, and -1 is next smaller. That fifth argument is not search_mode.

GOOGLEFINANCE #N/A from an illegal attribute or a bad historical windowGOOGLEFINANCE returns #N/A for an attribute that is not valid with those arguments, or when the end date is before the start. Not a ticker typo. Not investment advice.

FILTER misses the same day when the cell includes a timeFILTER equality against a date matches midnight only. A timestamp later that day is a different serial. INT or a half-open range keeps the calendar day.

CONVERT returns #N/A when the unit pair is unknown or incompatibleCONVERT returns #N/A for a unit it does not know, or for two units from different dimensions. m to ft works. m to kg does not. A temperature point is not a delta.

GOOGLEFINANCE currency pairs: CURRENCY: syntax, price history, and lagA currency ticker is CURRENCY: plus two codes. CURRENCY:USD is #N/A. History uses price, not close. A stale number is lag, not a bad pair. Not investment advice.

GOOGLEFINANCE mutual fund symbols are not stock tickersA fund needs the MUTF: prefix. volume and a stock-style close window are often #N/A. Fund attributes include closeyest and returnytd, and the feed can still have no row. Not investment advice. A number is not guaranteed.

GOOGLEFINANCE changepct is #N/A on a currency pairchangepct is a stock and mutual-fund attribute. CURRENCY:USDJPY with changepct is #N/A. The pair’s quote is price, delayed, and it is not a percent. Not investment advice.

Fix BYCOL LAMBDA errors in Google Sheets (array return or wrong arity)BYCOL must get one value back per column. Returning the column is a nested array. Two LAMBDA names is the wrong arity. Not BYROW, not MAKEARRAY.

IMPORTXML and the other IMPORT functions return #N/A when the request quota is exhaustedToo many IMPORTHTML, IMPORTDATA, IMPORTFEED, or IMPORTXML calls returns a cool-down #N/A, not a bad XPath and not Result too large. Wait, combine calls, or paste values.

SPARKLINE returns #N/A for an illegal charttype or a bad color optionSPARKLINE chart types are line, bar, column, and winloss. area and a misspelled color option are #N/A. An empty range is the other page. The options array is pairs.

SPARKLINE bar is already stacked; a group option is #N/Acharttype bar stacks the numbers in one cell. The option name group is #N/A. A vertical range does not draw one stack per row. column is separate columns, not a stack.

Fix SORTN in Google Sheets when display_ties_mode adds or drops rowsSORTN mode 0 caps the spill at n. Mode 1 adds rows tied with the nth. Mode 2 drops duplicate rows first. Mode 3 keeps every duplicate of the first n keys.

SORTN display_ties_mode spills every tie, so n rows become a crowdMode 1 keeps every row tied with the nth. Six people on the winning time and n of 3 still spill six rows. Mode 0 stops at n. Identical duplicate rows come along too.

Fix HSTACK #N/A in Google Sheets when the arrays have different heightsHSTACK pads a short array with #N/A down to the tallest one. A blank cell in a same-height range stays blank. FILTER refuses mismatched heights instead of padding.

Fix VSTACK #N/A in Google Sheets when the arrays have different widthsVSTACK pads a narrow array with #N/A out to the widest one. A short extra column of tags becomes a block of #N/A. HSTACK’s row pad is the other axis.

Fix MAP in Google Sheets when the arrays are different sizesMAP requires every array argument to share a height and width. A 3-row list beside a 4-row list is #N/A. A row beside a column is the same error. One LAMBDA name per array.

Fix IFS #N/A in Google Sheets when there is no default pairIFS has no unpaired default. A trailing other is the wrong number of arguments, or it is read as a condition. The else is a final TRUE pair. SWITCH’s last argument does not paste in.

Fix SWITCH in Google Sheets when the value and the case have different typesSWITCH matches with equality and does not coerce text to numbers. A numeric 1 misses the case "1" and the default always runs. With no default the cell is #N/A.

SWITCH(1, "1", …) misses because a number is not the text "1"SWITCH compares with equality and does not coerce. Multiplying the text by 1 or wrapping N() is not the same as a matching case. The miss is the default, or #N/A when the default is omitted.

FORECAST returns #N/A when the x and y ranges differ in lengthFORECAST(x, data_y, data_x) needs the same number of points in y and x. A shorter x range is #N/A. A matched line through 1,2,3 and 2,4,6 forecasts 8 at x of 4.

FORECAST.ETS seasonality 0 drops the weekly pattern; auto can return #NUM!In Excel, seasonality 0 fits a non-seasonal model and will not repeat a weekday spike. Seasonality 7 forces a weekly cycle. Omitted or 1 is automatic and is #NUM! when no cycle can be detected. Google Sheets has no FORECAST.ETS.

LINEST verbose spills a stats block, and some of those cells are #N/ALINEST with verbose TRUE returns five rows. Extra columns in the lower rows are #N/A on purpose. A shorter x range is a different error. A filled neighbor is #REF!.

LINEST’s five-row block is not slope, then r², then whatever fitsVerbose LINEST spills coefficients, then their standard errors, then r² and the residual standard error, then F and degrees of freedom, then two sums of squares. Reading the slope’s standard error as r² invents a confidence the fit did not report.

REGEXEXTRACT returns capturing groups, and a miss is #N/AREGEXEXTRACT of SKU-\d+ on SKU-101-A returns SKU-101. Parentheses around the digits return 101. Three groups spill three cells. No match is #N/A.

XMATCH match_mode 2 treats * and ? as wildcardsXMATCH’s third argument 2 is wildcard mode. A literal star in the key matches a prefix instead, or misses. Tilde escapes. The fourth argument 2 is binary search, a different page.

XLOOKUP search_mode -1 returns the last duplicate, which is not the latest dateOn two rows of sku 101, search_mode -1 returns the lower row. If that row is an older date, you did not get the latest sale. The -1 in match_mode is a different argument.

XLOOKUP returns the first duplicate and does not say there was anotherWith duplicates, the default search walks top to bottom and returns the first hit. There is no #N/A. search_mode -1 is the sixth argument and returns the last hit. It does not sort by a date.

LOOKUP in vector form returns the next smaller key and does not say the key was missingOn a sorted 10, 20, 30, 40, LOOKUP of 25 returns the name beside 20. There is no exact-match argument. VLOOKUP of 25 with FALSE is #N/A. Unsorted data does not become #N/A either.

LOOKUP’s result vector is positional, and the search vector has to be sortedLOOKUP(key, search, result) pairs rows by position and always approximates. An unsorted search returns a quiet wrong name. A shorter result vector is #N/A. There is no exact-match argument.

SWITCH still returns #N/A when the last word is not a real defaultA default is the leftover argument after complete case, value pairs. An even tail has no default, so the last word is a case value and a new status is still #N/A. A missing else with no extra tokens is the other page.

IFS uses only the first value when the condition is an array from MAPIFS(MAP(A2:A4,LAMBDA(...)), "e", TRUE, "other") returns one cell from the first row. Later East rows never get a label. MAP with IFS inside the lambda runs once per row. ARRAYFORMULA(IFS) is the neighbor bug.

ISNA of a column returns one FALSE unless you array-enter itISNA(A2:A) without ARRAYFORMULA tests the first cell and returns one FALSE. A #N/A further down the column does not flip that flag. ARRAYFORMULA(ISNA(...)) spills a flag per row.

FILTER and REGEXMATCH keep partial cells when the pattern has no ^ and $REGEXMATCH is true when the pattern occurs anywhere in the cell. FILTER then keeps A12-X and XA12 along with A12. Anchors make it a whole-cell test. The dot metacharacter is the other page.

FILTER returns #N/A when any criteria cell is #N/AOne #N/A in the criteria range fails the whole FILTER, including rows whose own criteria cell is fine. Wrap the test in IFNA so each row is TRUE or FALSE. A real empty match is a different #N/A.

XLOOKUP search_mode 2 and -2 return the wrong price when the sort direction is wrongBinary search_mode 2 needs an ascending lookup column. Mode -2 needs descending. On unsorted keys, or on the wrong direction, the result is #N/A or another row’s price, with no error on a wrong hit. Last-match -1 is the other page.

HLOOKUP with TRUE on an unsorted header row returns the wrong columnHLOOKUP’s fourth argument TRUE, or an omitted fourth argument, is approximate and needs the first row sorted left to right. On an unsorted header the result is #N/A or another column’s price. FALSE returns the exact column.

MATCH search type -1 needs a descending list and misses on ascending keysMATCH type -1 returns the smallest value that is still greater than or equal to the key, and only when the range is sorted descending. On an ascending list that is #N/A or the wrong position. Type 1 is the ascending page.

MATCH exact mode still treats * as a wildcard unless you escape it with ~search_type 0 is exact, and * ? ~ are still patterns. The pattern ~* finds a literal star, not the two characters tilde and star. A literal ~* is ~~~*. The pattern ~~* matches every key that starts with a tilde.

LOOKUP with one array is always approximate, and the shape picks the axisLOOKUP(key, array) has no exact flag. A tall range searches the first column and returns the last column. A wide range searches the first row and returns the last row. Unsorted keys still return a neighbor. The three-argument form is the other page.

A trailing space on a month header makes two-way INDEX/MATCH return #N/AMATCH on the header row is exact. Feb with a trailing space is not Feb, so the inner MATCH is #N/A and the whole INDEX is #N/A. A missing month name and a MATCH range that includes the corner cell are the other page.

HSTACK fills the short side with #N/A, and AVERAGE of the spill is #N/AA shorter array beside a taller one does not error. The missing cells are #N/A. SUM, AVERAGE, and a chart of that block all see the pad. A same-height blank stays blank. The row-count primer is the other page.

WRAPROWS pads the last row with #N/A, and SUM of that spill is #N/AWhen the value count is not a multiple of wrap_count, the leftover cells are #N/A unless pad_with is set. SUM and AVERAGE of the spill fail. A blank pad keeps the numeric total. wrap_count 0 is the other page.

WRAPCOLS pads the short last column, and a chart plots that padFive numbers wrapped by 3 leave #N/A in the last column. A column chart of the spill draws that pad. pad_with 0 draws a real zero bar. SUM of the default pad is #N/A.

#REF!

#REF! after you delete a cell in Google SheetsDelete a cell or row a formula pointed at and the formula becomes #REF!. Undo, rewrite the reference, and do not confuse this with a circular loop or a VLOOKUP index.

Fix VLOOKUP #REF! in Google Sheets (column index bigger than the range)VLOOKUP returns #REF! when the column index is bigger than the lookup range. Recreate index 3 on two columns and index 4 on three — this is not a deleted cell, not a spill, and not a circular loop.

Fix INDEX #REF! in Google Sheets (row or column past the range)INDEX returns #REF! when the row index or the optional column index sits past the range. Recreate a row that is too big and a column that is too big — not VLOOKUP index, not a delete, not INDIRECT.

Fix OFFSET #REF! in Google Sheets (offset walks off the sheet)OFFSET returns #REF! when rows/cols land off the grid, or when height/width is zero or negative. Recreate a step above A1 and a zero height — not a delete, not INDEX, not a spill.

Fix IMPORTRANGE #REF! in Google Sheets (You need to connect these sheets)IMPORTRANGE shows #REF! until you click Allow access. Recreate the connect prompt, then a wrong tab name after access — this is not Result too large and not a deleted cell.

Fix IMPORTRANGE “Result too large” in Google SheetsIMPORTRANGE errors with Result too large when the pull exceeds the ~10 MB cap. Narrow the range, wrap QUERY, or split the import — this is not the Allow access #REF!.

Fix IMPORTRANGE stuck on Loading… in Google SheetsIMPORTRANGE can sit on Loading… for a large or slow pull. Recreate a small connected import, a wide range that stays on Loading, and what is actually Allow access vs Result too large — do not keep refreshing forever.

Array result was not expanded because it would overwrite dataUNIQUE returns #REF! when its spill would overwrite a cell that already has a value. Recreate a block below and a SORT blocked to the right, then clear the path — this is not a deleted reference.

Fix SORT and UNIQUE #REF! in Google Sheets (spill cannot expand)SORT or UNIQUE returns #REF! when a cell below or to the right already has a value. Recreate the blocked spill, then clear the blockers or move the formula — not a deleted reference and not a VLOOKUP/HLOOKUP index.

Fix SEQUENCE #REF! in Google Sheets (spill cannot expand)SEQUENCE returns #REF! when its spill cannot expand into occupied cells. Recreate a blocked bay list — not SORT/UNIQUE spill, not UNIQUE overwrite.

SEQUENCE returns #REF! when the spill runs into a merged cellA merge occupies every cell in it, even the ones that look blank. SEQUENCE cannot expand through that block. The tooltip names the merge. Unmerge. A typed note in the way is the same #REF! without a merge.

Circular dependency detected in Google Sheets#REF! from a formula that refers to itself. Recreate a self-ref, a two-cell loop, and a SUM that includes its own cell — iterative calculation is the wrong fix here.

A circular formula looks finished once iteration is on, and the arrows do not mean it convergedExcel draws circular tracer arrows while a value sits still. Sheets does not draw those arrows. With iterative calculation on, #REF! disappears and a stable number can be the iteration cap, not the total you meant.

Fix INDIRECT #REF! in Google Sheets (bad address text or deleted sheet)INDIRECT returns #REF! when the text is not a valid A1/R1C1 address, or the sheet name does not exist. Recreate both — this is not a deleted-cell #REF! and not a VLOOKUP index.

INDIRECT cannot read another workbook, open or closedExcel's INDIRECT does not read a closed file either. Sheets has no second open workbook for INDIRECT to see. A path, an .xlsx name, or a docs URL inside INDIRECT is #REF!. Another Google Sheet is IMPORTRANGE, which has its own permission step.

Fix SPLIT unexpected columns or #REF! in Google Sheets (overwrite / empty delimiter)SPLIT returns #REF! when it would overwrite a filled cell, or explodes the row when the delimiter is empty. Recreate a blocked spill and SPLIT with "" — not a general array overwrite.

Fix named range #REF! or #NAME? in Google Sheets (name deleted, sheet renamed, or name never existed)A formula that uses a named range returns #NAME? if the name is gone or never existed, and #REF! if the name still exists but its cells do not. Recreate delete-name and delete-target — not a misspelled function, not delete-cell #REF!.

The same named range on two sheets: wrong scope or #REF! after you delete oneDuplicating a tab can create a second sheet-scoped name. SUM(qty) on that tab is local. Deleting the tab makes a qualified reference #REF!. A second qty cannot be typed by hand.

Restoring a version leaves a named range pointing at #REF!After File → Version history → Restore, a named range can still show #REF! as its target. Formulas that use the name are #REF! even though the cells are back. Re-point the name. Deleting it turns the error into #NAME?.

Dragging an ARRAYFORMULA down makes #REF! in the spillThe fill handle copies ARRAYFORMULA into the cells below. The original spill cannot expand and shows Array result was not expanded. Delete the copies. Leave one formula. A blocked UNIQUE is the same tooltip for a different reason.

Fix TOCOL / TOROW #REF! or wrong shape in Google Sheets (ignore blanks vs errors)TOCOL and TOROW flatten a block, then #REF! if the spill is blocked, or keep blanks and errors you thought ignore dropped. Recreate ignore 1 vs 2 — not SORT/UNIQUE spill.

Fix WRAPROWS / WRAPCOLS #NUM! or #REF! in Google Sheets (wrap count 0 / overwrite)WRAPROWS and WRAPCOLS return #NUM! when wrap_count is 0 or negative, and #REF! when the wrapped spill would overwrite a filled cell. Recreate count 0 and a blocked wrap — not TOCOL shape.

Fix CHOOSEROWS / CHOOSECOLS index errors in Google Sheets (index out of range)CHOOSEROWS and CHOOSECOLS error when an index is 0 or past the range. Recreate row 0, row 5 of a 3-row roster, and sheet-row vs relative-row — not INDEX #REF!, not CHOOSE #VALUE!.

Fix TEXTJOIN spill overwrite or empty delimiter in Google SheetsTEXTJOIN with an empty delimiter concatenates into one blob; ARRAYFORMULA(TEXTJOIN) joins the whole block; BYROW+TEXTJOIN #REF!s if the spill is blocked. Recreate all three — not SPLIT empty delimiter.

Fix XLOOKUP return-range spill #REF! in Google SheetsXLOOKUP spills the whole result_range width; a filled neighbor is #REF!. Recreate a two-column return blocked in F2 — not XLOOKUP vs VLOOKUP, not XLOOKUP exact miss.

Fix TRANSPOSE #REF! in Google Sheets (spill blocked or an overlapping paste)TRANSPOSE returns #REF! when the swapped rectangle would overwrite a cell, including a value pasted into the spill. Recreate a blocked column and a circular transpose — not MMULT, not SORT.

INDIRECT quotes sheet names that contain spaces or apostrophesINDIRECT needs quotes inside the text when the sheet name has a space. An apostrophe in the name is doubled. Unquoted text is #REF!.

IMPORTRANGE returns #REF! or stays on Loading after access is revokedA pull that used to work shows #REF! or Loading… when the source is unshared or you switch Google accounts. Allow access may not appear. Not the first connect, not Result too large.

IMPORTRANGE breaks when a named range is renamed or deletedIMPORTRANGE of a named range returns #REF! after that name is renamed or deleted. Renaming the tab does not break the name. An A1 string does the opposite.

IMPORTRANGE returns #REF! after the source tab is renamedAn A1 string in IMPORTRANGE breaks when the source tab is renamed. The tooltip is a missing range or sheet. A named range follows the tab. Not a revoked share.

An IMPORTRANGE cycle between two workbooks never finishesSpreadsheet A imports B and B imports that result from A. Google's help says the cycle never produces output. The cells sit on Loading… or return #REF!. A one-way small import is the control.

Several IMPORTRANGE formulas fill the spreadsheet and return #REF!One request over the cap is Result too large, about 10 MB. Several smaller imports can still overflow the spreadsheet's 10 million cells. The import that cannot land is #REF!, not a quiet trim.

A named function that calls IMPORTRANGE hides the Allow access promptIMPORTRANGE inside Data, Named functions often shows #REF! with no Allow access button. A direct IMPORTRANGE in a cell is what connects the two files. The named function does not grant access, and a new spreadsheet needs its own connect.

GETPIVOTDATA returns #REF! when the field or item is goneGETPIVOTDATA returns #REF! when the value field, the pivot item, or the pivot itself changed. The formula still names the old header. Not a VLOOKUP miss.

Clicking a pivot cell inserts GETPIVOTDATA, which breaks when the labels changeTyping = and clicking a pivot value does not store that cell address. Sheets writes GETPIVOTDATA with the field and item names. Rename or remove the field and the formula is #REF! while the grid still shows a number.

GETPIVOTDATA on a month group is #REF! when you pass the original dateAfter you group pivot dates by month, the item is the group label, such as Jan, and the field name gains the group. A source date like 15 Jan 2024 is not that item. GETPIVOTDATA returns #REF!.

WRAPCOLS returns #NUM! for a bad wrap count and #REF! when the next row is blockedWRAPCOLS fills down, then starts a new column. wrap_count 0 is #NUM!. A value under the formula is #REF!. Clearing the cell to the right does not unblock it.

CHOOSEROWS returns an error for 0, a bad negative, or a mixed index listCHOOSEROWS rejects 0 and a negative past the block. One bad index in a list fails the whole call. 0.9 truncates to 0. Not the sheet-row primer.

Fix OFFSET #REF! in Google Sheets when height or width is 0 or negativeOFFSET height and width must be greater than 0. Zero and negative sizes are #REF!. Omitting them means 1. Excel’s upward negative height is not how Sheets works.

Fix INDEX surprises in Google Sheets when the row or column is 0INDEX row 0 returns the whole column, and column 0 returns the whole row. A blocked spill is #REF!. An index past the range is the other #REF!, with a different tooltip.

Fix TOROW #REF! in Google Sheets when the horizontal spill is blockedTOROW spills across the row. A value in the next column is #REF!. Cells below the formula do not block it. Ignore only shortens the row. It does not turn TOROW into TOCOL.

Fix RANDARRAY in Google Sheets when the spill is blocked or the values keep changingRANDARRAY spills like SEQUENCE. An occupied neighbor is #REF!. Rows of 0 are #NUM!. The array is volatile: it changes on recalculation, so it is a bad lookup key.

Fix ADDRESS and INDIRECT mix-ups in Google Sheets (A1, R1C1, absolute mode)ADDRESS mode 4 is a relative A1 string. use_a1 FALSE is R1C1. INDIRECT of that string is #REF! unless its second argument is FALSE. Mode 0 is #NUM!.

MODE.MULT spills every mode and #REF! when the next cell is filledTwo values tied for most frequent make MODE.MULT spill down. A note in the next row is #REF!. No duplicate at all is #N/A. MODE.SNGL returns only one mode.

FREQUENCY spills one row past the bins, and a filled cell is #REF!FREQUENCY returns one count per bin plus a final greater-than bin. Three rows for two cutoffs. A note in that spill is #REF!. Unsorted bins return a quiet wrong count.

FREQUENCY adds an overflow bin, so the chart has one bar too manyTwo cutoffs return three counts. The last count is “greater than the last cutoff,” not a total and not an error. Charting the spill without a label for that bin draws an extra bar.

A wide SPLIT is #REF! when a later column is filled, and a cap hides the restSPLIT of a|b|c|d|e|f needs six cells. A note in the fifth spill cell is #REF!. ARRAY_CONSTRAIN to three columns succeeds and drops d, e, and f with no error.

REGEXREPLACE uses $ for groups, and a backslash escapes the next characterIn the replacement, $1 is group 1. \1 is the digit 1, not the group. \\ in the formula string inserts one backslash. A group number that does not exist is #REF!.

Excel table structured references become #REF! or text in Google SheetsTable1[Column] and [@Column] are Excel table syntax. After an .xlsx import they show up as #REF! or as text Sheets will not calculate. Rewrite them as normal ranges.

Iterative calculation is on and the circular cell is still #REF!File → Settings → Calculation can allow a circular formula, but a max iteration of 0 or 1 leaves #REF! or a value that has not converged. Raise the max. An accidental SUM loop is the other page.

ADDRESS absolute mode 4 does not follow the cell when rows are insertedADDRESS modes 1–4 only change dollar signs in a text label. Mode 4 is A1 with no locks. That text does not move when you insert a row. INDIRECT then reads the old coordinates. R1C1 notation is the other page.

INDIRECT of R[1]C[1] moves when you fill the formula acrossINDIRECT("R[1]C[1]", FALSE) is relative to the cell that holds the formula. Filled one column over, it points one column further. Absolute R1C1 text does not. Omitting FALSE is #REF!.

A sheet full of INDIRECT recalculates on every editINDIRECT is volatile. Hundreds of INDIRECT formulas rerun when any cell changes, so the file lags. A normal reference or INDEX does not. A bad address string is the #REF! page.

OFFSET ranges recalculate on every edit and drag FILTER and charts with themOFFSET is volatile. A dynamic height built with OFFSET reruns on unrelated edits, so dependent FILTER formulas and charts lag. INDEX can mark the last row without that. Height 0 is a different #REF!.

XLOOKUP spills the return array across when you expected the months stacked downThe spill follows the shape of return_array. A multi-column return next to a vertical lookup spills sideways. A multi-row return under a horizontal lookup spills down. A blocked neighbor is #REF! on the other page.

INDEX with one number returns the whole row; the column form leaves the row argument emptyINDEX(range, n) omits the column, so the result is every column of row n. INDEX(range,, n) omits the row and returns the whole column. A single cell needs both numbers. Explicit 0 is the same spill on another page.

INDIRECT of a named-range string follows scope, so the same text can point at the wrong cellsINDIRECT needs the name as text. INDIRECT(qty) without quotes passes the values and is #REF!. A workbook-scoped qty is the same from every sheet. After a tab copy, the bare string can still mean the original range. A bad address is the other page.

Excel's # spill reference imports as a parse error, #REF!, or #NAME? in Google SheetsA1# means the spill anchored at A1 in Excel. Sheets has no spill operator. The hash stays in the formula bar and the cell does not follow the spill. An empty formula bar is a different import: the spill became values.

OFFSET height or width that runs past the grid is #REF!A positive height or width is legal until the rectangle leaves the sheet. Sheets stops at column ZZZ. A width of 20000 from column A is #REF!. Stepping above row 1, and height 0, are other pages.

Named ranges will not store an OFFSET formulaData, Named ranges wants a range like Sheet1!A2:A. An OFFSET that grows with COUNTA is rejected. Excel Name Manager accepts that formula. In Sheets, put OFFSET in a named function or skip it for INDEX.

FREQUENCY with bins in a row still spills downward, so a chart across that row mis-bucketsClass limits typed across a row are read as the class list, and the counts still spill down, one row longer than the bin row. The cells under the later bins stay empty. Charting across the row pairs the wrong labels. A blocked extra row is the other page.

#DIV/0!

Fix #DIV/0! in Google Sheets (divide by zero or a blank divisor)A2/B2 is #DIV/0! when B2 is 0 or blank. Recreate it, then guard the divisor with IF — IFERROR only if you accept hiding other errors.

Fix AVERAGEIF #DIV/0! in Google Sheets (no numeric match)AVERAGEIF returns #DIV/0! when no matching cell is numeric — zero matches, or matches that are all text/blank. Recreate West with no numbers — not A2/B2 divide by zero.

An empty AVERAGEIFS is #DIV/0!, and a wrapper that turns it into 0 looks like a real average of zerosNo numeric row matches and AVERAGEIFS returns #DIV/0!. It does not return 0. IFERROR of that error to 0, or N of a caught error, matches the average of rows that really are 0. COUNTIFS tells the two zeros apart. MINIFS of no match really is 0.

IFERROR on a spill hides #DIV/0!, and IFNA only hides #N/AWrapped around an array, IFERROR replaces every error in the spill, including #DIV/0!. IFNA replaces only #N/A and leaves the divide visible. One-cell wrappers are the other page.

AVERAGEIF blank criteria: quotes, =, and an empty cell are not the same testAVERAGEIF with "" or "=" averages rows whose criterion cell is blank. A blank cell used as the criterion matches 0, not blanks. The average jumps, or it is #DIV/0!.

CORREL returns #DIV/0! when one column does not varyPearson correlation divides by both standard deviations. A column of identical numbers has standard deviation 0, so CORREL is #DIV/0!. A real correlation of 0 is a different result. Do not replace the error with 0.

#NAME?

Fix #NAME? in Google Sheets (unknown function or name)VLOKUP and other unrecognized names return #NAME?. Recreate the misspelling, then correct the function — this is not a locale parse error.

Fix LET #NAME? or #ERROR! in Google Sheets (name typo, reserved name, or a name used before it is defined)LET returns #NAME? or #ERROR! when a name is misspelled, reserved (like A1), or used before it is assigned. Recreate shiftt, A1 as a name, and ot before it exists — not a misspelled VLOOKUP.

Fix named function errors in Google Sheets (arity, A1 placeholder, named range shadow)A named function returns #N/A when you pass the wrong number of arguments. Placeholder A1 is rejected in the editor. A named range with the same name wins over the function.

A recursive named function calls itself with too few argumentsWALK2(n, acc) that recurses as WALK2(n-1) is #N/A: expected 2 arguments, got 1. The outer call can pass both. The missing argument is inside the definition. Not the depth limit.

AI() or Gemini() is unavailable or #NAME? in Google SheetsAI() and Gemini() can be #NAME? or show AI function not available. Plan, admin, language, and region decide access. This page does not promise the function runs for you.

AI() or Gemini() on a huge range does not analyze every rowA large range passed to AI() can fail to generate, stop after the first 350 selected cells, or return text that never used the later rows. Plan and region still gate the function. This page does not promise a summary.

A Gemini formula suggestion uses an Excel-only name and shows #NAME?Help me organize and Ask Gemini can be missing. A suggestion that inserts GROUPBY, TAKE, DROP, or a # spill reference is #NAME? or a parse error in Sheets. This page does not promise the panel opens.

Apps Script custom functions: #NAME?, Loading, and the time limitA custom function is #NAME? until the bound script is saved under that name. While it runs the cell says Loading. Past the time limit it fails. This is not Data, Named functions.

Apps Script custom functions stay cached until an input changesA custom function with no arguments does not refresh when other cells change. NOW() in the next cell does. Pass the cells you depend on as arguments. There is no volatile switch. #NAME? and the time limit are the other page.

A custom function shows You do not have permission to call UrlFetchAppUrlFetchApp.fetch inside a function called from a cell throws immediately. Custom functions cannot request that authorization. A menu or an installable trigger can fetch, write a cell, and let the sheet read that cell.

LET calculates names in order, including names the result never readsA nested LET that uses a name before the outer assignment is #NAME?. An earlier 1/0 still breaks the cell when the result does not use it. Sheets does not skip that binding. A repeated name in one LET is the clash page.

Fix LET name clashes in Google Sheets (duplicate names, SUM, named ranges)A repeated LET name errors. A local name sum hides the SUM function. A local rate hides a named range and returns the local value with no triangle. Typos and A1 names are the other page.

Fix named functions used as LAMBDA arguments in Google SheetsA named function is not a LAMBDA until you call it. MAP of the bare name fails. Placeholders substituted into an inner LAMBDA are not valid names. Parentheses decide which one runs.

An Excel LAMBDA stored in Name Manager imports as #NAME? in Google SheetsA name whose formula is LAMBDA is an Excel defined name. Sheets does not turn it into Data, Named functions. The cell that calls the name is #NAME?. _xlfn.LAMBDA is #NAME? too. Recreate the name in Sheets if the body uses functions Sheets has.

The wrong AGGREGATE function_num or option still includes errors or hidden rowsAGGREGATE is Excel. Option 0 ignores nested subtotals and still returns #DIV/0!. Option 6 ignores errors and still counts hidden rows. In Google Sheets the same formula is #NAME?.

#VALUE!

Fix #VALUE! in Google Sheets (wrong type)A2+B2 is #VALUE! when one cell is text. Recreate the type mix, then convert or stop adding the unit — this is not #N/A, #NAME?, or a parse error.

Fix DATEVALUE #VALUE! in Google Sheets (text the locale will not parse)DATEVALUE returns #VALUE! when the string is not a date in this spreadsheet’s locale. Recreate a US M/D/Y string on a D/M/Y locale (and the reverse) — English UI, not QUERY mixed dates.

Fix TIMEVALUE #VALUE! in Google Sheets (text the locale will not parse as a time)TIMEVALUE returns #VALUE! when the string is not a time in this spreadsheet’s locale. Recreate a colon time vs a dotted time, and a date string — not DATEVALUE, not generic #VALUE!.

Fix FIND and SEARCH #VALUE! in Google Sheets (substring not found)FIND and SEARCH return #VALUE! when the substring is not in the text. Recreate a FIND miss (case-sensitive) versus a SEARCH miss — this is not REGEXEXTRACT #N/A.

Fix COUNTIFS / SUMIFS #VALUE! in Google Sheets (mismatched range sizes)COUNTIFS and SUMIFS return #VALUE! when a criteria range is not the same length as the count or sum range. Recreate the size mismatch, then make every range the same size — not FILTER mismatch, not 10 + pcs, and not a QUERY parse.

Fix CHOOSE #VALUE! in Google Sheets (index is 0, negative, or past the list)CHOOSE returns #VALUE! when the index is 0, negative, or larger than the value list. Recreate index 0 and index 4 on three statuses — not INDEX #REF!.

Fix SUBSTITUTE #VALUE! in Google Sheets (instance number)SUBSTITUTE returns #VALUE! when occurrence_number is 0, negative, or non-numeric. Recreate instance 0 and instance 4 of three dashes — not REGEXREPLACE, not FIND miss.

Fix LEFT / RIGHT / MID #VALUE! in Google Sheets (negative or non-integer length)LEFT, RIGHT, and MID return #VALUE! when the length or start is negative, or MID’s start is 0. Recreate MID(lot,0,2) and LEFT(lot,-1) — not FIND miss, not SUBSTITUTE instance.

Fix TO_PURE_NUMBER on currency and text in Google SheetsTO_PURE_NUMBER strips number formatting from a numeric cell; currency typed as text is returned unchanged, so later math is #VALUE!. Recreate $12.50 format vs '$12.50' text — not VALUE() locale.

TO_PURE_NUMBER of a 50% cell is 0.5, and a later *100 counts the percent twicePercent format displays 0.5 as 50%. TO_PURE_NUMBER returns that stored 0.5 and strips the format. Multiplying by 100 because the cell 'was a percent' stores 50, which displays as 5000% if you format it as percent again.

Fix VALUE() #VALUE! in Google Sheets (locale-formatted number text)VALUE parses numeric text with this spreadsheet’s locale. Recreate 1.234,56 on a US sheet and 1,234.56 on a decimal-comma locale — not 10+pcs, not TO_PURE_NUMBER passthrough.

VALUE of 12% or $1,200 text is #VALUE! when this locale does not own that symbolVALUE reads percent and currency text with the spreadsheet locale. A space, a foreign currency mark, or the other decimal shape is #VALUE!. A percent that parses is 0.12, not 12.

FIXED returns text, so SUM stays 0 until you wrap VALUEFIXED rounds and returns a string. The cell looks like 1,234.50. ISNUMBER is FALSE. SUM of that text is 0. Adding 1 is #VALUE!.

FIXED writes a thousands separator VALUE cannot parse on a space-separator localeFIXED returns text. On a locale that groups thousands with a space, a comma in that text is not the local separator. VALUE of it is #VALUE!. Suppressing the separator still leaves a decimal mark you have to check. ROUND returns a number.

Fix MAXIFS / MINIFS #VALUE! in Google Sheets (mismatched range sizes)MAXIFS and MINIFS return #VALUE! when a criteria range is not the same size as the max/min range. Recreate a four-row region next to a three-row qty — not COUNTIFS, not AVERAGEIF #DIV/0!.

Fix MMULT #VALUE! in Google Sheets (inner dimensions)MMULT needs the first matrix’s column count to equal the second’s row count. Recreate a 2×3 times a 2×2 — not generic 10+pcs, not FILTER size.

MMULT of two columns is #VALUE!; the dot product needs one row vectorTwo n×1 ranges have inner dimensions 1 and n. MMULT returns #VALUE!. TRANSPOSE one of them and the product is the single number 1×4+2×5+3×6. Transposing the other way spills a 3×3 outer product, not that number.

Fix TEXT #VALUE! in Google Sheets (bad format code or a non-numeric input)TEXT returns #VALUE! when the format contains *, mixes a date pattern with 0, or the input is text like pcs. Recreate those — not VALUE(), not an unquoted format name.

DATEVALUE of 03/04/2024 is a different day when the locale swaps month and dayAn ambiguous DATEVALUE string parses with no error and the wrong day. 03/04/2024 is March 4 on a US locale and 3 April on a UK locale. Day 13 is the other page.

SUMPRODUCT returns #VALUE! when the arrays are different sizesSUMPRODUCT returns #VALUE! when the arrays differ in height or width. A 3-row list times a 4-row list is the mismatch. Text in a matched range becomes 0.

DGET returns #VALUE! for no match and #NUM! for more than oneDGET returns one cell. No matching record is #VALUE!. Two matching records is #NUM!. A blank criterion matches every row. Not VLOOKUP.

HYPERLINK returns #VALUE! for an empty URL or a nested link as the labelHYPERLINK needs a non-empty URL string and a label that is text. An empty URL is #VALUE!. A HYPERLINK nested in the label is #VALUE!. A bad scheme can still look clickable.

Fix REDUCE in Google Sheets when the LAMBDA or the accumulator is wrongREDUCE’s LAMBDA needs two names and must return the next accumulator. One name is #N/A. Starting at 0 and multiplying stays 0. Returning the element keeps only the last one.

COUNTUNIQUEIFS returns #VALUE! when the criteria range is a different sizeCOUNTUNIQUEIFS needs the unique range and every criteria range to share a shape. A taller region list is #VALUE!. No matching rows is 0, not an error.

AVERAGEIFS returns #VALUE! when the ranges are different sizesAVERAGEIFS average range and criteria ranges must match. A taller criteria range is #VALUE!. No numeric match on equal ranges is #DIV/0!, not a size error.

FIND is case-sensitive and SEARCH is not, and only SEARCH treats * and ?FIND of pro in Widget Pro is #VALUE!. SEARCH of pro returns 8. SEARCH of P?o also returns 8. FIND looks for a literal question mark. Not the xyz miss.

JOIN keeps blanks and TEXTJOIN can skip them, and JOIN rejects a two-dimensional rangeJOIN of North, a blank, and East is North--East. TEXTJOIN with ignore_empty TRUE is North-East. JOIN of two rows is #VALUE!. Zero is not an empty cell.

MMULT returns #VALUE! when a cell is blank or text, even if the shapes matchA 2×2 times a 2×2 is legal. One blank cell is #VALUE! because MMULT will not coerce empty to zero. N() turns the blank into 0 and also turns pcs into 0.

MINVERSE returns #NUM! on a singular matrix and #VALUE! on one that is not squareThe matrix 1,2 / 3,4 inverts. The matrix 1,2 / 2,4 has determinant 0 and MINVERSE is #NUM!. A 2×3 block is #VALUE!. MDETERM tells them apart.

A CSV import leaves a leading apostrophe and VALUE or math returns #VALUE!Imported ids show a leading apostrophe in the formula bar. The character is in the value, so VALUE and arithmetic are #VALUE! until you strip it. A text-versus-number lookup miss is the other page.

CHOOSE returns #VALUE! when a calculated index is 0 or past the listA blank index is 0. CHOOSE(0, …) is #VALUE!. An index from COUNTA or MATCH that lands past the list is the same error. INDEX of row 0 is a different function and is legal.

CHOOSE of an array literal spills a different shape once ARRAYFORMULA is wrapped around itCHOOSE(1, {1,2,3}, {4,5,6}) and the same call inside ARRAYFORMULA do not have to paint the same block. A column of indexes does not zip to one literal per row. Braces follow the locale.

TO_TEXT keeps the cell’s format, and TEXT follows the pattern you passTO_TEXT of a percent-formatted 0.2 returns the text 20%. TEXT(value, "0.00") returns 0.20. TO_TEXT does not take a format code. Math on the formatted string is #VALUE!.

CHOOSECOLS with a negative index follows the right edge, so an added column changes the pickIndex -1 is the last column of the range you passed, not a fixed price column. Append a notes column and -1 returns notes. The price stays at index 4 until a column is inserted in front of it. Index 0 is the other page.

#NUM!

Fix #NUM! in Google SheetsSQRT of a negative number is #NUM!. Recreate the domain failure, then guard the input — this is not #VALUE!, not divide-by-zero, and not a parse error.

Fix WORKDAY / WORKDAY.INTL #NUM! in Google Sheets (bad start date or holiday list)WORKDAY and WORKDAY.INTL return #NUM! when the result date is out of range, the weekend code is invalid, or a holiday serial is impossible. Recreate weekend 99 and an overflow — not generic #NUM!.

Fix NETWORKDAYS / NETWORKDAYS.INTL #NUM! in Google Sheets (bad dates or weekend number)NETWORKDAYS and NETWORKDAYS.INTL return #NUM! when a date serial is impossible or the weekend code is invalid. Recreate weekend 99 and a bad holiday — not WORKDAY #NUM!, not generic #NUM!.

Fix EOMONTH #NUM! in Google Sheets (start date unparseable or months overflow)EOMONTH returns #NUM! when the start serial is not a date this calendar can use, or months push the result out of range. Recreate a huge serial and overflow months — not DATEVALUE #VALUE!.

Fix WEEKDAY #NUM! or wrong numbering in Google Sheets (return_type)WEEKDAY returns #NUM! when return_type is out of range, and silent wrong day numbers when type 1 vs 2 vs 3 is mixed. Recreate type 99 and Sunday on type 1 vs 2 — not WORKDAY #NUM!.

Fix DATEDIF #NUM! in Google Sheets (end before start, or MD / YD units)DATEDIF returns #NUM! when end_date is before start_date. Recreate a reversed pair and the MD leftover-day unit — not YEARFRAC basis, not WORKDAY.

Fix YEARFRAC #NUM! in Google Sheets (basis out of range)YEARFRAC returns #NUM! when basis is not 0–4. Recreate basis 5 next to a working Actual/Actual fraction — not DATEDIF end-before-start, not generic SQRT(-1).

Fix IRR and XIRR #NUM! in Google Sheets (same-sign cashflows, dates, guess)IRR and XIRR return #NUM! when every cashflow has the same sign, or when a guess never converges. A shorter XIRR date range is a size error, not SQRT.

LARGE and SMALL return #NUM! when n is out of rangeLARGE and SMALL return #NUM! for n of 0, a negative, or an n bigger than the count of numbers. Text is ignored. The max is n=1, not n=0.

NETWORKDAYS.INTL returns #NUM! for a bad weekend code or stringNETWORKDAYS.INTL weekend codes are 1–7 and 11–17. 8, 9, and 10 are #NUM!. A weekend string must be seven 0/1 characters, Monday first. Weekend 99 is the other page.

DGET returns #VALUE! for no match and #NUM! for more than oneDGET returns one cell. No matching record is #VALUE!. Two matching records is #NUM!. A blank criterion matches every row. Not VLOOKUP.

Fix SEQUENCE #NUM! in Google Sheets when rows, columns, or the size is illegalSEQUENCE rows and columns must be at least 1. Zero or negative is #NUM!. A negative step is a legal countdown. Step 0 repeats the start. A blocked spill is #REF!, not #NUM!.

PERCENTILE.EXC returns #NUM! at 0, at 1, and when it cannot interpolatePERCENTILE.EXC refuses k of 0 or 1. On a five-point list, 0.15 and 0.9 are #NUM! too. PERCENTILE.INC of 0 is the minimum. PERCENTILE of the same k matches INC.

RATE returns #NUM! when the guess does not convergeRATE(12, -100, 1000) is about 2.92% per period. A guess of 5 is #NUM!. A payment and a present value with the same sign are #NUM! for every guess.

EPOCHTODATE reads seconds unless you pass the millisecond unit1700000000 as seconds is 14 Nov 2023 22:13:20 UTC. The same number as milliseconds is 20 Jan 1970. A 13-digit millisecond stamp with the default unit is #NUM!.

DATEDIF units MD, YM, and YD are not leftover calendar daysFrom 10 Mar 2020 to 25 Nov 2024, Y is 4, YM is 8, MD is 15, and YD is 260. Reversing the dates is #NUM!. MD on a month-end start is the unit that goes strange.

WORKDAY.INTL weekend strings start on Monday, and holidays skip workdaysWeekend code 1 and the string 0000011 both land on 17 Jun 2024 after 10 workdays. A string that starts on Sunday marks Monday off. A holiday on a workday moves the result. A holiday on Saturday does not.

WEEKNUM and ISOWEEKNUM disagree by a week when the systems differDefault WEEKNUM is system 1: the week that contains January 1 is week 1, and weeks start on Sunday. ISOWEEKNUM is Monday and the first Thursday. 1 January 2023 is week 1 and week 52.

FLOOR returns #NUM! when the factor's sign does not match the numberFLOOR needs the factor and the value to share a sign. A positive number with a negative factor is #NUM!. FLOOR.MATH ignores the sign and uses mode for negatives.

CEILING and CEILING.MATH round negative numbers in different directionsOn a negative value, CEILING uses the sign of the factor to pick a direction. CEILING.MATH ignores that sign and uses mode. Toward zero and away from zero disagree. A positive value with a negative factor is #NUM!.

RANDBETWEEN returns #NUM! when the bottom is greater than the topThe first argument is the low end and the second is the high end, inclusive. Swapping them is #NUM!. Decimals are truncated first, so 1.2 and 1.8 always return 1. A legal pair still redraws on every edit, which is the other page.

QUERY

QUERY + IMPORTRANGE NO_COLUMN in Google Sheets (use Col1, not A)QUERY on IMPORTRANGE needs Col1, not A — select A returns #N/A even when the import already works. Recreate select A and select B, then Col1 / Col2.

Fix QUERY “Unable to parse query string” in Google Sheets (quotes, dates, Col vs letter)QUERY returns Unable to parse query string when a text literal has no single quotes, a date has no date 'yyyy-mm-dd', or you used Col1 on a sheet range (or A on an array).

Fix QUERY #VALUE! in Google Sheets (dates mixed with text)QUERY year(A) returns #VALUE! when the column mixes dates with text like TBD. Recreate year() on the mix, then a helper column — this is not locale parse, not Col1 vs A, and not 10 + pcs.

Fix QUERY #N/A in Google Sheets (the query returned no rows)QUERY returns #N/A when WHERE matches nothing. Recreate an empty result, then IFNA around QUERY versus fixing the WHERE — not a parse error, not mixed dates, not Col1 vs A.

Fix QUERY group by / aggregation errors in Google Sheets (AVG_SUM_COLUMN, mixed types, missing LABEL)QUERY GROUP BY fails when you SUM a mixed column, skip aggregating a selected field, or omit LABEL. Recreate text in a numeric agg — not QUERY parse, not mixed dates, not no rows.

QUERY WHERE AND/OR returns extra rows when parentheses are missingQUERY binds AND tighter than OR. where A = 'East' or A = 'West' and B > 5 keeps a small East row. Parentheses are the fix. Not a parse error and not an empty WHERE.

QUERY GROUP BY still returns a label row or Col1 headersA working SUM and GROUP BY spills a header such as sum qty or sum Col2. The label clause renames it. offset 1 drops that row. Not AVG_SUM_ONLY_NUMERIC.

Fix QUERY contains / matches mistakes in Google Sheets (wrong quotes, case, or regex)QUERY contains is a case-sensitive substring; matches is a full-string regex. Recreate Pro vs Processor vs pro — not QUERY parse string, not empty WHERE.

Fix QUERY PIVOT errors in Google Sheets (mixed types or missing GROUP)QUERY PIVOT fails when the aggregated column is mixed, or a selected field is neither grouped, aggregated, nor pivoted. Recreate TBD in tickets and select A,B pivot C — not GROUP BY primer, not parse quotes.

Fix QUERY LIMIT / OFFSET mistakes in Google Sheets (not empty WHERE)QUERY LIMIT 0 or OFFSET past the table returns empty #N/A; LIMIT is not WHERE. Recreate offset 10 on four rows and limit 0 — not QUERY no rows WHERE 999.

QUERY SELECT * shifts columns after you insert a fieldQUERY select * follows the range by position. Insert a column inside that range and the spill gains a field with no #REF!. Name columns, or pick them by header after the query.

QUERY LIMIT and OFFSET skip the wrong block or drop the headerQUERY offset 10 limit 10 returns data rows 11–20, not 10–19. headers 0 lets OFFSET skip the label row. An offset past the table is the empty-output page.

QUERY PIVOT stalls or truncates when the key has too many valuesEach distinct QUERY PIVOT key becomes a column. Two skus finish. A key per row tries to build a very wide table, sits on Loading, or hits the calculation limit. Mixed-type PIVOT errors are the other page.

QUERY WHERE strings: quotes, apostrophes, and a formula that will not parseA QUERY text value uses single quotes. O'Brien needs a doubled apostrophe. A double quote inside the formula is #ERROR! Formula parse error, not the QUERY tooltip.

QUERY date literals must be date 'yyyy-mm-dd'QUERY compares dates with the date keyword and a yyyy-mm-dd literal. A bare date, double quotes, a text compare, or a concatenated serial does not.

QUERY LABEL and SELECT aliases fail when Col1 is mixed with column AQUERY has no SELECT AS alias. LABEL must name Col1 on an array and A on a sheet range. Mixing them is NO_COLUMN. Not the select-A primer alone.

QUERY LABEL fails when a comma or a Col reference is missinglabel Col1 'Name' Col2 'Qty' does not parse. Pairs need a comma. label Col3 on a two-column array is NO_COLUMN. label must sit after order by. Not the AS alias.

QUERY ORDER BY a label alias is NO_COLUMNorder by units fails when units is only a label. Google's query language does not accept a label in place of an id. Order by sum(B), then label that same expression. Put order by before label.

QUERY CONTAINS treats the pattern as literal text; MATCHES treats it as a full-cell regexcontains 'a.c' keeps the cell a.c and skips abc. matches 'a.c' keeps abc and a.c, and skips abc extra, because the pattern must match the whole cell. contains '.*' looks for a dot and a star.

QUERY STARTS WITH is case-sensitive and misses Applestarts with 'a' returns apricot and skips Apple. The empty case is #N/A, Query completed with an empty output. lower(A) starts with 'a' keeps both. The one-word startswith does not parse.

QUERY year(Col1) fails on a mixed array, and month(Col1) is 0 for Januaryyear(Col1) on dates mixed with a formula blank is #VALUE!: the column is not a Date. A blank from ="" is text. After the column is dates, month() returns 0 for January, not 1.

QUERY SELECT Col1 + Col2 fails when the column is textB+C in a QUERY select needs two number columns. Text-looking qty, stored with a leading apostrophe, makes the addition #VALUE!. A helper with VALUE, then add. sum() of mixed types is the GROUP BY page.

QUERY still returns rows a sheet filter has hiddenData → Create a filter can hide West. QUERY and SUM still include that row. SUBTOTAL 109 drops it. QUERY has no switch for visible rows only.

QUERY format patterns use a dot decimal, and the formatted column comes back as textformat B '#,##0.00' prints 1,234.50 as text, so adding 1 is #VALUE!. where B > 10,5 is Unable to parse query string. The sheet locale does not rewrite the query string.

QUERY headers 0 and 1 decide whether the first row is a label or a data rowheaders 1 on a range that starts at the first sku makes that sku the label. headers 0 on a range that includes sku treats sku as data. IMPORTRANGE uses the same argument and Col1, not A.

FILTER around QUERY treats the header row as data and the heights stop matchingQUERY with headers 1 returns a label row. FILTER then compares that label, or pairs a 3-row result with a 4-row condition, and the stack errors or shifts. headers 0 and 1 alone are the other page.

ARRAYFORMULA around QUERY does not run the query once per rowQUERY already spills. Wrapping it in ARRAYFORMULA returns the same block. Concatenating a range into the query string is #VALUE!, because parameter 2 has to be one string.

QUERY PIVOT and GROUP BY parse only in that clause ordergroup by comes before pivot. pivot then group by is Unable to parse query string. The pivot key cannot also be listed in group by. A legal pair is select A, sum(C) group by A pivot B.

QUERY format datetime patterns use MM for month and mm for minutesformat A 'yyyy-mm-dd hh:mm' is not ignored. mm is minutes and hh is a 12-hour clock, so May 15:04 can display as month 04 and hour 03. The query string does not follow the spreadsheet locale. The result is text.

QUERY avg of text-looking numbers is not the average of the digits you seeLeft-aligned qty is text. avg is #VALUE! with AVG_SUM_ONLY_NUMERIC when text is the column type. When the only real numbers are zeros, avg returns 0 and the text digits never entered. VALUE, then avg.

QUERY IS NULL misses a formula that returns an empty stringwhere B is null keeps a cell you never typed in. A formula that returns "" is not that cell. ISBLANK is FALSE there. where B = '' is the empty-string test. A space matches neither.

IMPORTXML

Fix IMPORTXML and IMPORTHTML in Google Sheets (Allow access, Result too large, still loading)IMPORTXML and IMPORTHTML fail on the Allow access banner, Result too large, or Loading… — recreate each on a blank sheet, then click the banner, narrow the XPath, or cut import count.

Fix IMPORTDATA errors in Google Sheets (could not fetch url)IMPORTDATA fails when the URL cannot be fetched or the file is not CSV/TSV. Recreate a dead URL and a non-CSV page — this is not IMPORTRANGE, not IMPORTXML, not IMAGE.

Fix IMPORTFEED errors in Google Sheets (fetch or parse, not CSV or XPath)IMPORTFEED needs a public RSS or Atom URL. Recreate a dead host, an HTML homepage, and a working feed query — not IMPORTDATA, not IMPORTXML, not IMPORTRANGE.

Fix IMPORTHTML “Resource at url contents exceeded maximum size” in Google SheetsThis tooltip means the URL’s file itself is over the import cap. Changing the table index does not shrink the download. Not Allow access, not Result too large.

Copying IMPORTXML down the sheet is not a backoff, and it spends the quota fasterEach live IMPORTXML counts toward the shared import quota. A column of retries, or a NOW gate that recalculates them, adds requests. There is no sleep argument. One call, then paste values.

Fix IMPORT failures in Google Sheets when a URL parameter is not ENCODEURLA space, ampersand, or hash inside a query value breaks IMPORTDATA, IMPORTXML, and IMPORTHTML. Encode the value only. Encoding the whole URL breaks the scheme.

IMPORTXML returns #N/A when the XPath matches nothing, and a broad path spills the wrong nodes//h1 on the Moon article spills the heading. //h9 is Imported content is empty. //a spills links, not the title. A capital H1 is not the same node.

IMPORTXML XPath misses namespaced elements until you use local-name()//title on an Atom document is #N/A, Imported content is empty. Those elements sit in a default namespace. local-name()='title' returns them. An HTML //h1 with no namespace still works.

IMPORTFEED leaves url or summary blank on an Atom feedIMPORTFEED can fetch an Atom feed and still spill an empty url or summary. RSS link and description fill those columns. The legal item fields are title, summary, url, and created. items content is not a query.

IMPORTHTML index 1 is the first table, and 0 or a list index is a different requestTable index 1 and table index 2 are not the same table. Index 0 and a missing index are #N/A. list is not table. Changing the index does not shrink a download that is already too large.

IMPORTHTML and IMPORTXML return #N/A when the table is drawn by JavaScriptA page you can see in the browser can still be #N/A if View Page Source has no table. Could not fetch url or an empty import is the result. Changing the index does not run the script.

IMPORTDATA splits on the delimiter it was given, and locale is not an encodingA comma CSV spills into columns. The same URL with a semicolon delimiter stays one column per row. A .csv of semicolons is split on commas if you omit the delimiter. There is no encoding argument.

IMPORTDATA stays on Loading or #N/A after a redirect or a 403A browser follows a 301 from http to the CSV. IMPORTDATA often returns #N/A Could not fetch url, or sits on Loading. A 403 is the same tooltip. The final https address that returns 200 is the fix. A dead host is the other page.

IMPORTDATA shows mojibake when the CSV is not the UTF-8 text you thinkA Windows-1252 or Latin-1 CSV lands with Ã characters or replacement marks. QUERY still addresses Col1, but a Türkiye literal misses the mojibake cell. The locale argument is not an encoding.

IMPORTHTML returns #N/A when the page has lists and you asked for a tableThe query table on a page with no table is #N/A, Imported content is empty. list on that page spills a list. list on a data table spills the menu, not the grid. Index 2 is the other page.

ARRAY

Fix ARRAYFORMULA 0 on empty rows in Google SheetsEmpty cells times 2 become 0 in ARRAYFORMULA. Putting 0 in IF still prints 0. Recreate the spill and a COUNT of those zeros, then use an empty string so the blank row looks blank.

Array literal { } parse error in Google Sheets (comma vs backslash, locale){A2,B2} is a Formula parse error when this spreadsheet’s locale uses a backslash as the array column separator. Recreate the braces, then type \ — this is not the VLOOKUP semicolon fix.

Fix BYROW / MAP LAMBDA errors in Google Sheets (#N/A or #VALUE! from a lambda that returns the wrong shape)BYROW and MAP error when the LAMBDA returns an array instead of one value, or MAP gets arrays of different size. Recreate BYROW(row, row) and mismatched MAP — not ARRAYFORMULA 0-on-empty.

BYROW errors when the LAMBDA returns two columns with HSTACKBYROW must return one value per row. HSTACK(SUM(row), AVERAGE(row)) inside the lambda is #N/A, Single value expected. HSTACK of two BYROW calls spills the two metrics.

Fix UNIQUE by_col and exactly_once surprises in Google SheetsUNIQUE’s second argument compares columns, not rows; the third returns values that appear exactly once. Recreate a wide header row and a doubled sku — not SORT/UNIQUE spill overwrite.

Fix SCAN / REDUCE LAMBDA shape errors in Google SheetsSCAN and REDUCE LAMBDA must return one accumulator value; extra names or a nested array error. Recreate SCAN returning {acc,v} and REDUCE with one name — not BYROW / MAP.

SCAN keeps adding across groups when the reset tests the wrong argumentSCAN calls the lambda as (accumulator, value). Renaming them does not swap the values. A test on A2 never sees the current group. Walk an index and reset when the group cell changes.

Fix ARRAYFORMULA with AND/OR in Google Sheets (one TRUE/FALSE for the whole column)ARRAYFORMULA(AND(...)) and OR(...) return a single TRUE or FALSE, so every row looks wrong. Recreate the collapse, then multiply or add the conditions — not empty-row zeros.

ARRAYFORMULA around IFS still returns only the first rowIFS inside ARRAYFORMULA evaluates the first row of the conditions and returns one cell. A later East row never spills. MAP runs IFS once per row. Not a scalar IFS miss, and not AND collapsing to one FALSE.

MAKEARRAY indexes start at 1, so a 0-based LAMBDA shifts every cellMAKEARRAY passes row and column indexes that start at 1. INDEX with r-1 hits row 0 and returns a nested array. INDEX with r+1 reads the next source row, so every output cell moves down one.

ARRAYFORMULA with IFERROR masks #DIV/0! and #N/A as blank or 0IFERROR or IF(ISERROR()) inside ARRAYFORMULA turns a column of errors into blanks or zeros. The sheet looks finished. Leave one unmasked formula so the error stays visible.

BYCOL includes the header row and returns #VALUE!BYCOL on A1:C4 runs the lambda on the text headers. SUM(col+0) is #VALUE! because mon cannot be added. Start at row 2. SUM/ROWS on the header row is a quiet wrong average.

LAMBDA and REDUCE hit the calculation limit in Google SheetsA LAMBDA that walks too many steps shows Calculation limit was reached, not a Formula parse error. A short REDUCE is the control. Simplify. Do not nest another copy.

ARRAYFORMULA hits the calculation limit or looks truncated in Google SheetsA large ARRAYFORMULA can sit on Loading… then stop with the calculation-limit tooltip. A short spill is the control. An IF blank mask is not a silent cut. Not the overwrite #REF!.

A recursive LAMBDA hits the calculation limit or #NUM!A named function that calls itself returns a short total, then Calculation limit was reached on a deep call. #NUM! is the same wall. Not a #NAME? and not a circular cell.

Excel spilled arrays become static values when the file opens in Google SheetsAn .xlsx dynamic array can arrive in Sheets as values. The anchor's formula bar is the test. A1# is an Excel spill reference. Re-enter FILTER or UNIQUE if the block no longer expands.

Legacy Excel CSE arrays become values, a parse error, or #NAME? in Google SheetsCtrl+Shift+Enter stored braces around an array formula. Sheets does not use that keystroke. {=SUM(IF(...))} is a parse error or a static value. _xlfn. is #NAME?. A1# dynamic spills are the other page.

FLATTEN vs TOCOL in Google Sheets: blanks, errors, and extra rangesFLATTEN stacks every value, including blanks, and can take several ranges. TOCOL has ignore and a scan direction. FLATTEN has neither. A blocked spill is still #REF!.

Fix TOCOL in Google Sheets when ignore 2 or 3 swallows errors or blanksTOCOL ignore 2 drops errors and keeps blanks. Ignore 3 drops both. A #DIV/0! in the source disappears from the stack. Scan-by-column changes order, not which values survive.

MAP errors when the LAMBDA returns more than one valueMAP must get one value back per cell. A LAMBDA that returns {h, h*2}, HSTACK, or FILTER is #N/A or #VALUE! (nested array). Sheets does not show Excel’s #CALC! for this. Different input sizes are the other page.

REDUCE with a missing or array initial_valueREDUCE needs a scalar initial_value before the array. Omit it and the call is the wrong number of arguments, or a blank seed is #VALUE!. A two-cell seed is a nested array. The product-of-zero case is the other page.

Fix UNIQUE in Google Sheets when exactly_once drops every repeated rowUNIQUE’s third argument keeps only rows that occur once. A sku that appears twice vanishes. Default UNIQUE keeps one copy. Whole-row identity decides what “once” means.

UNIQUE exactly_once keeps both East values when one has a trailing spaceexactly_once drops a row only when that exact text repeats. East and East plus a space each occur once, so both stay. TRIM makes them the same row, and then the flag drops them.

UNIQUE keeps different case and accents as different rowsUNIQUE treats Foo, foo, and José as distinct. It does not fold case or strip accents. LOWER and a small SUBSTITUTE map are the fold. exactly_once is the other page.

A case-sensitive sort puts Zebra before appleCharacter-code order places every ASCII capital before every lowercase letter. Plain SORT in Google Sheets usually keeps Apple next to apple. QUERY order by and a CODE key do not.

A named function with a LAMBDA inside hits the calculation limit on a wide mapDOUBLE_SUM on three cells returns a number. BYCOL of that named function across a large grid hits Calculation limit was reached. The name is valid. The embedded MAP is the extra work.

ARRAY_CONSTRAIN shows a corner of the array and does not say the rest was droppedARRAY_CONSTRAIN of a six-row FILTER to 3 rows shows three skus and no error. The other three are not on the sheet. Asking for more rows than exist does not pad. Zero rows errors.

RAND and RANDARRAY reshuffle the whole model on every editRAND, RANDBETWEEN, and RANDARRAY are volatile. One unrelated edit draws new numbers, charts jump, and a sheet full of them gets slow. A blocked RANDARRAY spill is the other page.

ARRAYFORMULA with NOW stamps the same time on every row, then updates itARRAYFORMULA(IF(A2:A="",,NOW())) does not timestamp a row when it is filled. NOW is volatile, so every non-blank row shows one clock and that clock moves on the next edit.

UNIQUE with by_col TRUE drops a duplicate column, so a day of numbers looks goneUNIQUE’s second argument TRUE compares columns, not rows. Two identical day columns collapse into one, with no error. Row dedupe and exactly_once are other pages.

TOCOL with scan_by_column TRUE reads down each column and reorders the listThe default walks each row left to right. TRUE walks each column top to bottom. The second value in the spill changes. Ignore codes do not cause that reorder. A blocked spill is the other page.

VSTACK of the raw range puts blank rows back next to a FILTER that had removed themFILTER drops rows that fail the test. VSTACK does not. Stacking the original block under a filtered block brings the blank names back. A FILTER that tests the city column also keeps a blank name. Width padding is the other page.

SEQUENCE treats 2024-01-01 as subtraction, so the series starts at 2022In a formula, 2024-01-01 is 2024 minus 1 minus 1, which is 2022. SEQUENCE then counts integers. DATE or a real serial is the start. A date serial used as the step jumps by tens of thousands of days. A negative row count is the other page.

Other

VLOOKUP returns the wrong row in Google Sheets (TRUE approximate match)TRUE approximate match — or omitting the fourth argument — returns the wrong row on an unsorted table. Recreate the wrong name and a missing sku that still returns a row, then pass FALSE.

Fix VLOOKUP after you insert a column in Google Sheets (index now points at the wrong field)VLOOKUP index 2 silently returns the wrong column after you insert a field inside the range. Recreate insert so index 2 is no longer role — not VLOOKUP #REF! index too big, not XLOOKUP vs VLOOKUP.

Fix VLOOKUP wildcard * and ? surprises in Google Sheets (partial match you did not want, or a literal asterisk in the key)VLOOKUP with FALSE still treats * and ? in the lookup value as wildcards. Recreate lookup N12* hitting N12 instead of the sku that actually contains a star — not spaces, not TRUE approximate.

Formula parse error in Google Sheets (comma vs semicolon, locale)VLOOKUP copied with commas is a Formula parse error when this spreadsheet’s locale uses semicolons as argument separators. Recreate VLOOKUP and IF, then type semicolons — this is not #NAME? and not the { } backslash fix.

Fix Formula parse error from curly quotes in Google SheetsQuotes pasted from Docs or Word are curly (“ ” or ‘ ’). Sheets shows #ERROR! Formula parse error. Retype straight quotes — this is not a comma-vs-semicolon locale.

Fix SUMIF/COUNTIF missing a number in Google Sheets (CHAR(160))COUNTIF and SUMIF skip a sku that looks like 101. LEN is 4 and TRIM stays 4. Recreate CHAR(160) next to an ordinary trailing space — the extra character is not an apostrophe and not CHAR(32).

Fix SUMIFS missing dates stored as text in Google SheetsSUMIFS skips invoice dates that look like dates but are text. Recreate apostrophe dates against a DATE criterion — not CHAR(160), not CSV text vs number.

IFERROR vs IFNA: hide #N/A without hiding real bugsOn the same sheet, IFERROR turns #N/A, #REF!, and #DIV/0! into none. IFNA only catches #N/A. Recreate both wrappers, then pick IFNA for lookups.

Fix IMAGE errors in Google Sheets (failed to load / invalid url)IMAGE fails on a bad URL, a 404, and a Drive permission miss in similar ways. Recreate a dead link and a working public PNG — this is not IMPORTXML and not a #REF! spill.

IMAGE of a Google Drive link stays blank until the file is sharedIMAGE of a Drive /view link stays blank. A uc or thumbnail URL still stays blank while the file is Restricted. Anyone with the link is the sharing step. A dead host is the other page.

COUNTIF date criteria in quotes count 0 against real date serialsCOUNTIF with ">2024-01-01" in quotes compares text and returns 0. Concatenate ">" with DATE or a date cell. Text that only looks like a date is the SUMIFS page.

REGEXEXTRACT returns the first match and ignores the rest of the cellREGEXEXTRACT on a cell with two emails returns the first one. There is no error. Parentheses still describe that first match. Peel every match with REGEXREPLACE and SPLIT, then keep pieces that match.

A Commenter can see formulas and cannot edit themShare as Commenter and the formula bar is read-only. Typing and paste do not change the cell. There is no #REF!. Editor is the role that can edit. A protected range is a different lock.

SPARKLINE goes flat or disappears on blanks and #N/ASPARKLINE of numbers draws a line. Blanks skip or sag. A #N/A in the range turns the cell into #N/A or a chart that drops out. Plot only numbers. An empty range and a bad charttype are the other pages.

INDEX/MATCH with search type 1 returns the wrong row on unsorted keysMATCH type 1, or an omitted type, is approximate and expects ascending keys. On an unsorted list it is #N/A or a nearby row, with no error when it hits the neighbor. Type 0 returns the exact price. Sorted bands are a different page.

HYPERLINK opens for editors and tells viewers they need accessA HYPERLINK to a Drive file shows the label for everyone. Viewers who click get You need access. Editors of the spreadsheet are not viewers of that file. The cell is not #VALUE!. An empty URL is the other page.

A sheet filter hides FILTER spill rows for everyone elseData → Create a filter hides rows. A FILTER formula’s spill sits on those rows, so the visible list looks short. The formula still returned every match. Turn the filter off. SUBTOTAL is the function that skips filtered rows on purpose.

Fix SPARKLINE errors in Google Sheets (empty range or wrong argument)SPARKLINE fails on an empty range or a bad option. Recreate both, then a working mini chart in the next cell — not IMAGE, not GOOGLEFINANCE.

Fix REGEXREPLACE unchanged text or #N/A in Google Sheets (pattern miss vs extract)REGEXREPLACE returns the original text when the regex does not match — not #N/A. Recreate a miss, a capturing replace, and an invalid pattern — not REGEXEXTRACT #N/A.

Fix TO_DATE surprises in Google Sheets (serial vs text, not DATEVALUE locale)TO_DATE turns a number into a date serial; text is returned unchanged, and 10/10/2000 is division. Recreate a serial, a leftover string, and the slash trap — not DATEVALUE #VALUE!.

Fix XLOOKUP search_mode -1 / 2 surprises in Google SheetsXLOOKUP search_mode -1 returns the last match; mode 2 binary-searches and needs sorted data. Recreate two Widgets and an unsorted binary lookup — not XLOOKUP exact miss, not XMATCH.

Fix IF blank vs 0 vs empty-string traps in Google SheetsIF(A2, …) treats blank and 0 as FALSE; IF(A2="", …) is not the same as IF(A2=0, …). Recreate hours 0 vs empty vs formula "" — not ISBLANK, not ARRAYFORMULA 0-on-empty.

Fix ISBLANK vs empty string vs IFBLANK in Google SheetsISBLANK is FALSE for a formula that returns ""; Sheets has no IFBLANK (that is #NAME?). Recreate a truly empty cell vs ="" vs a space — not IF(A2=0), not ARRAYFORMULA 0.

VLOOKUP TRUE on a quantity break: wrong price if the first column is unsortedVLOOKUP with TRUE, or with the fourth argument omitted, is an approximate band. It is correct only when the first column is sorted. This is not the exact-miss VLOOKUP page.

COUNTIF treats * ? and ~ as wildcards when you wanted the literal skuCOUNTIF patterns use * for any text, ? for one character, and ~ to escape. Counting N12* matches every N12 prefix unless you escape the star.

SUMIF skips text that looks like numbers in the sum rangeSUMIF adds real numbers and ignores text in the sum range, so a total is short with no #VALUE!. Criteria can still match a text-looking key.

SUMPRODUCT of TRUE and FALSE stays 0 until you coerce themA comparison inside SUMPRODUCT can total 0 when TRUE and FALSE are never turned into 1 and 0. Multiply by 1 or use a double unary. Not the different-size #VALUE!.

Dates stored as text sort wrong and break XLOOKUP and MATCHA left-aligned date is text. Sort A to Z follows characters, not the calendar. XLOOKUP and MATCH miss a real date key. Not a SUMIFS-only miss and not Locale alone.

Split text to columns turns 03/04 into the wrong month or dayData, Split text to columns breaks a date string on the slash and does not ask MDY versus DMY. DATE of the pieces follows the order you pick. Locale does not reshuffle them afterward.

NOW and TODAY follow the spreadsheet time zone, not your clockNOW() and TODAY() can jump a calendar day when File, Settings, Time zone is hours away from you. The laptop clock does not drive those functions. Not a bad date format.

A protected range blocks the edit: You are trying to edit a protected cellDouble-clicking a protected formula shows You are trying to edit a protected cell or object. Warning-only protection still offers Edit anyway. Not a locked formula bar from Commenter access.

Editors still cannot type in Except certain cells when another protection overlapsA sheet rule that excepts A2 does not cancel a range rule set to Only you on A2. The editor still gets You are trying to edit a protected cell. Remove the overlapping range rule. Warning-only is the other page.

A protected range set to Show a warning still saves the editThe dialog says you are editing a part of the sheet that should not be changed accidentally, and it offers Edit anyway. That click writes the cell. Restrict who can edit is the hard block, with no Edit anyway button.

A protected range description is a sidebar label, not the lock dialogThe description on Data, Protect sheets and ranges shows in the owner's rule list. Editors who hit the cell see the standard protected-cell dialog or the warning dialog. The description is not that text.

Filter views and sheet filters show different rows to collaboratorsData, Create a filter hides rows for everyone. Data, Filter views hides them only in your view. A shared link with fvid opens that view. Not FILTER the function.

A named filter view is gone after the filtered column is deletedData, Filter views lists a saved name until that view is removed or the sheet structure it stood on is deleted. Closing the view bar does not delete the name. A temporary filter view was never saved there.

A sort inside a filter view can reorder the sheet for everyoneFiltering in a filter view hides rows for you. A sort from that view can rewrite row order on the sheet, so other people see the new order after you close the view. Check version history. A private order is the SORT function on another range.

A pivot table stays on old totals after the source changesAn edit inside the pivot range updates in Google Sheets. A row pasted below a fixed range does not. Excel's pivot cache stays stale until Refresh. Not GETPIVOTDATA.

A slicer does nothing when its range is not the pivot sourceInsert, Slicer filters pivots and charts that use the same range. A slicer pointed at a copy of the data leaves the pivot totals unchanged. Widen the pivot, or point the slicer at the pivot range. Not GETPIVOTDATA.

One slicer moves every pivot that uses its range, and only thoseA slicer has no per-pivot checklist. Two pivots on A1:B4 both follow a slicer on A1:B4. A pivot on a copy of the data does not. Grouping chart objects does not attach the slicer.

A pivot’s own filter does not follow the slicer, and the slicer is not private to one pivotFilters in the pivot editor apply to that pivot only. A slicer applies to every pivot that uses its data range. Clearing the slicer does not clear the editor filter. The two filters stack.

Charts gap or drop to zero where the series has #N/A, #DIV/0!, or #VALUE!A line chart skips error cells. IFERROR to 0 pulls the line down to the axis. #N/A stays a gap. Not a SPARKLINE charttype miss and not a broken Insert menu.

A combo chart looks flat until the small series is on the right axisColumns in the thousands and a rate near zero share one axis, so the rate sits on the floor. Chart editor, Series, Axis, Right axis gives the rate its own scale. Not a gap from #N/A.

Pie chart labels show the raw values, or 0, until the slice label is PercentageSlice label Value prints the cell. Percentage prints that slice's share of the pie. Decimal places 0 turns 0.2 into 0. A percent format on the column is not the slice label.

A chart trendline shows no R² until Show R² is checkedCustomize, Series, Trendline turns the line on. Show R² is under that checkbox, not above it. The label is on the chart, not in a cell. LINEST is the cell. Pie charts have no trendline section.

Waterfall connectors float when a total is plotted as another changeA waterfall adds the value column as steps. A final row that already holds the sum is added again, so the connector leaves the axis. Connectors are one style for the whole chart. Text and formula blanks break the running base.

An installable Apps Script trigger needs authorization again after Make a copyFile, Make a copy copies the script and drops installable triggers. The copy stays quiet until you add the trigger and authorize. Authorization is required is not #NAME?.

A simple onEdit cannot call UrlFetchApp, and the sheet stays quietA function named onEdit runs as a simple trigger. It can write a neighbor cell. It cannot call UrlFetchApp. The cell does not change, and the execution log says you do not have permission. An installable trigger under another name can, after you authorize.

Utilities.formatDate uses the script time zone, not the spreadsheet time zoneApps Script project settings have a time zone. File, Settings, Time zone is a different clock. A formatted string can sit hours away from TEXT(NOW()) on the sheet. Return the spreadsheet zone to formatDate.

Gemini in the Sheets side panel: quota, region, plan, or adminAsk Gemini and Help me organize can be missing or stopped by plan, admin, language, region, or a quota banner. This page does not promise the panel opens for you.

Explore can answer with column names you already renamedThe Explore panel is not a formula. After you rename a header, a suggestion can still say the old name. There is no refresh that this page can guarantee. QUERY and a pivot read the current header. The panel may be missing or replaced.

FILTER blank cells, empty strings, and spaces are not the same testFILTER with a criterion of empty string keeps truly blank cells and formulas that return empty text. A space stays out. This is not the no-match FILTER page.

FILTER OR is addition; extra arguments are ANDFILTER treats each condition argument as AND. OR of two arrays collapses to one boolean and the tooltip is mismatched range sizes. Add the conditions for either-or.

COUNTBLANK and COUNTIF of an empty string count the same blanksCOUNTBLANK and COUNTIF(range, empty string) both count untouched cells and formulas that return empty text. Neither counts a space or a zero. They are not two definitions.

NPV treats the first cashflow as end of period 1, not time zeroPutting the opening outlay inside NPV discounts it by one period. The cell stays a number. Time zero belongs outside the function. Not investment advice.

Locale semicolons stay even when function names are EnglishA comma-decimal locale separates arguments with semicolons. Always use English function names does not switch those separators. Pasted commas are still Formula parse error.

setFormula with commas is a Formula parse error on a semicolon localerange.setFormula('=SUM(A1,B1)') stores commas. On a locale that separates arguments with semicolons, the cell is #ERROR! Formula parse error. The formula bar still shows the commas. English function names do not change the separator.

Decimal-comma locales reject a period inside numbers and TEXT patternsOn a comma-decimal locale, 1,5 is the number and a period in 1.5 is not that decimal mark. TEXT patterns use the locale mark too. Argument separators are a different bug.

Conditional formatting formulas lock the wrong row when both $ are absoluteA custom formula is written for the top-left cell of the range and fills from there. $A$2 tests only A2 for every row. $A2 keeps the column and lets the row change.

A conditional formatting custom formula paints only the first column of a wide rangeThe apply-to range can be A2:C6 while =A2="open" fills column A only. Each column shifts the relative reference. $A2 keeps the test on column A and paints the row. Locking the row with $A$2 is the other page.

Conditional formatting Text contains does not read a regular expressionText contains searches for the characters you typed, including a caret. The pattern ^East matches a cell that contains those five characters, not cells that start with East. REGEXMATCH in a custom formula is the regex.

A color scale does not paint a text columnConditional formatting, Color scale maps numbers to a gradient. Status words stay unpainted. Text is exactly, one rule per word, is the categorical color. A color scale has no text stops.

The first true conditional formatting rule is the one that paintsRules run in the order listed. The first true rule sets the format. A later rule does not override it. Sheets has no separate stop-if-true checkbox. Drag the rule you want above the one that is winning.

Data validation rejects a good number when the custom formula is missing a $A rule on C2:C10 written as =C2<=D1 is tested on the next row as =C3<=D2. Blank D2 rejects a value that is under the cap in D1. Reject the input does not keep the number. Warning-only validation would.

A dropdown shows a blank or the old items after the source range movesInserting a row inside the list range puts a blank choice in the dropdown. Cutting the list to another column leaves the rule on the old address, so the menu is empty while the words sit elsewhere.

Show a warning keeps the value; Reject the input does notData validation, Advanced options: Show a warning saves the typed value and marks a triangle. Reject the input shows a dialog and the value does not stay. Help text is not the reject switch.

A checkbox is boolean TRUE; a list item TRUE is textInsert, Checkbox stores TRUE and FALSE as booleans. A dropdown item TRUE is the text TRUE. A2=TRUE is FALSE for the text. COUNTIF on the boolean misses the word. Custom checkbox values can store text on purpose.

Merged cells make VLOOKUP and SUMIF see only the first cellA merged dock label stores the text in the top-left cell. The other rows are blank. VLOOKUP and SUMIF miss them. Unmerge and fill. Not a type mismatch.

A SPARKLINE stays the default dark line when color or rtl never appliedThe option coulour is #N/A, the same class as colour. A line with no color pair is the default dark chart. rtl wants the boolean TRUE. The word yes does not reverse it. Not an illegal charttype.

IMAGE mode, height, and width: a blank-looking cell or a stretched pictureIMAGE mode 4 needs height and width. Mode 5 is an error. Mode 2 stretches to the cell. Mode 1 in a short row looks blank. A dead URL is the other page.

IFERROR hides the real error in Google Sheets: how to see the causeIFERROR turns #N/A, #REF!, #DIV/0!, and #VALUE! into one fallback. Peel the wrapper into a scratch cell and read the raw error before you change the formula.

ISERROR, ISNA, and ISERR each miss a different errorISNA is only #N/A. ISERR is every error except #N/A. ISERROR is every error. Picking the wrong one leaves a miss or a #DIV/0! looking like a clean FALSE.

ERROR.TYPE turns each error into a number you can filterERROR.TYPE maps #DIV/0! to 2, #VALUE! to 3, #REF! to 4, #NAME? to 5, #NUM! to 6, and #N/A to 7. A cell with no error makes ERROR.TYPE itself return #N/A.

COUNTA counts a formula blank and a zero as presentCOUNTA counts text, zeros, spaces, and formulas that return an empty string. A truly empty cell is the one it skips. The count is larger than the labels you can see.

COUNTUNIQUE counts one formula blank, so the category total is one highTruly empty cells are ignored. A formula that returns "" is one extra unique, no matter how many rows show it. Auditors who want real categories have to drop that blank first.

MINIFS and MAXIFS return 0 when nothing matchesNo row passes and MINIFS or MAXIFS shows 0, the same 0 as a real minimum of zero. COUNTIFS of the same criteria tells the two zeros apart. A size mismatch is #VALUE!.

SUMIFS wildcards match more rows than an exact criterionSUMIFS treats * and ? as wildcards. East* adds Eastern. A sku criterion N12* adds every N12 prefix. The literal star is N12~*. Equal ranges that miss are 0.

SUBTOTAL 9 and 109 do not treat hidden rows the same wayFiltered-out rows drop out of SUBTOTAL 9 and 109. Manually hidden rows stay in 1–11 and drop out of 101–111. SUM of the same cells ignores both kinds of hiding.

SUBTOTAL 9 includes rows you hid by hand; 109 leaves them outThe function_num picks the aggregate, and adding 100 picks manual-hide behavior. 9 and 109 are both SUM. 1 and 101 are both AVERAGE. A filter is skipped either way.

RANK.AVG and RANK.EQ score a tie differentlyTwo scores tied for second: RANK.EQ gives both 2 and the next score 4. RANK.AVG gives both 2.5. RANK matches RANK.EQ. Neither result is an error.

XNPV returns an error when dates and amounts differ in lengthXNPV pairs each amount with a date by position. Three amounts and two dates do not pair. A matched schedule at 10% returns a number. Text dates are #VALUE!.

PMT and IPMT are negative when the present value is positivePMT of a positive loan balance is a negative payment. Flipping the present value flips the sign. IPMT uses the same sign. The figure is cash direction, not a broken rate.

SPLIT treats each character of the delimiter as its own cut unless split_by_each is FALSESPLIT of other-the-note on the delimiter the shreds t, h, and e. split_by_each FALSE cuts only on the word the. A doubled colon hides the bug when empty pieces are dropped.

SPLIT does not treat the delimiter as a regular expressionThe delimiter \s+ is the characters backslash, s, and plus, not “one or more spaces.” The s inside east is a cut. A period does not mean any character. Use REGEXREPLACE to normalize, then SPLIT on a literal mark.

SUBSTITUTE replaces every match unless you pass the occurrence numberSUBSTITUTE of each hyphen in N-12-E-7 is N|12|E|7. Occurrence 2 is only the second hyphen: N-12|E-7. There is no last-only flag. Instance 0 is a different page.

REGEXMATCH is case-sensitive until the pattern starts with (?i)REGEXMATCH of pro against Widget Pro is FALSE. The same pattern with (?i) is TRUE. (?i)B2 searches for the letters B2. An invalid pattern is #N/A, not FALSE.

CLEAN removes ASCII 0–31 and leaves NBSP and zero-width charactersCLEAN strips a tab and a line break. CHAR(160) and CHAR(8203) stay, so LEN is still one longer and VLOOKUP still misses. TRIM does not remove them either.

Power Query steps are dropped when an Excel file opens in Google SheetsAn .xlsx Power Query lands in Sheets as the last cached values. There is no query to refresh. Rebuild with IMPORTDATA, IMPORTRANGE, or QUERY. This is not a broken spill formula.

A cell shows 05/06/2024 while the formula bar is the other dayTyping 05/06/2024 stores the spreadsheet locale’s month/day order. A custom dd/mm format then paints the other reading. The serial did not move. DATEVALUE of text is the other page.

TEXT with "$#,##0.00" keeps a dollar sign the locale did not ask forThe $ in a TEXT format is a dollar sign, not the spreadsheet currency. A UK or EU file still prints $. Put the symbol you want in the pattern. A bad pattern is the other #VALUE! page.

IFERROR replaces the error before ERROR.TYPE can classify itERROR.TYPE(IFERROR(1/0,"ok")) is #N/A, not code 2. IFERROR already turned #DIV/0! into text. Classify the original cell. The code map is the other page.

TO_PERCENT shows 2000% when the cell already displays a percentTO_PERCENT is percent format with 1 = 100%. A cell that stores 20 and already looks like a percent displays 2000% again. A stored 0.2 displays 20%. Multiplying by 100 first is the same double scale.

N() turns text into 0, so a SUMPRODUCT check still looks validN("abc") is 0 with no error. Inside SUMPRODUCT that zero is indistinguishable from a real zero flag, so a bad label is counted as not-true.

T() returns a blank when the cell is a numberT keeps text and turns numbers, including numeric IDs, into an empty string. LEN is 0. A lookup or a join then runs on a blank key.

ROUND(1.005, 2) and a scaled 1.005*100 do not land on the same pennyThe double stored for 1.005 sits a hair under 1.005. ROUND of the typed literal is often 1.01. ROUND of the raw product 1.005*100 can snap to 1. Round an integer scale instead.

MOD(0.3, 0.1) returns a long 0.0999… instead of 00.3 and 0.1 are not exact in binary floating point. Their remainder is a hair under 0.1, not 0. Integer tenths, then MOD, then scale back, return 0.

QUOTIENT truncates toward zero; INT of a negative division does notQUOTIENT(-7, 2) is -3. INT(-7/2) is -4. They match on positive numbers. TRUNC follows QUOTIENT. A zero divisor is #DIV/0! on another page.

SUMIF and SUMIFS only see the top-left cell of a merged categoryA merged region label stores the word in the top-left cell. The rows underneath are blank. SUMIF adds one qty. SUMIFS with a second condition still misses them. A criterion pointed at the blank half of a merge is 0.

COUNTIFS of a date plus a text wildcard returns 0 when the types do not meetA quoted date criterion does not match date serials, and a wildcard does not match those serials either. Both criteria have to face the right column or the count stays 0.

MAXIFS AND-combines criteria, so East or West comes back as 0Two criteria on the same column must both be true. A row cannot be East and West. MAXIFS returns 0. Take the MAX of two MAXIFS calls, or FILTER with addition.

MAXIFS returns the max number, not the rest of that rowMAXIFS can tell you the largest qty. It cannot return the rep who sold it. FILTER or SORTN returns the row. Ties stay in FILTER and collapse in SORTN unless you ask for them.

A SUBTOTAL grand total skips nested SUBTOTAL rows, so it looks too smallSUBTOTAL ignores other SUBTOTAL results in its range. Detail 10 and 20, a subtotal 30, and a 40 add to 70, not 100. SUM includes the 30 and double-counts.

A pivot calculated field multiplies the totals, and a cell reference is not a field='qty'*'price' on a grouped pivot is sum of qty times sum of price, not the sum of the line totals. =B2*C2 is not a source column. A helper column is the row-level product.

Pivot Show as % of column snaps back to the raw sumsShow as % of column labels each row as a share of the column. % of row on a single value column is 100% per row. Removing the value and adding it again returns Show as to Default, so 60% becomes 30.

SORT uses the first column you name as the primary keySORT(range, 2, TRUE, 1, TRUE) sorts by column 2, then by column 1. The first pair is the primary key. TRUE belongs to the column index in front of it.

Parallel Apps Script runs overwrite a cell when they never take a LockService lockTwo triggers can read the same counter, add one, and write back. One update disappears. A lock only orders scripts that actually acquire it. A person typing in the cell is not locked.

CacheService drops a stored result when the TTL ends, and the sheet flickerscache.get returns null after the expiration, or earlier if the entry was evicted. A script that writes that null onto the sheet clears the block. The maximum TTL is six hours. This is not IMPORTRANGE and not a custom-function cache.

UrlFetchApp with followRedirects false stops on the short link and never returns the final pageA 301 or 302 is the response when followRedirects is false. The body is not the page you wanted. The default is true, and even then a login wall or a hop limit can still fail. Custom functions cannot call UrlFetchApp.

A script reads a formula it just wrote and gets the old value until SpreadsheetApp.flushsetValue then getValue on a dependent formula can return the value from before the write. flush applies pending changes in this execution. It does not lock other runs, and it does not make every read stale.

Help samples use commas, and a semicolon locale reports Formula parse errorGoogle's function pages and most US write-ups separate arguments with commas. A comma-decimal locale separates arguments with semicolons. The sample is not broken. The paste does not match this file. English function names do not change the separator.

TODAY flips the dashboard at the spreadsheet midnight, which may be UTCA file set to GMT changes the calendar day at UTC midnight. A New York business sees the flip in the evening. The laptop clock does not pick the midnight. One spreadsheet has one time zone.

IFERROR of an error with "" is not a chart gap, and IFNA does not blank the same cellsIFERROR(x, "") writes an empty string. ISBLANK is false. A line chart can treat that string differently from a true blank and from #N/A. IFNA leaves #DIV/0! in place, so the chart still gaps there.

ROUNDUP on a negative number moves away from zero, not toward positive infinityROUNDUP(-1.2, 0) is -2. ROUNDDOWN(-1.2, 0) is -1. On positive numbers, away from zero looks like up. On negatives, away from zero is more negative. A finance rule that says toward +∞ is a different function.

MOD(-3, 2) is 1, because the remainder takes the sign of the divisorSheets MOD matches the divisor's sign. MOD(-3, 2) is 1, not -1. A remainder built with QUOTIENT is -1, which is what some languages' % operator returns. A zero divisor is #DIV/0!.

SUMIFS puts the sum range first, so a SUMIF-shaped call totals the wrong columnSUMIF is criteria range, then criterion, then sum range. SUMIFS is sum range first, then pairs of criteria range and criterion. Swapping two numeric columns returns a plausible total with no error. Putting the criterion in the range slot is #VALUE!.