With **ratio**, we can visualize the relative size between two or more variables or numbers, In Excel, we also have to deal with ratios a lot. How can we **calculate the ratio between two numbers** in Excel and determine the percentage after that will discuss here with adequate explanations

## Download Practice Workbook

Download this practice workbook below.

## What Is Ratio?

Two or maybe more numbers can be compared by using a **ratio** to express their relative values. ratio actually achieves this by division actually. here the number which is going to be divided is called antecedent and the other one is called the consequent.

The expression of ratios generally is a:b, where a and b could be integers decimals or even fractions. Ratio values give us a comparatively easier understanding of the comparison between two variables or numbers. Although they are basically the same thing as division mathematically. But the expression is different.

## 4 Suitable Ways to Calculate Ratio Percentage in Excel

We are going to use the below dataset to demonstrate how we are going to determine the Ratio of **Number 1 **to **Number 2 **in the **Ratio** column and the percentage of **Number 1** with respect to the **Number 2 **column in the **Percentage** column.

### 1. Using GCD Function

**The GCD function** will determine the greatest common divisor between the two numbers directly. And then using that number we are going to calculate the ratio between number and percentage thereafter.

**Steps**

- In the beginning, select the cell
**D5**and enter the following formula:

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

- After entering this formula you will notice that the ratio between cell
**B5**and**C5**is now present in cell**D5.**

- Now drag the
**Fill Handle**to cell**D7**. - Then the range of cells
**D5:D7**is now filled with the ratio of number in the range of cell**B5:B7**and range of cells**C5:C7.**

- Now, select the cell
**E5**and enter the following formula:

`=B5/C5`

- Drag the
**Fill Handle**to cell**E7,**you will see the range of cells**E5:E7**now filled with the quotient value of**Number 1**column by**Number 2**columns.

- Now select the range of cells
**E5**to**E7**, and from the**Home**tab, click on the**%**(Percentage) sign on the**Number**group.

- Then the range of cells
**E5**to**E7**now shows the percentage value of**Number 1**of**Number 2**.

This is how we can calculate ratio percentages in Excel using the** GCD** function.

**Breakdown of the Formula**

**GCD(B5,C5)**: This function will return the greatest common divisor between the values in cells**B5**and**C5****B5/GCD(B5, C5)**and**C5/GCD(B5, C5):**They will return the quotient of the division of values in cells**B5**and**C5**by the value returned by the**GCD**function above.**B5/GCD(B5,C5)&”:”&C5/GCD(B5,C5):**Basically this function will return the values returned above with ratio sign**“:”.**

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

### 2. Combining SUBSTITUTE and TEXT Functions

Using **the SUBSTITUTE function** with the combination of **the TEXT function** we will calculate the ratio between two numbers and calculate the percentage.

**Steps**

- In the beginning, select the cell
**D5**and enter the following formula

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

- After entering this formula you will notice that the ratio between cell
**B5**and**C5**is now present in cell**D5.**

- Now drag the
**Fill Handle**to cell**D7**. - Then the range of cells
**D5:D7**is now filled with the ratio of number in the range of cells**B5:B7**and range of cells**C5:C7.**

- Now select the cell
**E5**and enter the following formula:

`=B5/C5`

- Drag the
**Fill Handle**to cell**E7,**you will see the range of cells**E5:E7**now filled with the quotient value of**Number 1**column by**Number 2**columns.

- Now, select the range of cells
**E5**to**E7.**And from the**Home**tab, click on the percentage sign in the**Number**group.

- After clicking on the percentage sign, now the range of cells
**E5**:**E7**is now filled with the percentages of**Number 1**with respect to**Number 2.**

This is how we can calculate ratio percentages in Excel using the** SUBSTITUTE** function.

**Breakdown of the Formula**

