Elite Membership

Ratio In Excel Formula

Written by WallStreetMojo Team WallStreetMojo Team Content Writers & Editors Our team of international writers & editors do in-depth research and fact check while creating informative articles, covering all aspects from basic to advanced. They employ their expertise to bring best possible content to our readers. 20+ years of experience Finance Excel View Full Profile
Reviewed by Dheeraj Vaidya, CFA, FRM Dheeraj Vaidya, CFA, FRM Content Reviewer & Course Director A former J.P.Morgan and CLSA Equity Analyst, Dheeraj specializes in financial modeling, AI, forecasting, and valuations. In his career spanning almost two decades, he has trained and mentored more than 100,000 students and professionals on a range of topics. 20+ years of experience CFA, FRM, IIT Delhi, IIM Lucknow Financial Modeling View Full Profile
Updated May 14, 2025
Read Time 5 min

What Is Ratio In Excel Formula?

The ratio usually compares the values. It helps us to tell how much one value is smaller or bigger than the other. In mathematics, a ratio is a relationship between two values showing how many times the first value contains the additional value.

Download FREE Ratio Formula In Excel Template and Follow Along!
Download Excel Template

For example, suppose in a dataset, we have the value 5 in cell A2 and 30 in cell B2, and we need to determine the ratio. In this scenario, we can use the RATIO formula in Excel. In this example, we can use the following formula in cell C2:

=(A2/B2)&”:”&(B2/B2)

=1:6.

It helps to define the comparisons between two different values or things.

Ratio In Excel Formula

Key Takeaways

  • The ratios in Excel compare values to determine their relative size. In math, ratios show the relationship between two values, indicating how many times the first value contains the second.
  • When calculating the ratio of numeric values in Excel, one must ensure that both values are positive and the second value is not zero.
  • In Excel, there is no specific formula for calculating ratios. Users need to rely on other functions and formulas based on their requirements.
  • It is crucial to understand these functions and apply them to data sets accurately for meaningful results.

How To Calculate Ratio In Excel?

In Excel, there is no specific function to calculate the ratio. Instead, there are four functions to calculate the ratio in the Excel formula, which we can use per the requirements.

Following are the functions to calculate the ratio in the Excel formula:

  1. Simple Divide Method
  2. GCD Function
  3. SUBSTITUTE and TEXT Function
  4. ROUND Function

Let us learn how to calculate the ratio in the Excel formula with the help of a few examples of each function.

#1 – Simple Divide Function

The formula for the Simple Divide Method in Excel:

= value1/ value2 &”:“&“1”

Example Of Simple Divide Function

We have data where we have two values. We will have to calculate the ratio of the two numbers.

Steps to calculate the ratio in Excel formula with the help of the simple divide function are shown below:

  • Data with two values, as shown below:
calculate ratio in excel example 1.1
  • Now, select the cell in which you want the ratio of the given values, as shown below.
calculate ratio in excel example 1.2
  • Now, use simple divide function to calculate the ratio as =first value/second value&” : “ & “1”.
calculate ratio in excel example 1.3
  • Press the “Enter” key to see the result after using the simple divide function.
calculate ratio in excel example 1.4
  • Now, drag the function to the below cells to calculate the ratio of complete data and find the result as shown below.
calculate ratio in excel example 1.5

#2 – GCD Function

A formula for GCD Function:

= value1 / GCD (value1, value2) &”:“& value2 / GCD (value1, value2)

Example Of GCD Function

We have data where we have two values. We will have to calculate the ratio of two numbers in Excel.

Steps to calculate the ratio in Excel formula with the help of the GCD function are shown below:

  • Data with two values, as shown below:
calculate ratio in excel example 2.1
  • First, calculate the GCD to make it less complicated than GCD (first value, second value).
calculate ratio in excel example 2.2
  • Now, use the GCD function method to calculate the ratio as =first value/ GCD (first value, second value) & ” : “ & second value/ GCD (first value, second value).
calculate ratio in excel example 2.3
  • Press the “Enter” key to see the result after using the GCD function.
calculate ratio in excel example 2.4
  • Now, drag both the functions to the below cells to calculate the ratio of complete data and find the result as shown below.
calculate ratio in excel example 2.5

#3 – SUBSTITUTE And TEXT Function

A formula using the SUBSTITUTE Function in excel and Text Function in excel.

= SUBSTITUTE (TEXT (value1/value2, “#####/#####”),”/”, “:”)

Example of SUBSTITUTE and Text Function

We have data where we have two values. We will have to calculate the ratio of the two numbers.

Steps to calculate the ratio with the help of the SUBSTITUTE and TEXT functions are shown below:

  • Data with two values, as shown below:
calculate ratio in excel example 3.1
  • Now, select the cell in which you want the ratio of the given values, as shown below.
  • Now, use the SUBSTITUTE and TEXT function to calculate the ratio as =SUBSTITUTE(TEXT(first value/second value, “#####/#####”),”/”, “:”).
calculate ratio in excel example 3.2
  • Press the “Enter” key to see the result after using the SUBSTITUTE and TEXT functions.
example 3.3
  • Now, drag the function to the below cells to calculate the ratio of complete data and find the result as shown below.
example 3.4

#4 – ROUND Function

A formula for Round Function.

= ROUND (value1/value2, 1) &“:”& 1

Example of Round Function

We have data where we have two values. We will have to calculate the ratio of the two numbers.

Steps to calculate the ratio of two numbers with the help of the ROUND function are shown below:

  • Data with two values, as shown below:
example 3.1
  • Now, select the cell in which you want the ratio of the given values, as shown below.
example 1.2
  • Now, use the ROUND function to calculate the ratio as =ROUND( first value/second value,1)&“:”&1
example 4.1
  • Press the “Enter” key to see the result after using the ROUND function.
example 4.2
  • Now, drag the function to the below cells to calculate the ratio of complete data and find the result as shown below.
example 4.3

Important Things To Note

  • There should be numeric values for which you want to calculate the ratio in Excel.
  • Both the values should be positive, and the second should not be zero.
  • There is no specific function to calculate the ratio; as per the requirement, we can use the functions.

Frequently Asked Questions (FAQs)

What is current ratio in Excel?

The current ratio is a financial metric that determines whether a company’s short-term assets can cover its short-term liabilities and outstanding payments. If the ratio is below 1, it indicates that the company may face difficulty in paying its debts during a recession, downturn, or other unfavorable economic conditions.

How do you divide ratios in Excel?

The ratio can be calculated using the simple divide function, which is equal to the first value divided by the second value.

How do I convert percentage to ratio in Excel?

To convert a percentage to a ratio, one must remove the percentage symbol, and then divide the value by 100, and convert the fraction to a reduced fraction.

Recommended Articles

This article is a guide to Calculate Ratio in Excel formula. Here, we discuss calculating ratios in Excel using 1) Simple Divide function 2) GCD function 3) SUBSTITUTE and TEXT functions, and 4) ROUND function with examples and downloadable Excel templates. You may also look at these useful functions in Excel: –