A colleague in Germany sends you a spreadsheet formula to paste in: =SUMMEWENN(A:A;"Bezahlt";B:B). You drop it into your English Excel and get #NAME?. The formula is correct. It is just written in another language. This guide explains why that happens and how to convert the formula so it works.
The short answer: Excel translates function names to match its interface language, so SUM shows as SUMME in German and SOMME in French, and many languages swap the comma argument separator for a semicolon. To move a pasted formula between versions, you translate the function names and switch the separator. The Excel formula translator does both at once.
Why function names change by language
Excel localises its function names. Install Excel in German and the function you know as SUM appears as SUMME everywhere: in the formula bar, in the function wizard, in autocomplete. Install it in French and the same function is SOMME. This is not a setting you turned on; it is how every language version of Excel works.
Here is the part that trips people up. The file does not store SUMME or SOMME. Internally, every workbook keeps function names in a single canonical form, and your copy of Excel renders them in your language when it shows you the formula. That is why opening a German colleague’s .xlsx in your English Excel just works: Excel reads the stored function and displays it as SUM for you.
The trouble starts when you bypass the file. Copying a formula as text (from an email, a Stack Overflow answer, a blog post, a chat message) and pasting it into the formula bar gives Excel a string in the wrong language. It has no file to translate, so it tries to parse SUMME literally, finds no function by that name in English, and returns #NAME?.
The two things that have to change
Translating a pasted formula is not only about the function names. Two things differ between language versions:
- Function names.
VLOOKUPbecomesSVERWEIS(German),RECHERCHEV(French),BUSCARV(Spanish),CERCA.VERT(Italian),PROCV(Portuguese) orVERT.ZOEKEN(Dutch). - The argument separator. English Excel separates arguments with a comma:
=IF(A1>0,"yes","no"). Most European versions use a semicolon:=WENN(A1>0;"yes";"no"). Miss this and even correctly named functions will not parse.
Here is the same formula across a few languages so the pattern is clear:
| Language | Formula |
|---|---|
| English | =IF(SUM(A1:A10)>100,"High","Low") |
| German | =WENN(SUMME(A1:A10)>100;"High";"Low") |
| French | =SI(SOMME(A1:A10)>100;"High";"Low") |
| Spanish | =SI(SUMA(A1:A10)>100;"High";"Low") |
Notice what does not change: the cell range A1:A10, the number 100, and the quoted text "High" and "Low". Only the function names and the separators move. That is the rule a translation has to follow: touch the function names and separators, and leave everything else alone.
How to translate a formula
The reliable way is to convert the names and the separator in one step rather than hunting through a long formula by hand.
Step 1: Identify the source language
Look at the function names. SVERWEIS and WENN are German; RECHERCHEV and SI are French; BUSCARV is Spanish. If you are not sure, the person or page you got the formula from usually tells you which Excel they use.
Step 2: Paste it into the translator
Open the formula translator, choose the language the formula is written in and the language you need, and paste the formula in. The function names are rewritten and the comma or semicolon separator is adjusted to match the target.
Step 3: Copy the result into Excel
Copy the converted formula and paste it into your formula bar. Because only names and separators changed, it calculates exactly as the original did.
A worked example
You are handed this French formula:
=SIERREUR(RECHERCHEV(A2;Feuil2!A:C;3;FAUX);"Introuvable")Translating French → English gives:
=IFERROR(VLOOKUP(A2,Feuil2!A:C,3,FALSE),"Introuvable")Every function name changed (SIERREUR to IFERROR, RECHERCHEV to VLOOKUP, FAUX to FALSE), and each semicolon became a comma. The cell reference A2, the column range A:C, the index 3 and the quoted French text "Introuvable" are all untouched. The text stays in French on purpose: translating a function name is a technical fix, while the words inside quotes are your data and changing them would change the result. (The sheet name Feuil2 also stays, since it is the literal name of a tab in the workbook.)
Common mistakes to avoid
- Forgetting the separator. Translating
SUMMEtoSUMbut leaving the semicolons in place still throws an error. The separator has to change with the language. - Translating text inside quotes. Anything between
"marks is data, not a function. Leave it exactly as written, even if it is in another language. - Assuming every function has a different name. Some, like
MAXandMIN, are identical in several languages. A good translation leaves those alone rather than inventing a change. - Trusting a blind find-and-replace. Replacing
SIwithIFacross a formula will also corrupt any word containing those letters. Translation has to recognise where a function name actually is, directly before an opening parenthesis, not just match the letters.
Once your formula calculates, you might need to hand the finished sheet to someone who works in CSV. From there, see how to convert Excel to CSV.