sodcalculator

10 Tech How-To

N Function in Excel: How to Use It

Guide 10-56855 · Updated · Step by step

The N function in Microsoft Excel converts a value into a number. It is a simple but useful tool for cleaning data, building formulas that need numeric input, and avoiding errors when a cell contains text. You give it a cell reference or a value, and it returns a number based on a fixed set of rules. This guide explains how to use N, what it returns for different types of data, and how to apply it in real-world scenarios.

What You’ll Need

  • Microsoft Excel (any recent version, including Excel 365, Excel 2019, 2016, and earlier).
  • A worksheet with some data, or just a blank workbook to practice.
  • Basic familiarity with entering formulas and using cell references.

Step-by-Step

  1. Open Excel and select a cell where you want the result to appear. For example, click cell B2.
  2. Type the formula starting with an equals sign: =N(. Excel will show a tooltip explaining the function.
  3. Enter the value or cell reference inside the parentheses. You can type a number directly, like =N(5), or reference a cell, like =N(A2). You can also reference a range, but only the top-left cell is used.
  4. Close the parentheses and press Enter. The result appears in the cell.
  5. Understand what N returns based on the input:
    • If the value is a number, N returns that number.
    • If the value is a date (stored as a serial number in Excel), N returns that serial number.
    • If the value is TRUE, N returns 1.
    • If the value is FALSE, N returns 0.
    • If the value is text, N returns 0.
    • If the value is an error (like #DIV/0!), N returns the error itself.
  6. Use N in a larger formula. For example, to add 10 to a cell that might contain text, use =N(A2)+10. If A2 contains “hello”, N returns 0, so the result is 10. If A2 contains 5, the result is 15.
  7. Drag the fill handle (the small square at the bottom-right of the selected cell) down to apply the formula to other cells in the column.

Common Mistakes

  • Expecting N to convert text numbers to numbers. N does not parse text like “123”; it returns 0 for any text. Use VALUE() or arithmetic like *1 for that.
  • Using N on a range expecting an array result. N only evaluates the first cell in a range. For example, =N(A1:A5) returns the N of A1 only, not an array.
  • Forgetting that N returns the date serial number, not a date. If you input a date, you get a large number like 45000, which may look odd unless formatted as a date.
  • Using N with a formula that returns text. If your formula returns text, N will give 0, which might hide a problem. Always check the original formula’s result.

Tips

  • Use N to force numeric context. In array formulas or SUMPRODUCT, N can convert logical values to 1/0, which helps with calculations.
  • Combine N with IF for cleaner formulas. Instead of =IF(A1>0,1,0), you can use =N(A1>0) – it returns 1 if true, 0 if false.
  • Use N to avoid errors when concatenating. For example, ="Total: " & N(A1) will show “Total: 0” instead of “Total: #VALUE!” if A1 has text.
  • Remember that N is not the same as ISNUMBER. ISNUMBER returns TRUE/FALSE; N returns a number. Use them for different purposes.
  • For compatibility with other spreadsheet software, N is a standard function, but some alternatives like -- (double unary) or *1 are more common in Excel formulas.

Troubleshooting

If your N formula doesn’t give the expected result, check the following:

  • Result is 0 when you expected a number: The input is likely text. Check if the cell is formatted as text (look for a green triangle or the Number Format dropdown). If so, convert it to a number using VALUE() or by multiplying by 1.
  • Result is a date serial number: That’s normal. To display it as a date, change the cell format to Date.
  • Result is an error like #VALUE!: The input itself is an error. Use IFERROR to handle it, e.g., =IFERROR(N(A1),0).
  • Formula not calculating: Check if calculation is set to manual (Formulas > Calculation Options). Press F9 to recalculate.
  • N appears in a formula but Excel says it’s not a function: This is rare; ensure the function name is in English and the cell is not in text format.

Frequently Asked Questions

What does N do in Excel?

N converts a value to a number according to a fixed rule: numbers stay as they are, dates become serial numbers, TRUE becomes 1, FALSE becomes 0, and text becomes 0.

Why would I use N instead of just leaving the value?

N is useful when you need to ensure a formula treats a cell as a number, especially when the cell might contain text or logical values. It helps avoid errors and makes formulas more robust.

Does N work with blank cells?

Yes, a blank cell is treated as 0 by N. That’s because an empty cell is considered numeric 0 in Excel.

Can N convert text numbers like “123” to 123?

No, N returns 0 for any text, even if it looks like a number. Use VALUE() or -- to convert text numbers.

Is N the same as the NUMBERVALUE function?

No. NUMBERVALUE converts text that represents a number (like “1,200”) into a numeric value, respecting locale. N does not parse text at all.

Can I use N with an array formula?

Yes, but N only processes the first cell in a range. To apply N to an entire array, you might need to use it in a formula that iterates, like SUMPRODUCT, or use the newer dynamic array functions.

Related

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →