How to Divide Two Columns in Excel 365 (C5:C12/D5:D12)

If you need to divide two columns row by row in Microsoft 365, you can do it in one step with a dynamic array formula. Enter a single formula like =C5:C12/D5:D12 and Excel will spill the results for you—no Ctrl+Shift+Enter required.

Key takeaways

Use =C5:C12/D5:D12 for element‑wise division that spills to E5:E12 automatically.
Reference the entire spill with E5# to aggregate or reuse results.
Prevent #DIV/0! with IF or IFERROR; aggregate safely with SUM, AVERAGE, or SUMPRODUCT.
Avoid #SPILL! by clearing blocked cells and keeping spilled formulas outside Tables.

Quick example: divide two columns with a single formula

Suppose numbers to divide are in C5:C12 and denominators are in D5:D12. Click E5 and enter:

=C5:C12/D5:D12

Press Enter. Excel 365 evaluates the division row by row and spills the results down E5:E12. Only E5 contains the formula; the rest are output cells. To reference the full spill in other formulas, use the hash operator with the anchor cell:

=SUM(E5#)

This spill behavior, including the E5# reference, is part of Excel’s dynamic arrays. For an overview of rules (top‑left anchor, resize, spill borders), see Microsoft’s guide on dynamic array formulas and spilled array behavior.

Step‑by‑step instructions

Prepare your ranges

Ensure C5:C12 and D5:D12 are the same size and contain numbers. If you imported data, double‑check that numeric text has been converted.

Enter the array division

Select E5 and type =C5:C12/D5:D12. Press Enter. You should see a blue spill border around E5:E12.

Check the results

Each row shows Cx divided by Dx. If any denominator is 0, you’ll see #DIV/0! in that row. We’ll fix that in a moment.

Reuse the spill

To total the spilled results later, use =SUM(E5#) rather than selecting cells manually. It updates automatically if the spill grows or shrinks.

Tip: Not seeing a spill? Ask yourself: what’s blocking Excel from filling those cells below or beside the anchor?

Understand spill behavior and avoid the #SPILL! error

Dynamic arrays write results into adjacent cells starting at the top‑left anchor (E5 in our example). If Excel can’t place one or more results, it returns #SPILL! in the anchor cell.

Top causes and quick fixes:

Obstructed spill range (data, spaces, merged cells) → clear the destination cells or unmerge.
Spilling inside an Excel Table → spilled arrays aren’t supported inside Tables; place the formula outside the Table.
Range too large or spills beyond the sheet’s edge → reduce the referenced range.

Microsoft documents these behaviors in How to correct a #SPILL! error. For a concise, practical walkthrough of common blockers, see Exceljet’s guide on fixing the #SPILL! error.

Handle divide‑by‑zero and other errors cleanly

Here’s the deal: if any cell in D5:D12 is 0 (or non‑numeric), =C5:C12/D5:D12 will display errors in those positions. You’ve got two simple ways to make results user‑friendly.

Preempt zero denominators with IF (return a blank):

=IF(D5:D12=0, "", C5:C12/D5:D12)

If a denominator equals 0, the result is blank; otherwise, the quotient spills normally. This is great when you want to visually skip problem rows.

Replace any error with a fallback value using IFERROR:

=IFERROR(C5:C12/D5:D12, 0)

IFERROR returns 0 where the division would error (e.g., #DIV/0!, #VALUE!). You can also return a blank "" or a label like "N/A". Function behavior is detailed in Microsoft’s IFERROR reference.

When averaging quotients, remember that plain AVERAGE propagates errors. Wrap the division with IFERROR first or filter valid numbers before averaging (see the next section for aggregation options).

Aggregate without spilling: totals and averages for Excel array division

Sometimes you don’t want a visible spill at all—you just want a single cell with a total or average of the row‑by‑row division. Use an aggregator to collapse the array directly.

Sum, ignoring errors:

=SUM(IFERROR(C5:C12/D5:D12, 0))

Average, ignoring errors:

=AVERAGE(IFERROR(C5:C12/D5:D12, ""))

Compact, non‑spilling total with SUMPRODUCT:

=SUMPRODUCT(C5:C12/D5:D12)

Why choose SUMPRODUCT? It naturally returns a single value without spilling and plays well with older workbooks. Microsoft’s SUMPRODUCT function page covers how it multiplies and sums arrays, and—importantly for us—how it reduces array expressions to a scalar.

Working with Tables and structured references

Spilled array formulas don’t work inside Excel Tables. If your data lives in a Table, place your dynamic formula outside the Table or aggregate without spilling.

Example: from a cell outside the Table, you can compute a non‑spilling total directly from Table columns using structured references:

=SUMPRODUCT(Dept[Num]/Dept[Den])

This divides each row in Dept[Num] by the corresponding row in Dept[Den] and returns one total—no spill needed. If denominators may be zero or non‑numeric, combine with IFERROR or an appropriate filter.

Tips, compatibility, and performance notes

Compatibility: Dynamic arrays (spilling and E5#) are supported in Microsoft 365 and Excel 2021+. In older Excel, arrays don’t spill; you’d select the output range first, enter the formula, then confirm with Ctrl+Shift+Enter. If you need a single result that works broadly, SUMPRODUCT is a safe bet.
Data hygiene: If you imported values and division returns unexpected #VALUE! errors, some cells might be text‑numbers. Try coercion before dividing:

=VALUE(C5:C12)/VALUE(D5:D12)

Or apply cleaning when needed (e.g., TRIM, CLEAN, or a double‑unary like --D5:D12) before division. Think of it as tidying the kitchen before you cook—the whole meal comes out better.

Referencing spilled results downstream: Prefer the spill reference E5# when charting or feeding other formulas; it expands and contracts with the source automatically.

Closing and next steps

You now have a reliable playbook for Excel array division: start with =C5:C12/D5:D12, handle errors with IF or IFERROR, and aggregate cleanly with SUM, AVERAGE, or SUMPRODUCT. Want to speed up analysis even more without memorizing formulas? Tools like hiData can help you process spreadsheet data with natural‑language instructions, auto‑clean imports, and generate charts or slides—useful when you’re juggling multiple files and just need answers fast.

Further reading from Microsoft and reputable Excel references: