Learn how to calculate the ratio between two numbers in Excel with simple formulas and practical examples. Discover how to use division and the GCD function to simplify ratios, compare values, calculate percentages, and automate ratio calculations.
How to Calculate Ratio Between Two Numbers in Excel (Formula & Examples)
Calculating the ratio between two numbers is a simple way to understand how one value compares with another. Ratios are commonly used in business, finance, education, sales reports, budgeting, and everyday calculations.
For example, if a company sells 40 laptops and 20 desktop computers, the ratio of laptops to desktops is 2:1. This means the company sells twice as many laptops as desktop computers.
You can calculate ratios manually, but Microsoft Excel makes the process much faster. With a few simple formulas, you can compare two numbers, simplify ratios, and calculate multiple ratios automatically.
In this guide, you'll learn how to calculate the ratio between two numbers in Excel using practical examples, formulas, and step-by-step instructions.
What Is a Ratio?
A ratio shows the relationship between two or more numbers. It explains how much one quantity compares with another.
For example:
10:5 means the first value is twice the second value.
15:5 means the ratio is 3:1.
20:10 means the ratio is 2:1.
Ratios are usually written using a colon (:), but Excel can calculate them using division and other mathematical functions.
Example: Suppose a shop sells 30 apples and 15 oranges. The ratio of apples to oranges is:
30:15 = 2:1
This means that for every 2 apples sold, 1 orange is sold.
How to Calculate Ratio Between Two Numbers in Excel
Excel doesn't have a dedicated RATIO function, but you can easily calculate ratios using division or formulas.
Let's understand the process with a simple example.
Suppose you have two numbers:
Number 1: 40
Number 2: 20
You want to find the ratio between these values.
Step 1: Enter Your Numbers in Excel
Open a new Excel worksheet and enter the following data:
Cell | Value |
|---|---|
A1 | Number 1 |
B1 | Number 2 |
A2 | 40 |
B2 | 20 |
Step 2: Calculate the Ratio Using Division
Click on cell C2 and enter this formula:
=A2/B2Press Enter.
Result: 2
The result is 2, which means the first number is twice the second number.
Step 3: Display the Ratio Using a Colon
If you want to display the result in the traditional ratio format (2:1), use:
=A2/B2&":1"The result will be:
2:1This formula works when the division produces a whole number. For other values, use the simplified ratio method explained below.
How to Calculate a Simplified Ratio in Excel
Sometimes, the two numbers in a ratio can be divided by a common factor to make the ratio smaller and easier to understand.
For example:
Original ratio: 40:20
Common divisor: 20
Simplified ratio: 2:1
Excel provides the GCD function to find the greatest common divisor of two numbers.
Use the following formula:
=A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)If A2 contains 40 and B2 contains 20, Excel returns:
2:1
This formula automatically simplifies the ratio without requiring manual calculations.
How to Calculate Ratio Between Two Numbers Using GCD
The GCD function is particularly useful when working with larger numbers.
GCD stands for Greatest Common Divisor. It finds the largest number that can divide both values without leaving a remainder.
For example, suppose you want to calculate the ratio between 120 and 80.
Enter the following formula:
=A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)Excel performs the following calculation:
Calculation | Result |
|---|---|
First number | 120 |
Second number | 80 |
GCD | 40 |
120 ÷ 40 | 3 |
80 ÷ 40 | 2 |
Simplified ratio | 3:2 |
This method is useful for comparing sales, expenses, employee counts, inventory, and other business figures.
How to Calculate Ratio for Multiple Rows in Excel
If you have a large dataset, you don't need to calculate each ratio individually. You can use one formula and copy it down the column.
For example, imagine you manage a shop and want to compare product sales.
Product | Apples | Oranges | Ratio |
|---|---|---|---|
Shop A | 40 | 20 | 2:1 |
Shop B | 60 | 30 | 2:1 |
Shop C | 90 | 60 | 3:2 |
Shop D | 100 | 25 | 4:1 |
Assume:
Column A contains shop names.
Column B contains apples sold.
Column C contains oranges sold.
Column D displays the simplified ratio.
Enter this formula in cell D2:
=IF(OR(B2="",C2=""),"",IF(OR(B2=0,C2=0),"Check values",B2/GCD(B2,C2)&":"&C2/GCD(B2,C2)))Press Enter and drag the formula down to calculate the ratios for the remaining rows.
Excel will automatically adjust the cell references for each row.
This approach is especially helpful when comparing hundreds of products, sales figures, or financial records.
How to Calculate Ratio as a Percentage in Excel
Sometimes, you may want to express the relationship between two numbers as a percentage instead of using a colon.
For example, if your business earns ₹80,000 in sales and spends ₹20,000 on advertising, you may want to know how much the advertising expense represents compared with sales.
Use this formula:
=B2/A2Then apply the Percentage format from Excel's Home tab.
If:
Total sales = ₹80,000
Advertising expense = ₹20,000
The calculation is:
20,000 ÷ 80,000 = 25%
This means advertising expenses represent 25% of total sales.
Remember that a percentage comparison and a simplified ratio communicate the relationship differently.
How to Calculate Ratio When One Number Is Zero
Excel cannot calculate a meaningful division result when the second number is zero.
For example:
=A2/B2If B2 contains zero, Excel returns a #DIV/0! error.
To avoid this problem, use an IF function:
=IF(B2=0,"Cannot calculate",A2/B2)If you are calculating a simplified ratio, you can use:
=IF(OR(A2=0,B2=0),"Check values",A2/GCD(A2,B2)&":"&B2/GCD(A2,B2))This formula prevents division errors and displays a helpful message when either number is zero.
Note: A zero value may have a specific meaning in your dataset. Check the source values before interpreting the result.
Excel Formulas for Calculating Ratios
Here is a quick reference table containing useful Excel formulas for different ratio calculations.
Purpose | Excel Formula |
|---|---|
Basic ratio using division |
|
Display a ratio for whole-number results |
|
Simplify a ratio |
|
Calculate percentage |
|
Avoid division by zero |
|
Simplified ratio with zero check |
|
For percentage calculations, format the result cell as Percentage. The simplified ratio formula is intended for non-negative whole numbers.
Practical Uses of Ratio Calculations in Excel
Ratio calculations are useful in many everyday situations. Here are some practical examples:
Business and Sales: Compare the number of products sold, sales revenue, or customer purchases.
Finance and Budgeting: Compare income with expenses, savings with spending, or assets with liabilities.
Education: Calculate the ratio of boys to girls in a classroom or compare students who passed and failed an examination.
Inventory Management: Compare available stock of two different products.
Marketing: Measure the relationship between advertising costs and sales revenue.
For example, if a company spends ₹10,000 on advertising and generates ₹50,000 in sales, the advertising-to-sales ratio is 1:5.
These comparisons help businesses understand their numbers and make better decisions.
Common Mistakes to Avoid When Calculating Ratios in Excel
Even though ratio calculations are straightforward, a few common mistakes can produce incorrect results.
Reversing the numbers: A ratio of 40:20 is different from 20:40. Always check which value should come first.
Ignoring zero values: Dividing by zero produces an Excel error.
Not simplifying ratios: A ratio of 40:20 is correct, but 2:1 is easier to understand.
Using percentages incorrectly: A ratio and a percentage are related but are not the same format.
Using decimal values with GCD: Excel's GCD function is designed for integer inputs. Scale decimal values into integers before simplifying if necessary.
Checking these details will help you produce accurate and easy-to-read Excel reports.
Frequently Asked Questions (FAQs)
1. How do I calculate the ratio between two numbers in Excel?
Use =A2/B2 to divide the first number by the second. To display a simplified ratio, use the GCD function.
2. Which Excel function is used to simplify ratios?
The GCD function finds the greatest common divisor of two numbers and helps simplify ratios.
3. How do I display a ratio with a colon in Excel?
Use =A2/GCD(A2,B2)&":"&B2/GCD(A2,B2) to display a simplified ratio such as 2:1.
4. Can Excel calculate ratios automatically for multiple rows?
Yes. Enter the ratio formula in the first row and drag the fill handle down to apply it to other rows.
5. Can I calculate a ratio as a percentage in Excel?
Yes. Divide the first number by the second using =A2/B2 and apply Percentage formatting to the result cell.
Final Thoughts
Calculating the ratio between two numbers in Excel is easy once you understand the basic formulas. For simple comparisons, division is enough. However, if you want to display ratios in a clean and simplified format, the GCD function is a better choice.
Whether you're comparing product sales, business expenses, student results, or financial figures, Excel can handle ratio calculations quickly and accurately.
Save the simplified ratio formula in your worksheet so you can reuse it whenever you need to compare two numbers.

COMMENTS