How to Calculate Ratio in Excel (4 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

The ratio is one of the common ways to compare two values. It indicates the amount of difference between two values from each other. In Excel, we can calculate this ratio. But there is no direct formula for that. Here, we will show how to calculate the ratio by customizing different functions.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Calculate Ratio in Excel

We will calculate the ratio in Excel using 4 different methods. We will compare the length and width of 5 wheat fields of a farmer.


1. Calculate Ratio with the Help of Ampersand (&) Symbol

In this section, we will use the Ampersand (&) symbol to determine the ratio. Before using the Ampersand symbol, we will divide one value by the other and then add the Ampersand symbol with the division inside the same formula. Follow the steps below:

📌 Steps:

  • Go to Cell D4.
  • Put the following formula based on division and ampersand symbol.
=B4/C4&":"&1

Calculate Ratio with the Help of Ampersand (&) Symbol

Here, we divide the length by width. Then, add the Colon(:) symbol using the ampersand. Lastly, add 1 as the right-side value of the ratio.

  • Then, press the Enter button.

Calculate Ratio with the Help of Ampersand (&) Symbol

  • Now, pull the Fill Handle icon downwards.

We’ve got the ratio.

  • We can slightly modify the formula. And the formula becomes:
=B4/C4&":"&C4/C4

Calculate Ratio with the Help of Ampersand (&) Symbol

  • Hit the Enter button and drag the Fill Handle icon.

We get the same result.

Note:

The disadvantage with this method is- we do not get integer numbers on both sides.

Read More: How to Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)


2. Using Excel GCD Function

The GCD function returns the greatest common divisor.

The GCD function finds out the biggest common divisor of multiple numbers. This GCD can divide that number without any reminder. In this section, we will find out the GCD of two numbers and then use this GCD to calculate the ratio.

📌 Steps:

  • We add a column named GCD to the dataset.

  • We will calculate GCD now. Put the following formula on Cell D4.
=GCD(B4,C4)

Determine Ratio with Excel GCD Function

  • Double click the Fill Handle icon to get values for the rest of the cells.

We get the GCD for all the data cells. Now, we will use this GCD to calculate the ratio.

  • Go to Cell E4 and write the following formula.
=B4/D4&":"&C4/D4

Determine Ratio with Excel GCD Function

  • Drag the Fill Handle icon now.

We get the ratio for all the data. One thing that needs to mention is that the ratio values are integers.

Note: If you don’t want to add an extra column, then use the following formula in Cell D4:

=B4/GCD(B4,C4)&":"&C4/GCD(B4,C4)

Read More: How to Calculate Ratio Between Two Numbers in Excel (5 Ways)


Similar Readings


3. Combining TEXT and SUBSTITUTE Functions

In this method, we will use the combination of the TEXT and SUBSTITUTE functions to calculate the ratio.

The TEXT function converts a value of text to a specific number format.

The SUBSTITUTE function replaces existing text with new text in the text string.

📌 Steps:

  • Put the following formula on Cell D4.
=SUBSTITUTE(TEXT(B4/C4,"##/##"),"/",":")

Combining TEXT and SUBSTITUTE Functions to calculate ratio

  • Press the Enter button to get the ratio.

  • Pull the Fill Handle icon to get the result for the rest of the cells.

Here, we get the ratio easily.

Read More: How to Calculate Current Ratio in Excel (2 Suitable Examples)


4. Calculate Ratio with ROUND Function

The ROUND function rounds a number to a specified number of digits.

In this section, we will use the ROUND function to get the ratio. This ROUND function is used along with the Ampersand symbol.

📌 Steps:

  • Go to Cell D4 and put the formula below.
=ROUND(B4/C4,1)&":"&1

Calculate Ratio with ROUND Function

  • Hit the Enter button.

  • Now, drag the Fill Handle icon downwards.

Here we used the ROUND function because this will fix the digits after the decimal point to a certain number.

We can also use this formula and it will return the same value.

=ROUND(B4/C4,1)":"&C4/C4

Read More: How to Graph Ratios in Excel (2 Quick Methods)


Conclusion

In this article, we described 4 methods to calculate ratio in Excel. I hope this will satisfy your needs. Please have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

1 Comment
  1. Great tutorial, yet I am stuck at trying to apply a conditional formatting of color scales to a column of ratios – in the same way I could with a column of values.

    Any idea if this is possible and how to achieve?

    I did try finding a way to dynamically copy the colors from a range of the values the ratios represent, and where the conditional formatting did work as I wished. Unfortunately, I couldn’t find a way to dynamically copy conditional format generated colors either.

    Would be pleased to figure out a way to do either! (but the first would obviously be better). Also, FWIW, I am working in Excel 2007.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo