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`

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.

- 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`

- 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)`

- 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`

- 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**

**Convert Percentage to Ratio in Excel (4 Easy Ways)****How to Calculate Odds Ratio in Excel****Convert Ratio to Decimal in Excel (3 Handy Methods)****How to Calculate Male Female Ratio in Excel (3 Suitable Methods)****Calculate Ratio Percentage in Excel (4 Easy Methods)**

**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,"##/##"),"/",":")`

- 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`

- 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

**How to Calculate Compa Ratio in Excel (3 Suitable Examples)****Calculate Average Ratio in Excel (2 Simple Ways)****How to Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)****Calculate Sharpe Ratio in Excel (2 Common Cases)****Debt Service Coverage Ratio Formula in Excel****How to Calculate Sortino Ratio in Excel (2 Methods)**

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.