How to calculate ratio in Excel

Microsoft Excel is the best software for numeric data calculations. Microsoft Excel is famous due to its fast speed calculations and complex numeric problem solving tools and functions. A ratio is one of the common methods to compare two values with each other. And it can help you to compare variations between those two values. Even in our daily life, we use the word RATIO to define the comparison between two different things. When it comes to Excel, we don’t have any specific single function which can help us to calculate a ratio, but you can calculate it using some custom formulas.

In this tutorial, we will learn to find ratios by two different methods.

Method 1 – using basic ratio method

We can use this method when the larger value is divisible by the smaller value. We will use the ratio symbol and the value. This method has limitations. If the divisor is greater than divisible then it will give values in decimals. In the below example;

Step 1 – Select the cell and apply formula

• Select any empty cell where you wish to find the ratio.
•  In this case we selected C14.
• Divide the two values and use the ratio symbol and place the second value.

In the above case when the large value is divisible by a small value it gives only the whole number results. In other cases it has given decimal values.

Method 2 – By GCD function

There is no single function in excel to calculate the ratio for you. But, the GCD function is close enough. It can help you to get a common denomination for both of the values and then by using little concatenation, we can calculate the ratio. The formula is 

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

Step 1 – Select the cell and apply GCD

• Select any cell where you wish to find ratios.
• Type the formula =E6/GCD(E6,F6)&”:”&F6/GCD(E6,F6)
• Press the enter key.
• Ratios will be calculated and the numbers will be shown in proper ratios without any decimal points.

In the above shown animation we have calculated ratios among two numbers. If the divisor is large and divisible is small GCD covers the decimal values.