A #VALUE! error when multiplying in Excel usually means at least one value in the calculation is text or another kind of input Excel cannot use as a number. Check the cells being multiplied for words, hidden spaces, or an existing error; then correct or convert the problem value before multiplying again.
What the error means
Excel’s multiplication operator, the asterisk, expects values it can interpret as numbers. A cell that looks numeric may actually contain a label, an unrecognized number format, or invisible characters. For example, the entries 12 and 3 multiply normally, but a value such as “12 units” is not a number Excel can multiply directly.
With 12 in A2 and 3 in B2, the formula is =A2*B2; it returns 36. If B2 instead contains text that Excel cannot convert to a number, the same calculation can return #VALUE!. The error is not a statement that multiplication is mathematically impossible; it points to an input or expression Excel cannot evaluate as written.
Excel may automatically coerce text that looks like a number in some arithmetic calculations. That behavior does not make every text entry safe: spaces, symbols, imported data, and regional number formats can prevent conversion. A reliable fix starts with identifying the actual cell content rather than assuming that every number stored as text behaves the same way.
Find which input is causing it
Start with the formula that displays the error. Select its cell and look in the formula bar to see which cells it references. If the formula multiplies ranges or contains several operations, temporarily test its individual inputs in empty cells. That narrows the search without changing the original data.
With the suspected value in A2, the formula =ISNUMBER(A2) returns TRUE if Excel recognizes A2 as a number and FALSE otherwise. This test checks the cell’s underlying value, not just whether its display resembles a number. A cell formatted to show two decimal places can still contain text.
Also check whether an input already contains an error such as #N/A or #VALUE!. Multiplication normally passes an existing error through to the result. Select each referenced cell, inspect its formula bar, and follow any earlier formulas if necessary. If the calculation refers to a large range, work backward from the cells included in the failing expression rather than changing unrelated formatting.
- Confirm the formula points to the intended cells, especially after copying it to another row.
- Look for words, currency symbols typed into the value, leading apostrophes, and blank-looking cells that may contain spaces.
- Check whether a referenced cell already displays an error.
- Test suspect values with
ISNUMBERbefore changing the whole column.
Common causes in imported or typed data
Text mixed into a numeric column is a frequent cause. Entries such as “pending,” “not available,” or “12 kg” are meaningful to a person but are not plain numbers. If a calculation should exclude these entries, decide whether to leave the result blank, display a message, or correct the source data. Silently treating an invalid entry as zero can make a total or report misleading.
Hidden characters are another source of trouble. Data copied from websites, reports, or other systems may include leading or trailing spaces, nonbreaking spaces, or nonprinting characters. A cell can look like 12 while containing a character that prevents Excel from reading it as a number. Ordinary formatting changes do not remove characters from the cell.
Regional separators matter too. Depending on regional settings, a decimal may be written with a period or a comma, and thousands separators can differ. Text such as 1,25 may not be interpreted as intended in a workbook configured for a different convention. Do not replace punctuation blindly: first establish what the source means, then use a conversion that matches its decimal and grouping separators.
A leading apostrophe can make an entry text, and a formula may itself return text rather than a number. For example, a formula that joins a number to a unit label creates a text result. The visible number alone does not make the entire cell numeric. Dates and times, by contrast, are stored as numbers in Excel, so multiplying them may calculate successfully even when the displayed result looks surprising; that is generally a format or logic issue, not a #VALUE! input error.
Convert numeric text safely
If a cell contains a clean numeric value stored as text, convert it at the source when practical. Select the affected cells and look for Excel’s error indicator; where offered, its menu may include a conversion to a number. Another option is to use Data > Text to Columns and complete the wizard without changing the delimiter settings, but review the preview and destination first because this can change the selected data.
For a formula-based conversion, with numeric text in A2 and B2, use =VALUE(A2)*VALUE(B2). If A2 contains text “12” and B2 contains text “3”, the formula returns 36. VALUE converts text that Excel recognizes as a number; it will not repair arbitrary words or every unwanted character. Keep the original data until you have checked the converted results.
When the source uses known separators, NUMBERVALUE can make the conversion explicit. With A2 containing text in US-style format, such as 1,234.5, and B2 containing 2, use =NUMBERVALUE(A2,".",",")*B2. It returns 2469. The second argument identifies the decimal separator and the third identifies the grouping separator. Set these to match the source text, not simply the computer’s usual display convention.
If nonbreaking spaces are the problem, remove them before converting. With A2 containing text with possible ordinary or nonbreaking spaces and B2 containing a number, use =VALUE(TRIM(SUBSTITUTE(A2,CHAR(160),"")))*B2. This removes character 160, trims ordinary extra spaces at the ends, converts the remaining text, and multiplies by B2. It is a targeted cleanup, not a universal way to remove every unusual character; inspect the result if imported text remains invalid.
Choose how invalid entries should be handled
A formula should reflect what an invalid input means in the worksheet. Wrapping every calculation in an error-suppression formula may make a sheet look tidy while hiding bad data. For a one-off check, it is often better to let the error remain visible until the source is fixed. In a report, a clear message can be more useful than an error code, provided someone can still find and correct the underlying entry.
With the intended values in A2 and B2, use =IFERROR(A2*B2,"Check input") to show “Check input” for any error in the calculation; when A2 is 12 and B2 is 3, it returns 36. IFERROR also catches errors other than #VALUE!, including reference and division errors. Use it only when replacing all such errors with the same message is appropriate.
If the worksheet should calculate only when both cells contain numbers, with inputs in A2 and B2, use =IF(AND(ISNUMBER(A2),ISNUMBER(B2)),A2*B2,"Check input"). It returns 36 for 12 and 3, and the message if either input is text or blank. If blank cells should leave the result empty instead, with the same layout, use =IF(OR(A2="",B2=""),"",A2*B2). This prevents a blank input from being treated as zero in the displayed result, but it does not validate nonblank text.
Check formula references and workbook behavior
Not every multiplication error comes from the values a person can see. A copied formula may shift references unexpectedly, or a formula may include an unintended cell. Compare the references in a working row with those in the failing row. If you intended a fixed reference, use dollar signs to anchor its column, row, or both when filling the formula down.
For a detailed check in Excel for Microsoft 365 on Windows, select the cell containing the formula and choose Formulas > Evaluate Formula. Step through the calculation to see which part produces the error. The Formulas tab also provides Error Checking for navigating formula problems. If a cell’s formula is hidden because the column is narrow, widen it or select the cell and read the formula bar.
Multiplication can also be written with the PRODUCT function, but it is not automatically a better error fix. PRODUCT ignores text in referenced cells, which may cause a calculation to omit an entry rather than flag it. That difference can conceal incomplete data. Choose between an operator and a function based on the intended treatment of text, and verify that excluded entries are genuinely meant to be ignored.
Prevent recurring errors
Prevention is most effective at the point where data enters the workbook. Keep measurements and units in separate columns—for example, store 12 in one cell and “kg” in a neighboring label column—rather than typing “12 kg” into a field that later needs arithmetic. Use consistent decimal and grouping separators when importing or sharing data.
For a recurring import, clean or convert a representative sample first, then apply the same process to the full dataset. Preserve a copy of the original values until you have compared the results. Data validation can help restrict future entries, but it does not automatically clean existing cells or guarantee that pasted data follows the rule. A useful worksheet also labels units, documents how missing values are handled, and makes invalid records easy to locate.
- Separate numeric values from labels, units, and explanatory notes.
- Use consistent source formats and confirm regional separators before conversion.
- Check imported columns for text values before building dependent formulas.
- Prefer a visible warning over silently changing invalid values to zero.
Frequently Asked Questions
Why does Excel show #VALUE! when I multiply two cells? At least one referenced value may contain text Excel cannot convert, an unusual space or character, or an existing error. Check the cells’ contents and test them with ISNUMBER; also confirm the formula references the intended cells.
Can a number stored as text cause a multiplication error? It can, although Excel may coerce text that looks numeric in some arithmetic calculations. Imported formatting, spaces, symbols, and regional separators can prevent that conversion. Convert the value deliberately and verify the result instead of relying on automatic coercion.
How do I multiply cells and show zero instead of an error? With the inputs in A2 and B2, =IFERROR(A2*B2,0) returns zero for any error and returns the product when the inputs are valid—for example, 36 for 12 and 3. Use this only if zero is a truthful substitute; otherwise, a warning message is safer.
Why does PRODUCT give a different result from using an asterisk? PRODUCT ignores text in referenced cells, while the multiplication operator may try to coerce a text value or return an error. PRODUCT can therefore omit an entry without making that omission obvious. Check the data and the function’s treatment of text before relying on the result.
Does changing the cell format to Number fix #VALUE!? Usually not. Number formatting changes how a value is displayed; it does not necessarily convert text or remove hidden characters. Convert or clean the underlying value, then apply the desired number format if needed.
Related
- Find and Replace All in Word: Steps, Options, and Tips
- Excel Analysis Features in Microsoft 365
- Excel Key to Delete a Row: Shortcut and Options
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Errors & fixes
- Circular Reference in Excel: How to Find and Fix ItTo get rid of a circular reference in Excel, edit the formula so it no longer refers to…
- Excel Filter Not Working? Fix Common ProblemsIf Excel’s Filter option is not working, first select a cell in the full data range and choose…
- Excel Page Break on Row: How to Set and Fix ItTo put a page break above a specific row in Excel for Microsoft 365 on Windows, select that…
- Excel Cell Shows Formula Not Result? Fix It FastIf your Excel cell shows the formula text instead of the calculated result, the most likely cause is…
- Excel Not Highlighting Rows? Fix the Selection and RulesIf Excel is not highlighting rows, first check whether you are selecting the row or expecting a formatting…
- Excel FILTER Function Errors: How to Diagnose and Fix ThemMost Excel FILTER errors come from a criteria range that does not match the data range, no rows…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.