Excel GCD and LCM: Greatest Common Divisor and Lowest Common Multiple
GCD finds the largest number that divides them all; LCM finds the smallest that they all divide into.
GCD returns the greatest common divisor — the largest whole number that divides every value you give it without a remainder. LCM returns the lowest common multiple, the smallest number that all of them divide into evenly. They are schoolbook arithmetic that turns out to be genuinely useful for scheduling and packing.
GCD's practical job is simplifying and splitting into equal groups. Given 140 items and 210 items, GCD tells you the largest batch size that divides both evenly, which is how you split two different quantities into the same number of identical groups.
LCM answers cycle questions. If one task repeats every 12 days and another every 18, LCM(12, 18) is 36 — the first day both happen again. Any question of the form "when do these line up?" is an LCM underneath.
Syntax
=GCD(number1, [number2], …) =LCM(number1, [number2], …)Arguments
number1Required- The first value. Both functions accept up to 255 numbers or ranges of them.
number2, …Optional- Further values. Decimals are truncated to whole numbers before the calculation runs.
The example data
Headers in row 1, data in A2:D5.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | On hand | Per box | Adjustment |
| 2 | Cordless drill | 47 | 12 | -8 |
| 3 | Extension lead | 140 | 24 | 15 |
| 4 | Safety goggles | 63 | 18 | 0 |
| 5 | Work gloves | 210 | 30 | -22 |
Worked examples
=GCD(B3, B5)Result: 70
The largest batch size that divides both 140 and 210 evenly — two and three batches respectively.
=LCM(C2, C4)Result: 36
Boxes of 12 and boxes of 18 first agree at 36 items, which is three of one and two of the other.
=GCD(B2, C2)Result: 1
47 and 12 share no factor above 1, which means 47 items cannot be split evenly into boxes of 12 at all.
=LCM(C2, C3, C4)Result: 72
Both accept more than two arguments — here the common multiple of 12, 24 and 18.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
#NUM!
Why it happens: One of the arguments is negative, or the LCM result exceeds Excel's numeric limit.
How to fix it: Both functions require values of zero or above. Wrap inputs in ABS if a negative might appear.
#VALUE!
Why it happens: An argument is text.
How to fix it: All arguments must be numeric.
Decimals seem to be ignored
Why it happens: Both truncate to whole numbers before calculating. GCD(4.7, 12) treats the first as 4.
How to fix it: That is documented behaviour, not a bug. Multiply up to whole numbers first if the decimals matter.
Tips worth knowing
- GCD of 1 means the numbers share no common factor, which is a useful test in its own right.
- Simplify a ratio by dividing both sides by their GCD.
- LCM answers "when do these two cycles coincide?" for schedules and maintenance intervals.
- Both accept ranges, so =GCD(A2:A20) finds the common divisor of a whole column.
Frequently asked questions
What is GCD used for in a spreadsheet?
Simplifying ratios and splitting quantities into equal groups. Given two quantities, the GCD is the largest group size that divides both without leftovers, which is exactly what you need when packing two products into matching batches.
What does a GCD of 1 mean?
That the numbers are coprime — they share no whole-number factor other than 1. Practically, it means there is no group size above one that divides both evenly.
Why does GCD ignore my decimals?
Both GCD and LCM truncate their arguments to whole numbers before calculating, because greatest common divisors are only defined for integers. If your decimals matter, scale everything up by a power of ten first.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.