How to Calculate Ratio Between Two Numbers in Excel (Formula & Examples)

Learn how to calculate the ratio between two numbers in Excel using simple formulas, the GCD function, and practical examples. Includes step-by-step i

 

How to Calculate Ratio Between Two Numbers in Excel (Formula & Examples)

   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/B2

Press 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:1

This 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/A2

Then 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/B2

If 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

=A2/B2

Display a ratio for whole-number results

=A2/B2&":1"

Simplify a ratio

=A2/GCD(A2,B2)&":"&B2/GCD(A2,B2)

Calculate percentage

=A2/B2

Avoid division by zero

=IF(B2=0,"Error",A2/B2)

Simplified ratio with zero check

=IF(OR(A2=0,B2=0),"Check values",A2/GCD(A2,B2)&":"&B2/GCD(A2,B2))

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

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content