Some links on this page are affiliate links: if you buy through them we may earn a commission, at no extra cost to you.
Use Excel’s DELTA function when you need a numeric equality flag: it returns 1 when two numeric values are equal and 0 when they are not. The syntax is =DELTA(number1, [number2]). The first argument is required; the second is optional and is treated as zero when omitted. That makes =DELTA(A2) a zero test, while =DELTA(B2,C2) is a row-by-row match test. DELTA is useful when you want results that can be summed, filtered, or fed into another calculation, but it is not a subtraction function and it compares exact stored numbers.
What does DELTA do in Excel?
DELTA tests whether two numeric values are equal. Microsoft Support describes it as returning 1 if number1 = number2 and 0 otherwise. Microsoft classifies it as an engineering function and also refers to it as the Kronecker Delta function.
As an Amazon Associate I earn from qualifying purchases.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →The result is deliberately numeric rather than logical. A returned 1 can be counted with SUM, multiplied by another value, or used as a condition inside IF. A returned 0 marks a non-match without producing the numeric difference between the arguments.
| Formula | Result | Meaning |
|---|---|---|
=DELTA(5,5) |
1 | The values are equal. |
=DELTA(5,4) |
0 | The values differ. |
=DELTA(0.5,0) |
0 | 0.5 is not zero. |
=DELTA(7) |
0 | The omitted second argument is treated as zero, so 7 does not equal 0. |
Both arguments must resolve to numeric values. If either argument is nonnumeric, Excel documents a #VALUE! error.
Popular on Amazon
- Classic Office Apps | Includes classic desktop versions of Word, Excel, PowerPoint, and OneNote for creating documents, spreadsheets, and presentations with ease.
- Install on a Single Device | Install classic desktop Office Apps for use on a single Windows laptop, Windows desktop, MacBook, or iMac.
- Ideal for One Person | With a one-time purchase of Microsoft Office 2024, you can create, organize, and get things done.
- Consider Upgrading to Microsoft 365 | Get premium benefits with a Microsoft 365 subscription, including ongoing updates, advanced security, and access to premium versions of Word, Excel, PowerPoint, Outlook, and more, plus 1TB cloud storage per person and multi-device support for Windows, Mac, iPhone, iPad, and Android.
What is the DELTA syntax?
Enter =DELTA(number1, [number2]) in a worksheet cell. Square brackets indicate that number2 is optional; do not type the brackets themselves.
| Part | Requirement | What Excel does |
|---|---|---|
number1 |
Required | Uses the first numeric value in the equality test. |
number2 |
Optional | Uses the second numeric value. If omitted, Excel uses zero. |
| Return value | Always numeric for valid numeric inputs | Returns 1 for equality and 0 for inequality. |
Argument order does not change an equality result: if two valid numbers match, both =DELTA(A2,B2) and =DELTA(B2,A2) return 1. Keep a consistent order in a worksheet so that formulas are easier to audit.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
How do I compare two cells with DELTA?
Put a DELTA formula in a comparison column, then copy it down so each row compares the corresponding pair.
- Place the first value in column B and the value you want to check in column C. For example, row 2 contains the two numbers to compare.
- Select the result cell, such as D2, and enter
=DELTA(B2,C2). - Press Enter. D2 displays 1 when B2 and C2 are equal and 0 when they differ.
- Drag the fill handle down, or copy D2 and paste it through the remaining rows. Relative references change to B3/C3, B4/C4, and so on.
- Use the 1/0 column for filtering, conditional formatting, a count, or another formula.
This approach is useful for reconciling an expected amount against an observed amount, checking two answer columns, or flagging rows that agree. DELTA reports only the match state; it does not tell you how far apart unequal values are.
Example: a row-level reconciliation
| Row | Expected (B) | Observed (C) | Formula in D | Result |
|---|---|---|---|---|
| 2 | 125 | 125 | =DELTA(B2,C2) |
1 |
| 3 | 125 | 124.99 | =DELTA(B3,C3) |
0 |
| 4 | 0 | 0 | =DELTA(B4,C4) |
1 |
How do I check whether a value is zero?
Omit the second argument: =DELTA(D2) compares D2 with zero. It returns 1 when D2 is numerically zero and 0 for any other numeric value.
- Identify the balance, variance, or status value to test, such as D2.
- Enter
=DELTA(D2)in a separate status column. - Interpret 1 as “equals zero” and 0 as “not zero.”
Be careful with empty cells. A practical Excel tutorial reports that an empty balance can be treated as zero in this kind of test, which could incorrectly make an unfilled balance look paid. If an empty cell must remain distinct from a genuine numeric zero, test whether it is blank separately before relying on the DELTA result.
How do I count matching values with DELTA?
Because DELTA returns 1 and 0, adding its results counts matches. For two ten-row answer ranges, the array formula =SUM(DELTA(+B2:B11,+C2:C11)) compares each pair and adds the returned 1s. In the tutorial’s example, seven matching pairs produce a score of 7.
Also popular on Amazon
![Microsoft Office Home & Business 2024 | Classic Desktop Apps: Word, Excel, PowerPoint, Outlook and OneNote | One-Time Purchase for 1 PC/MAC | Instant Download [PC/Mac Online Code]](https://m.media-amazon.com/images/I/41H8B5iqO4L._SL160_.jpg)
- [Ideal for One Person] — With a one-time purchase of Microsoft Office Home & Business 2024, you can create, organize, and get things done.
- [Classic Office Apps] — Includes Word, Excel, PowerPoint, Outlook and OneNote.
- [Desktop Only & Customer Support] — To install and use on one PC or Mac, on desktop only. Microsoft 365 has your back with readily available technical support through chat or phone.
Modern dynamic-array Excel
In newer Excel versions, enter =SUM(DELTA(+B2:B11,+C2:C11)) in one cell. The unary plus signs coerce each range into arrays, DELTA produces a corresponding series of 1s and 0s, and SUM totals them. If you want to inspect each row rather than only the total, enter =DELTA(+B2:B11,+C2:C11) in a blank area and let the results spill downward.
Excel 2019 and earlier
The practical tutorial recommends =SUMPRODUCT(DELTA(+B2:B11,+C2:C11)) as an array-counting alternative for older versions. Another reliable option is to enter the row formula =DELTA(B2,C2) in a helper column and copy it down, then sum that helper column.
A plain range expression such as =DELTA(B2:B11,C2:C11) may return #VALUE! in the tutorial’s example. The unary plus before each range is important there because it coerces the references into array values that DELTA can evaluate.
Why am I getting #VALUE! with DELTA?
#VALUE! means that at least one argument is not being supplied as a numeric value. Microsoft’s documented rule is that a nonnumeric argument produces this error.
Check for ordinary text
Labels, words, and other ordinary text are not valid numeric DELTA arguments. Inspect the source cells for imported characters, currency symbols stored as text, leading apostrophes, or error values. Clean the source data or convert a text representation to a number before comparing it.
Check TRUE and FALSE values
The practical tutorial reports that ordinary TRUE/FALSE values produce #VALUE! with DELTA. Do not assume that a logical value will be treated as the number 1 or 0. If your source is logical, decide whether you need a logical comparison with = or an explicit numeric conversion before calling DELTA.
Check numeric text
A numeric string such as "34.99" can be converted for comparison, while ordinary text cannot. Validate imported data before treating a match as authoritative. A conversion wrapper such as VALUE can make the intent explicit when the cell contains a number represented as text; if conversion fails, fix the source rather than hiding the error.
Outdated Drivers Are Slowing You Down
One free scan finds every outdated or missing driver and matches the right update for your exact hardware.Free scan · exact hardware matchPC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Also popular on Amazon
- Designed for Your Windows and Apple Devices | Install premium Office apps on your Windows laptop, desktop, MacBook or iMac. Works seamlessly across your devices for home, school, or personal productivity.
- Includes Word, Excel, PowerPoint & Outlook | Get premium versions of the essential Office apps that help you work, study, create, and stay organized.
- 1 TB Secure Cloud Storage | Store and access your documents, photos, and files from your Windows, Mac or mobile devices.
- Premium Tools Across Your Devices | Your subscription lets you work across all of your Windows, Mac, iPhone, iPad, and Android devices with apps that sync instantly through the cloud.
- Easy Digital Download with Microsoft Account | Product delivered electronically for quick setup. Sign in with your Microsoft account, redeem your code, and download your apps instantly to your Windows, Mac, iPhone, iPad, and Android devices.
Check ranges and arrays
If the error appears only when you compare ranges, use the array-coercion form shown above: =DELTA(+B2:B11,+C2:C11). For a single row, use direct cell references such as =DELTA(B2,C2).
Why does DELTA return 0 when the numbers look the same?
DELTA compares the exact stored numeric values, not only the rounded values displayed in the cells. Two results can display as 13.05 while differing in hidden decimal places.
Binary floating-point storage is a common cause. The practical tutorial gives the example of a calculation such as 4.35 × 3 being stored as 13.049999999999999. A billed amount displayed as 13.05 can therefore fail an exact DELTA comparison and return 0.
Round the calculated side before comparing
Round both values to the precision that matters to your business rule before testing equality. For row-level values, use =DELTA(ROUND(B2*C2,2),D2) when B2 times C2 should match D2 to two decimal places. For array comparisons, the tutorial uses =DELTA(ROUND(B2:B11*C2:C11,2),+D2:D11).
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Scan for outdated or missing drivers - takes under a minute3Repair Windows errors before they cause bigger problems- Choose the number of decimal places from the meaning of the data, not merely from the current display format.
- Round calculated amounts before DELTA evaluates them; changing a cell’s visual number format alone does not change its stored value.
- Keep the same rounding policy on every row so that a match means the same thing throughout the worksheet.
What is the difference between DELTA, an equals sign, and subtraction?
Choose among these operations based on the output you need: DELTA gives a numeric 1/0 indicator, = gives a logical TRUE/FALSE result, and subtraction gives the amount of the difference.
| Method | Example | Output | Best use |
|---|---|---|---|
| DELTA | =DELTA(B2,C2) |
1 or 0 | Countable match flags and numeric scoring. |
| Equality operator | =B2=C2 |
TRUE or FALSE | Logical tests, especially when a Boolean result is what a formula or rule expects. |
| Subtraction | =B2-C2 |
Numeric difference | Finding the size and direction of a variance. |
DELTA does not calculate a difference. If a payment is 0.01 below an invoice, DELTA tells you only that it is not equal; subtraction tells you the variance. Conversely, subtraction alone does not produce a direct 1/0 match indicator unless you add another test.
The methods also differ in how they handle numeric text, blanks, and logical values. Test a representative sample of your imported data before replacing one method with another, and use explicit conversion or rounding when your comparison rule requires it.
Also popular on Amazon
How can I use DELTA inside a larger formula?
DELTA’s numeric result makes it convenient as a building block. For example, =IF(DELTA(B2,C2)=1,"Match","Review") turns the numeric flag into a readable label, while =SUM(DELTA(+B2:B11,+C2:C11)) turns many flags into a count.
Quick wins for a faster PC:
Clear out junk files and repair common Windows errorsFree Scan →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Scoring matched answers
Place an answer key in one column and submitted answers in another. A row-level DELTA result gives one point for each exact numeric match. Sum the result column, or use the array form, to obtain the total score.
Reconciliation workbooks
Use DELTA for an exact check after applying the appropriate currency rounding. Keep the original values, the rounded comparison values, and the 1/0 result in separate columns so an auditor can see whether a mismatch comes from the source data or from precision.
Zero-status indicators
Use the one-argument form for a calculated variance column. A 1 means the variance is exactly zero under the values and rounding policy you supplied; a 0 means it is not.
Which Excel versions support DELTA?
Microsoft’s current Support documentation lists DELTA for Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016, including the listed Mac editions. The function is therefore available across those supported desktop releases, but array behavior and spill handling depend on the Excel version.
| Environment | Practical approach |
|---|---|
| Microsoft 365 or another modern dynamic-array release | Use range formulas with unary-plus coercion and let results spill when appropriate. |
| Excel 2019 or earlier | Copy a row-level formula down, or use the tutorial’s SUMPRODUCT(DELTA(+range,+range)) pattern for an aggregate. |
| Mac editions listed by Microsoft | Use the same DELTA syntax, while checking the array behavior of the installed release. |
If a workbook will be shared across versions, helper columns are often easier for recipients to inspect than a spilling formula. Keep the formula simple and document any rounding or text-conversion rule beside the calculation.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.How do I call DELTA from VBA?
VBA exposes the worksheet function as WorksheetFunction.Delta. The equivalent call is WorksheetFunction.Delta(Arg1, Arg2); it returns a Double, defaults the second argument to zero when it is omitted, and follows the same equality and #VALUE! behavior for invalid arguments.
Also popular on Amazon
- Fully compatible with Microsoft Office documents, Office Suite is the number 1 affordable alternative. It is compatible with Word, Excel and PowerPoint files allowing you to create, open, edit and save all your existing documents in an easy-to-use professional office suite. Suitable for home, student, school, family, personal and business use, it includes comprehensive PDF user guides to help you get started, plus a dedicated guide for university students to help with their studies. Multilingual - English, Spanish (Español) and more languages supported.
- Professional premier office suite includes word processor, spreadsheet, presentation, graphics, database and math apps! It can open a plethora of file formats including doc, docx, odt, txt, xls, xlsx, xlsm, ppt, pptx and many more, making it the only office suite you will ever need. You can use the ‘Save as’ feature to ensure your files remain compatible with Word, Excel and PowerPoint, plus you can convert and export your documents to PDF with ease.
- Full program included that will never expire! Free for life updates with lifetime license so no yearly subscription or key code required ever again! Unlimited users allow you to install to both desktop and laptop without any additional cost, and everything you need is provided on USB; perfect for offline installation, reinstallation and to keep as a backup. Compatible with Microsoft Windows 11, 10, 8.1, 8, 7, Vista, XP (32/64-bit), Mac OS X and macOS.
- PixelClassics exclusive extras include 1500 fonts, 120 professional templates, 1000's of clip art images, PDF user guides, over 40 language packs, easy-to-use PixelClassics installation menu (PC only), email support and more! Each USB comes complete with our quick start install guide, plus a fully comprehensive PDF guide is provided on USB.
- You will receive the USB (not a disc) exactly as pictured, in protective sleeve (retail box not included). Our slimline USB is 100% compatible with ALL standard size USB ports. To ensure you receive exactly as advertised including all our exclusive extras, please choose PixelClassics. All our USBs are checked and scanned 100% virus and malware free giving you peace of mind and hassle-free installation, and all of this is backed up by PixelClassics friendly and dedicated email support.
Dim flag As Double
flag = WorksheetFunction.Delta(Range("B2").Value, Range("C2").Value)Use the VBA form when a macro needs a numeric match flag. Validate or convert worksheet inputs before the call, because passing nonnumeric data produces the same kind of failure as entering the function directly in a cell.
What should I check before trusting a DELTA result?
- Are both inputs numeric? Look for imported text, apostrophes, labels, logical values, and existing error cells.
- Was the second argument omitted intentionally? Omitting it means “compare with zero,” not “ignore the second value.”
- Could an empty cell be mistaken for zero? Treat blank balances as a separate data-quality case if blank does not mean paid or settled.
- Are you comparing ranges? In a spilling or aggregate formula, use unary-plus coercion as in
+B2:B11and+C2:C11. - Do decimals need a tolerance or a rounding rule? Round calculated values before DELTA when display-equivalent amounts can differ internally.
- Do you need a difference instead of a flag? Use subtraction when the size or direction of the variance matters.
- Will someone open the file in an older Excel release? Prefer a copied row formula or the documented SUMPRODUCT pattern when dynamic spilling is unavailable.
Can DELTA compare dates or percentages?
DELTA compares numeric values, so dates and percentages can be tested when Excel stores them as numbers. Two dates that represent the same serial value can return 1, and two percentages with the same underlying value can return 1. If the cells contain date or percentage text imported as characters, convert and validate that data first; formatting alone does not make text numeric.
Free tools Windows power users keep installed
One-click scans. No signup required.
Can I use DELTA for approximate equality?
DELTA itself performs exact equality and has no tolerance argument. For measurements or calculated amounts where a small variance is acceptable, first define the allowed precision, round both sides to that precision, and then apply DELTA. If you need the actual tolerance breach or signed variance, subtraction is the more informative first calculation.
Frequently Asked Questions
Can I make DELTA display “Match” and “No match” instead of 1 and 0?
Yes. Wrap the test in an IF formula, for example =IF(DELTA(B2,C2)=1,"Match","No match"). Keep the underlying numeric DELTA result in a helper column if you also need to count matches later.
Does changing the number format fix a DELTA mismatch?
No. Number formatting changes what you see, not the stored value that DELTA compares. Round the relevant calculation explicitly before calling DELTA when the displayed precision is the intended comparison rule.
Can I use DELTA when one cell is blank and the other contains zero?
Treat that as a data-definition decision rather than assuming the result proves the records agree. A practical tutorial notes that an empty balance may be treated as zero, so add a separate blank check when an empty field should not count as a paid or completed value.
Is DELTA available in Excel for Mac?
Microsoft’s supported-product list includes the listed Mac editions alongside Microsoft 365, Excel 2024, Excel 2021, Excel 2019, and Excel 2016. The exact spill behavior still follows the installed Excel release, so older Mac versions may be better served by copied row formulas.
Why would the equals operator work when DELTA returns #VALUE!?
The two operations do not have identical coercion behavior. DELTA requires numeric arguments and documents #VALUE! for nonnumeric inputs, while an equals comparison can handle some values differently; inspect and normalize imported text or logical data before choosing the test you want.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
