How to Calculate Ratios in Excel

It’s a common task to calculate ratios when working with financial data or doing other data analysis work in Excel.

Even in our day-to-day life, we use ratios in areas such as screen size (in specifying resolutions such as 16:9) or cooking (in specifying proportions such as using two ingredients in 2:3 proportion).

While there is no inbuilt formula in Excel to calculate ratios, there are a couple of simple workarounds you can use to do this.

Calculate Ratios Using the GCD Function

When calculating the ratios of two numbers, we have to find out the lowest proportion in which we can write it.

For example, If we want to calculate the ratio of 1920 and 1080 (the screen dimension of a 1080p resolution), the answer would be 16:9.

While we could have also used 1920:1080, it’s not the most simplified ratio, so we divided both these values by 480 (which is the Greatest Common Divisor that divides both numbers).

So if we want to calculate ratios in Excel, The first step would be to calculate the Greatest Common Divisor for the given numbers. Thankfully, Excel already has an inbuilt function to calculate the GCD for us.

Let me show you how it works.

Below I have a data set where I have the width value in column A and the height value in column B, and I want to calculate the ratio of these two numbers.

Before getting the ratio, I would have to calculate the GCD value, which can be done using the below formula.

=GCD(A2,B2)

Enter this formula in cell C2 and copy it for the entire column to get the GCD value for the entire data set.

Once I have the GCD value, I can calculate the ratio by using the below formula:

=A2/C2&":"&B2/C2

The above formula divides the values in A2 and B2 by the Greatest Common Divisor value in cell C2.

And then, both these values (A2/C2 and B2/C2) are then combined Using the ampersand ‘&’ operator, and a colon is inserted in between the values.

If you do not want to create an additional column that calculates the GCD value, you can use the below formula, where the GCD values are calculated in the formula itself:

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

Note: In this example, I have shown you how to calculate the ratio for two numbers. You can follow the same process to calculate the ratio for three numbers as well.

Also read: Convert Decimal To Fraction In Excel

Calculate Ratios Using Custom Number Formatting

There is also a less-known custom formatting technique that allows you to format numbers as ratios.

The only drawback of this method is that it will give you the ratio with a forward slash (/) instead of the colon (:).

Let me show you how it works.

Below I have the data set where I have the width value in column and height value in column B, and I want to calculate the ratios in column C.

Here are the steps to do this:

1. Enter the following formula in cell C2

=A2/B2

1. Copy this formula for the entire column.

1. Select the entire data set that has the values in column C (C2:C7 in our example)
2. Hold the Control key and then press the 1 key. This will open the ‘Format Cells’ dialog box.
3. Within the Number group, select the Custom option.

1. Enter the following format in the Type: field.

#/#

1. Click OK

When you apply the above custom number format, it will show you the result of the division in the ratio format (except that there would be a forward slash instead of a colon in between the two numbers).

One benefit of this method is that the result in the cells showing the ratios still contains the number that you got after dividing values in column A and column B. So you see 16:9 in cell C2, the value in the backend is still 1.77778 (which we got when we divided 1600 by 900). So while you see a ratio in the cell, you can use the decimal value in the back end in calculations

Calculate Ratios Using the TEXT Function

Another formula you can use to calculate ratios in Excel is by using the TEXT function, along with the SUBSTITUTE function.

The TEXT function allows you to use a custom number format to calculate the ratio, and then the SUBSTITUTE function replaces the forward-slash (/) with the colon (:).

Let me show you how it works.

Below I have the same data set where I have the width value in column A and the height value in column B, and I want to get the ratio in column C.

Here is the formula that would give us the ratio:

=SUBSTITUTE(TEXT(A2/B2,"#/#"),"/",":")

Enter this formula in cell C2 and copy it for all the cells in the column.

Let me explain how this formula works:

1. TEXT function takes two arguments, the value (which is where we have calculated the ratio using A2/B2) and the format in which it should be displayed (which is “#/#”). The result of the TEXT function would be 16/9 in cell C2.
2. SUBSTITUTE function Is then applied on the result of the TEXT function to replace the forward slash (/) with a colon (:)

The result that you get from this formula is a text value, Which means that you can append text before or after the ratio if you want.

For example, I can use the below formula to append the text “Resolution” followed by the ratio value:

="Resolution - "&SUBSTITUTE(TEXT(A2/B2,"#/#"),"/",":")

In this article, I’ve covered three methods you can use to calculate ratios in Excel. The easiest way would be to calculate the Greatest Common Divisor value using the GCD function in Excel and then use it to calculate the ratio.

In many cases, I prefer using the custom formatting method, where I am ok showing a forward slash instead of the colon. I hope you found this Excel tutorial useful.

Conclusion

Calculating ratios in Excel is a crucial skill for financial data analysis and everyday tasks. Whether it's finding screen dimensions, cooking proportions, or complex financial modeling, these methods will help you calculate ratios effortlessly. Choose the method that suits your needs best, whether it's using the GCD function, custom number formatting, or the TEXT and SUBSTITUTE functions. Excel's versatility allows you to handle ratios efficiently, empowering you to make better data-driven decisions.