How to Divide Two Columns in Excel (A/B) — Quick Guide

If you need a quick, reliable way to calculate ratios—like revenue per user or cost per unit—Excel makes it easy. You can divide two columns in Excel with the / operator, copy the formula down with the Fill Handle, use a single dynamic array formula in Microsoft 365, or perform an in-place division with Paste Special. This guide shows each option and how to prevent #DIV/0! errors.

Key takeaways

You’ll divide two columns in Excel most often with a simple formula like =A2/B2 and copy it down the column. In Microsoft 365, you can divide entire ranges at once (for example, =A2:A100/B2:B100) and let the results “spill” automatically. If you need static values, Paste Special > Divide can rewrite numbers in place. Wrap your division in IFERROR to avoid #DIV/0! when the denominator is zero or invalid, and clean inputs that are stored as text before you start.

Quick method chooser to divide two columns in Excel

According to Microsoft Support, Excel uses the “/” operator for division in formulas (e.g., =A2/B2). See the official overview in Multiply and divide numbers in Excel for examples and notes on formula entry: divide numbers with the “/” operator.

Method 1 — Cell-wise division and Fill Handle

This is the universal method and works in every supported Excel version. It’s perfect when you want visible formulas and easy editing.

1. Select the first result cell (for example, C2) and enter:

   =A2/B2

2. Press Enter. If the result looks right, point to the bottom-right corner of C2 until you see the small square (the Fill Handle), then drag down to copy the formula through the rest of the rows. Tip: Select the target range first and press Ctrl+D (Windows) or Command+D (Mac) to fill down faster.

3. Sanity-check a few rows to confirm the references updated correctly (C3 should read =A3/B3, and so on).

Pro tip: If you select the entire destination range before typing, you can type the formula once and press Ctrl+Enter to populate all selected cells at once.

Method 2 — Dynamic array range-wise division — Microsoft 365

If you’re on Microsoft 365 or Excel 2021+, you can divide two columns in Excel with one formula that “spills” results into the cells below. This keeps your sheet simpler and reduces copy-down work.

1. Choose a single empty cell where you want the results to begin (for example, C2), and enter:

   =A2:A100/B2:B100

2. Press Enter. Excel will calculate the division element‑by‑element and spill results down automatically. If something is blocking the spill range, you’ll see a #SPILL! error—clear any values or merged cells in the way and try again.

3. Ensure both ranges are the same size. Mismatched heights or widths can cause errors or unexpected results.

Spilled arrays and their behavior are covered in Microsoft’s documentation on dynamic arrays: see dynamic array formulas and spilled array behavior.

Note: If you prefer explicit element‑wise control, you can also use MAP with LAMBDA in Microsoft 365:

=MAP(A2:A100,B2:B100,LAMBDA(x,y, x/y))

Method 3 — Paste Special > Divide — desktop-only, static results

Use this when you want to overwrite numbers with their divided results—no formulas left behind. It’s handy for finalizing import data or preparing one-off files.

1. Enter your divisors in a helper cell or range (for example, type 2 in D1 for a constant divisor, or paste a column of divisors D2:D100).

2. Copy the helper cell/range (Ctrl+C or Command+C).

3. Select the target numbers to divide (your numerators).

4. Open Paste Special:

   • Windows: Ctrl+Alt+V

   • Mac: Command+Control+V

5. In the dialog, under Operation, choose Divide, then click OK. Excel replaces the destination values with the results.

This feature and options are explained in Microsoft’s Paste Special reference: see Paste Special operations. Remember, Paste Special > Divide overwrites values—use Undo immediately if you made a mistake.

Prevent #DIV/0! with IFERROR (and clean inputs)

Errors happen when a denominator is zero, blank, or not numeric. Guard your formulas and clean data first so your dashboards don’t break.

Guarded single cell:

=IFERROR(A2/B2, 0)

• Returns 0 if B2 is 0 or invalid. Use "" instead of 0 if you’d rather show a blank.

Guarded dynamic array (365):

=IFERROR(A2:A100/B2:B100, "")

Input cleanup that fixes many #VALUE! errors:

• Convert numbers stored as text: =VALUE(E2), fill down, then paste values back. Microsoft explains several conversion options here: convert numbers stored as text to numbers.

• Remove stray spaces and non‑printing characters: wrap inputs with TRIM and CLEAN if you suspect hidden characters.

For IFERROR syntax and examples, see Microsoft’s guide to the function: IFERROR to replace errors like #DIV/0!.

Format and round the results

Once you divide two columns in Excel, decide how to display the ratio.

• Percentage format: If your result is a rate (e.g., click‑through rate), select the result cells and click the % button on the Home tab, or use Format Cells > Percentage. A value like 0.275 will display as 27.5%.

• Fixed decimals: Either format the cells or round in the formula. Example:

  =ROUND(A2/B2, 2)

• Combined with error handling:

  =IFERROR(ROUND(A2/B2, 2), "")

Quick reference: formulas and shortcuts

Cell-wise division (all versions)
=A2/B2  → copy down with Fill Handle, Ctrl+D (Win) or Command+D (Mac)

Guard against errors
=IFERROR(A2/B2, 0)   or   =IFERROR(A2/B2, "")

Dynamic array (Microsoft 365)
=A2:A100/B2:B100     or     =MAP(A2:A100,B2:B100,LAMBDA(x,y,x/y))

Paste Special > Divide (desktop)
Copy divisor(s) → select target → Ctrl+Alt+V (Win) / Command+Control+V (Mac) → Operation: Divide → OK

Automation note

If you often repeat this workflow across messy files, a lightweight automation tool can help. For example, hiData supports plain‑English instructions to clean text‑numbers and calculate column ratios, then exports charts or slides—all without writing formulas.

Wrap-up

You’ve got three solid ways to divide two columns in Excel: a classic formula with the Fill Handle, a single dynamic array that spills results in Microsoft 365, and an in‑place Paste Special for static numbers. Add IFERROR to prevent #DIV/0!, clean text‑numbers before you start, and format results as percentages or rounded decimals. Try each method on a small sample to see which best fits your workbook and version.