Written and maintained by CASRAI Editorial Board
Last updated
To compute a ratio in Excel, divide one number by the other, or, to get the familiar A:B form, divide both numbers by their greatest common divisor with the GCD function: =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2). For 24 and 36 this returns 2:3. This guide gives that formula, a decimal version, a percentage version, rounding, and the errors you are most likely to hit. All example numbers are made up for illustration.
What it is used for in research
A ratio compares two quantities of the same kind: cases to controls, treated samples to untreated samples, funded proposals to submitted proposals. Reporting it as 2:3, as 0.67 or as 66.7% are three presentations of the same relationship, and which one suits a table depends on the reader. The sections below show all three so you can pick on purpose. If you are unsure whether a ratio is even meaningful for your variable, see ratio scale examples; for a ratio of odds in a study, see the dictionary entry on the odds ratio, which is a different quantity from the simple ratio computed here.
Install or access (official source only)
Nothing needs installing beyond Excel itself. The three functions used here are documented by Microsoft on its own support site, and the exact pages are listed under Sources: GCD, TEXT and ROUND.
Step-by-step: how to calculate a ratio in Excel
Put the first quantity in A2 and the second in B2. The examples use A2 = 24 and B2 = 36.
Ratio formula in Excel: the A:B form with GCD
- Click an empty cell, for example C2.
- Type
=A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)and press Enter. - Excel divides each number by their greatest common divisor and joins the two results with a colon.
Microsoft’s GCD page gives =GCD(24,36) = 12, so 24/12 = 2 and 36/12 = 3, and the cell shows 2:3. The result is text, not a number, so you cannot do arithmetic on it; keep the numeric ratio in another column if you need one.
How to do a ratio in Excel as a decimal
- In D2, type
=A2/B2to get the ratio of A to B as a decimal (0.6667 for 24 and 36). - To round it, type
=ROUND(A2/B2,2), which returns 0.67. - To express it as “x to 1”, divide the other way:
=ROUND(B2/A2,2)&":1"returns 1.5:1.
Microsoft documents ROUND(number, num_digits): a positive num_digits sets the decimal places, 0 rounds to the nearest integer, and a negative value rounds to the left of the decimal point. Its examples include =ROUND(2.15,1) = 2.2 and =ROUND(2.149,1) = 2.1.
Ratio as a percentage
Type =TEXT(A2/B2,"0.0%"). With 24 and 36 it returns 66.7%. The TEXT function takes a value and a format_text string that defines the formatting; Microsoft’s own example is =TEXT(0.285,"0.0%"), which shows 28.5%. Like the A:B formula, TEXT returns text.
Which version to use
| You want | Formula | Result for 24 and 36 |
|---|---|---|
| Simplest whole-number form | =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2) |
2:3 |
| Decimal ratio, rounded | =ROUND(A2/B2,2) |
0.67 |
| “x to 1” form | =ROUND(B2/A2,2)&":1" |
1.5:1 |
| Percentage | =TEXT(A2/B2,"0.0%") |
66.7% |
Results are shown for the example values 24 and 36.
Output and how to read it
A 2:3 result means that for every 2 units of the first quantity there are 3 of the second. A decimal of 0.67 means the first is about two-thirds the size of the second. A percentage of 66.7% says the same thing as a share of the second quantity. Order matters: swapping A and B inverts the ratio (3:2, 1.5, 150%). Label the columns so a reader knows which quantity comes first.
Decimal inputs and the GCD limit
GCD works on whole numbers. Microsoft states that a non-integer argument is truncated, so =GCD(2.5,1.5) would be computed on 2 and 1, not 2.5 and 1.5. To keep decimals, scale both numbers to whole numbers first. For 2.5 and 1.5, multiply both by 10:
=ROUND(A2*10,0)/GCD(ROUND(A2*10,0),ROUND(B2*10,0))&":"&ROUND(B2*10,0)/GCD(ROUND(A2*10,0),ROUND(B2*10,0))
This evaluates 25 and 15, whose greatest common divisor is 5, and returns 5:3. Use 100 instead of 10 for two decimal places. Wrapping each product in ROUND, as above, makes sure the scaled value is a whole number before GCD truncates it.
Troubleshooting
- #VALUE!: Microsoft documents this for GCD when an argument is nonnumeric. Check that A2 and B2 hold numbers, not text such as “24 mg”.
- #NUM!: GCD returns this if an argument is less than zero, or if it is 2^53 or larger. The A:B method therefore does not work directly with negative values.
- Unexpected 1 or a ratio that is not simplified: if the inputs were decimals, GCD truncated them. Scale to whole numbers as shown above.
- Result will not calculate: the A:B and TEXT results are text. Use the plain division formula for any further calculation.
GCD accepts 1 to 255 values, which is useful for reducing a three-part ratio such as A:B:C: divide each of the three cells by GCD(A2,B2,C2) and join them with colons.
Free alternatives
| Tool | What its official help documents |
|---|---|
| Google Sheets | A GCD function with the syntax GCD(value1, [value2, ...]); Google documents that any input with a decimal part is silently truncated. The same divide-by-GCD approach therefore applies, with the same whole-number caveat. |
FAQ
How do I calculate a ratio in Excel?
Divide one cell by the other (=A2/B2) for a decimal ratio, or divide both by their GCD and join with a colon for the A:B form. The step-by-step section above has each formula.
How do I make a ratio in Excel that reads 2:3 rather than 0.67?
Use =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2). The colon is a text string joined to the two divided values.
How do I show a ratio as a percentage?
Use =TEXT(A2/B2,"0.0%"), or divide and apply a percentage number format to the cell.
Why does my ratio formula show #NUM!?
GCD returns #NUM! for negative arguments and for arguments of 2^53 or more, per Microsoft’s GCD documentation.
What is the formula for a ratio in Excel?
There is no single ratio function in the three documented here: the ratio formula in Excel combines division with GCD, for example =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2), or plain division =A2/B2 for a decimal.
Can I calculate ratios in Excel with decimals?
Yes, but scale both numbers to whole numbers first, because GCD truncates non-integers. Or skip GCD and use the decimal ratio with ROUND.
Related spreadsheet guides: how to calculate CAGR in Excel and coefficient of variation in Excel.
Sources
- Microsoft Support, GCD function, checked 2026-10-29.
- Microsoft Support, TEXT function, checked 2026-10-29.
- Microsoft Support, ROUND function, checked 2026-10-29.
- Google Docs Editors Help, GCD, checked 2026-10-29.
