In this article, we will learn about Ratio in Excel. Here, we will explore everything about ratios and formulas for calculating the ratio.

A ratio is used to compare two or more numbers and get a clear idea about the relationship between these values. You can calculate the ratio between 2 numbers and sometimes you will need to calculate the ratio between 3 numbers. The male-female ratio is also important in some cases like data analysis or reports. After reading this article you will have a clear idea about ratios.

**Download Practice Workbook**

You can download the practice workbook from here.

**Table of Contents**Expand

## How to Calculate Ratio in Excel

### 1. Use Simple Division to Calculate Ratio

- Select the cell where you want to calculate the ratio >> Write the following formula:

`=C5/D5&":"&1`

- Press
**Enter**>> Drag the**Fill Handle**down to copy the formula in other cells.

**C5**is divided by the value in cell

**D5**, and the

**Ampersand (&)**operator is used to join the result with

**“:”**and

**1**.

### 2. Calculate the Ratio Using Excel GCD Function

- Select the cell where you want to calculate the ratio >> Write the following formula.

`=C5/GCD(C5,D5)&":"&D5/GCD(C5,D5)`

- Press
**Enter**>> Drag the**Fill Handle**down to copy the formula in other cells.

**🔎 ****How Does the Formula Work?**

**GCD(C5,D5):**Here, the**GCD**function returns the greatest common divisor of the numbers in cells**C5**and**D5**.**C5/GCD(C5,D5):**The value in cell**C5**is divided by the**GCD**of**C5**and**D5**here.**D5/GCD(C5,D5):**Now, the value in cell**D5**is divided by the**GCD**of**C5**and**D5**.**C5/GCD(C5,D5)&”:”&D5/GCD(C5,D5):**Finally, the**Ampersand (&)**operator joins these**2**formulas with a**Colon (:)**.

### 3. Use SUBSTITUTE And TEXT Functions to Calculate Ratio

- Select the cell where you want to calculate the ratio >> Write the following formula.

`=SUBSTITUTE(TEXT(C5/D5,"####/####"),"/",":")`

- Press
**Enter**>> Drag the**Fill Handle**down to copy the formula in other cells.

**🔎 ****How Does the Formula Work?**

**TEXT(C5/D5,”####/####”):**Here,**the TEXT function**returns the result in the desired format.**SUBSTITUTE(TEXT(C5/D5,”####/####”),”/”,”:”):**Now,**the SUBSTITUTE function**replaces**Slash (/)**with a**Colon (:)**.

### 4. Apply the ROUND Function to Calculate the Ratio

- Select the cell where you want to calculate the ratio >> Write the following formula.

`=ROUND(C5/D5,2)&":"&1`

- Press
**Enter**>> Drag the**Fill Handle**down to copy the formula in other cells.

**🔎 ****How Does the Formula Work?**

**ROUND(C5/D5,2):**Here, the**ROUND**function rounds the result to the given number of decimal places, which in this case is**2**.**ROUND(C5/D5,2)&”:”&1:**Finally, the**Ampersand (&)**operator joins the result with a**Colon (:)**and**1**.

## How to Calculate Ratio Percentage in Excel

- Select the cell where you want to calculate the ratio percentage >> Write the following formula.

`=C5/D5`

- Press
**Enter**>> Drag the**Fill Handle**down to copy the formula in other cells.

**For this method, you must select the cell format as Percentage.**

*Note:*## Things to Remember

- As Excel does not provide a function for calculating ratios, you have to use functions according to your need to calculate Ratio in Excel.
- The second number can not be zero. Otherwise, it will return an error.

## Frequently Asked Questions

**1. What Is the Formula for the Ratio of 2 Numbers?**

If the numbers are considered as **x **and **y **then the formula for the ratio will be **x/y**.

**2. How to Calculate the Ratio of 3 Numbers?**

You can use the **GCD **function to calculate the ratio of **3 **numbers.

## Conclusion

In this article, we learned about Ratio in Excel. We explored different types of ratios like average or percentage. Here, we also calculated ratios for different situations. We hope this article was helpful to you. If you have any questions or queries, you can let us know in the comment section below.

## Ratio in Excel: Knowledge Hub

- How to Calculate Ratio of 3 Numbers in Excel
- How to Calculate Average Ratio in Excel
- How to Calculate Male Female Ratio in Excel
- How to Calculate Sortino Ratio in Excel
- How to Calculate Sharpe Ratio in Excel
- How to Calculate Odds Ratio in Excel
- How to Calculate Compa Ratio in Excel
- How to Calculate Current Ratio in Excel

**<< Go Back to How to Calculate in Excel | Learn Excel**