WPS Office

Free All-in-One Office Suite with PDF Editor

correct-icon

Edit Word, Excel, and PPT for FREE.

correct-icon

Read, edit, and convert PDFs with the powerful PDF toolkit.

correct-icon

Microsoft-like interface, easy to use.

Free Download

Windows • MacOS • Linux • iOS • Android

banner

Why error message #NAME? occurs and how to solve it

Uploaded time: March 14, 2022 Difficulty Beginner

Why error message #NAME? occurs and how to solve it

Why error message #NAME? occurs and how to solve it

When processing data in a table, the #NAME? error might occur.

There are four possible causes: first, the formula name was misspelled. Second, the double quotation marks were omitted for text values. Third, the colon was omitted in a range reference. Fourth, the formula refers to a name that has not been defined.

gif.gif

First, the formula name was misspelled

Take this table as an example. When we use the formula =SUN(C3:C12) to calculate the total sales of all products, the calculation result will return the error #NAME? This is because we entered the wrong formula name.

gif_1.gif

To solve this, we need to double-check the spelling of the formula and re-enter the correct name. The correct formula should be SUM so that we can achieve the right calculation result.

gif_2.gif

Second, the double quotation marks were omitted for text values

Take this table as an example. When we want to replace Pencil with Automatic pencil in the table, enter the formula = substitute (A3, Pencil, Automatic pencil) in cell E3.

gif_3.gif

The first parameter A3 involves text values. The second parameter is the old text Pencil to be changed, and the third parameter is the new text Automatic pencil to replace the old one. Why does the error value #NAME? appear? .

This is because the text reference misses a double quote.

To solve this, we need to add double quotes to these two text values. After filling in the remaining cells, all Pencil are replaced by Automatic pencil.

gif_4.gif

Third, the colon was omitted in a range reference.

Take this table as an example. When we want to calculate the total volume of all products, enter the formula  =SUM(B3B12), and the error #NAME? will come to us. This is because no colon is added to the range reference in the formula.

gif_5.gif

To solve this, we only need to add a colon to the range reference.

gif_6.gif

Fourth, the formula refers to a name that has not been defined.

When using a formula to calculate data, Name Manager can be used to name the selected area, making the following calculations easier.

For example, we can name the sales volume of products when we want to calculate the total volume.

Click Name Manager in the Formulas tab. Click New in the pop-up dialog, and then name the cell range B3:B12 as Volume.

gif_7.gif

At this time, when we use the SUM function to calculate the total volume, we can directly enter the formula =SUM (Volume) to get the result.

gif_8.gif

When we enter the wrong undefined name, it will display #NAME? At this time, we need to check whether the name is misspelled or we have to add a new custom name.

gif_9.gif

WPS Spreadsheet is a powerful chart and form processor. In WPS Academy, you can learn not only how to use Spreadsheet to sort out data but also how to corporate numeric information into graphs. By watching the step-by-step tutorials on WPS Academy, you will become a spreadsheet expert soon.

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.