**TEXT(B5/C5,”######/######”):**This function will return the quotient of the division of the cell**B5**by**C5**and format it as fraction.**SUBSTITUTE(TEXT(B5/C5,”######/######”),”/”,”:”):**This formula will substitute the “**/**” with the “**:**” in the fraction.

**Read More:** **How to Convert Percentage to Ratio in Excel (4 Easy Ways)**

**Similar Readings**

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

### 3. Applying Simple Division Method

We can simply divide numbers into the first columns by numbers in the second columns to get the ratio. But the output might not be an integer, like other methods.

**Steps**

- In the beginning, select the cell
**D5**and enter the following formula

`=(B5/C5)&":"&"1"`

- After entering this formula you will notice that the ratio between cell
**B5**and**C5**is now present in cell**D5**with respect to 1.

- Now drag the
**Fill Handle**to cell**D7.** - Then the range of cells
**D5:D7**is now filled with the ratio of number in the range of cell**B5:B7**and range of cells**C5:C7**with respect to 1.

- Now select the cell
**E5**and enter the following formula:

`=B5/C5`

- Drag the
**Fill Handle**to cell**E7.**You will see the range of cells**E5:E7**now filled with the quotient value of**Number 1**column by**Number 2**columns. - Then select the range of cells
**D5:D7.**And click on the percentage icon from the**Home**tab in the**Numbers**group.

- After clicking on the percentage icon, now the range of cells
**E5**:**E7**is now filled with the percentages of**Number 1**with respect to**Number 2.**

This is how we can calculate ratio percentages in Excel using simple division.

### 4. Using Combined Formula

The combination of **ROUND **and **TEXT** functions will help us to calculate the ratio of two numbers in the **Number 1 **and** Number 2 **columns. We also use the** LEFT**, **ROUND**, **SEARCH**, and **TEXT **functions in this method.

**Steps**

- In the beginning, select the cell
**D5**and enter the following formula:

`=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)&":"&MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)`

- After entering this formula you will notice that the ratio between cell
**B5**and**C5**is now present in cell**D5.**

- Now drag the
**Fill Handle**to cell**D7.** - Then the range of cells
**D5:D7**is now filled with the ratio of number in the range of cell**B5:B7**and range of cells**C5:C7.**

- In the beginning, select the cell
**E5**and enter the following formula

`=LEFT(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))-1)/MID(TEXT(ROUND(B5/C5,5),"###/###"),SEARCH("/",TEXT(ROUND(B5/C5,5),"###/###"))+1,3)`

- After entering this formula you will notice that the percentages of cell
**B5**with respect to**C5**are now present in cell**E5.**

- Now drag the
**Fill Handle**to cell**E7.** - Now, select the range of cells
**E5**to**E7.**And from the**Home**tab, click on the percentage icon in the**Numbers**group.

- Then the range of cells
**E5:E7**is now filled with the percentages of the numbers in the range of cells**D5:D7.**With respect to numbers in the range of cells**C5:C7.**

This is how we calculate ratio percentage in Excel by using combined formulas.

**Breakdown of the Formula**

**ROUND(B5/C5,5):**This function will return the quotient of the division of values in cells**B5**and**C5.**And round them up to 5 decimal digits.**TEXT(ROUND(B5/C5,5),”###/###”):**Then this function will format the values as fraction.**SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))**: This formula will return the location of a particular character inside of a given text. Starting from the left side.**LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1):**It will extract the portion of the text from the left side of the text up to a specified location.In this case it will extract up to the location of**“/**”.**MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3):**This formula will extract a specified section length from a particular position. How many characters will be removed is specified. In this case, the three characters after the “/” will be extracted.**LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1)****&”:”&****MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3):**This section the extracted part by**LEFT**function and**MID**function is now assembled with keeping “:” in the middle.

**LEFT(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))-1)****/****MID(TEXT(ROUND(B5/C5,5),”###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),”###/###”))+1,3):**In this function, the extracted part in**LEFT**function is now divided by the outcome of the**MID**function.

## Conclusion

To sum it up, the question “how to calculate Ratio Percentage in Excel’ is answered here in 4 different ways. Starting from using the **GCD** function. Then using the substitute with **TEXT**, **ROUND**,** LEFT, MID, SEARCH, ROUND** functions, etc, and by simple division. Among all of the methods used here, using the division and the **GCD** function is comparatively easier to use.

For this problem, a workbook is available for download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the **Exceldemy** community will be highly appreciable.

## Related Articles

**Debt Service Coverage Ratio Formula in Excel****Calculate Sortino Ratio in Excel (2 Methods)****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)****Graph Ratios in Excel (2 Quick Methods)**

You have an extra 0 in your formula in section 1:

=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C50)

it should read:

=B5/GCD(B5,C5)&”:”&C5/GCD(B5,C5)

Thanks a lot for providing the correction. Both the Image and the Formula has been updated.