With ratios, 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 it here with adequate explanations.

## What Is Ratio?

Two or maybe more numbers can be compared by using a **ratio** to express their relative values. The ratio actually achieves this by division. here the number that is going to be divided is called the 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.

## How to Calculate Ratio Percentage in Excel: 4 Suitable Ways

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 numbers in the range of cell**B5:B7**and the 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 the ratio sign**“:”.**

**Read More: **How to Convert Percentage to Ratio in Excel

### 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 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.**From the**Home**tab, click on the percentage sign in the**Number**group.

- After clicking on the percentage sign, 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 a fraction.**SUBSTITUTE(TEXT(B5/C5,”######/######”),”/”,”:”):**This formula will substitute the “**/**” with the “**:**” in the fraction.

**Read More: **How to Convert Ratio to Decimal in Excel

### 3. Applying Simple Division Method

We can simply divide numbers in 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 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, 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.

**Read More: **How to Do Ratio Analysis in Excel Sheet Format

### 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 numbers in the range of cell**B5:B7**and the 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.**From the**Home**tab, click on the percentage icon in the**Numbers**group.

- Then the range of cells
**E5:E7**is 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 percentages 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.

**Read More: **How to Graph Ratios in Excel

**Download Practice Workbook**

## 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. Download the workbook to practice these methods. Please feel free to ask any questions or feedback through the comment section.

## Related Articles

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

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.