# How to Calculate Ratio Percentage in Excel (4 Methods)

## Understanding Ratios:

• Ratios express the relative size between two or more numbers.
• The ratio is obtained by dividing one number (the antecedent) by another (the consequent).
• The general format of a ratio is “a:b,” where “a” and “b” can be integers, decimals, or fractions.

## Dataset Example:

• Let’s use a dataset with two columns: Number 1 (in column B) and Number 2 (in column C).
• We want to calculate the ratio of Number 1 to Number 2 and express it as a percentage.

### Method 1 – Using the GCD Function:

• The GCD function finds the greatest common divisor between two numbers.
• We’ll use this value to calculate the ratio and percentage.

Step-by-Step Calculation:

• In cell D5, enter the following formula:
`=B5/GCD(B5,C5)&":"&C5/GCD(B5,C5)`

This formula calculates the ratio between cell B5 and C5 and displays it as a:b in cell D5.

• Drag the Fill Handle down to cell D7 to fill the entire range (D5:D7) with ratios.

• In cell E5, enter the formula:

`=B5/C5`

This calculates the quotient of Number 1 divided by Number 2.

• Drag the Fill Handle down to cell E7 to fill the entire range (E5:E7) with quotient values.

• Select the range E5:E7, go to the Home tab, and click the % (Percentage) sign in the Number group. This converts the quotient values to percentages.

Result:

• The range E5:E7 now shows the percentage value of Number 1 relative to Number 2.

Breakdown of the Formula

The GCD(B5,C5) function finds the greatest common divisor, and the expressions B5/GCD(B5,C5) and C5/GCD(B5,C5) give us the ratio values. The final formula B5/GCD(B5,C5)&“:”&C5/GCD(B5,C5) combines these values with the ratio sign “:”. You can use this method to calculate ratio percentages in Excel.

### Method 2 – Combining SUBSTITUTE and TEXT Functions:

Steps

• In cell D5, enter the following formula:

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

This formula calculates the ratio between cell B5 and C5 and formats it as a:b (replacing “/” with “:”).

• Drag the Fill Handle down to cell D7 to fill the entire range (D5:D7) with ratios.

• In cell E5, enter the formula:
`=B5/C5`

This calculates the quotient of Number 1 divided by Number 2.

• Drag the Fill Handle down to cell E7 to fill the entire range (E5:E7) with quotient values.

• Select the range E5:E7, go to the Home tab, and click the “%” (Percentage) sign in the Number group.

• This converts the quotient values to percentages.

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

### Method 3 – Applying Simple Division:

Steps

• In cell D5, enter the formula:
`=(B5/C5)&":"&"1"`

This expresses the ratio of Number 1 to Number 2 with respect to 1.

• Drag the Fill Handle down to cell D7 to fill the entire range (D5:D7) with ratios.

• In cell E5, enter the formula:
`=B5/C5`

This calculates the quotient as before.

• Drag the Fill Handle down to cell E7 to fill the entire range (E5:E7) with quotient values.
• Select the range D5:D7, and click the percentage icon from the Home tab.

• Now the range E5:E7 shows the percentages of Number 1 relative to Number 2.

### Method 4 – Using Combined Formula:

Steps

• In cell D5, 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)`

This formula calculates the ratio and formats it as a:b.

• Drag the Fill Handle down to cell D7 to fill the entire range (D5:D7) with ratios.

• In cell E5, enter the 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)`

This calculates the percentages.

• Drag the Fill Handle down to cell E7.
• Select the range E5:E7 and click the percentage icon.

• Now the range E5:E7 shows the percentages of Number 1 relative to Number 2.

Breakdown of the Formula

• ROUND(B5/C5,5):
• This function calculates the quotient of the division of values in cells B5 and C5.
• It rounds the result to 5 decimal places.
• TEXT(ROUND(B5/C5,5),“###/###”):
• The TEXT function formats the rounded quotient as a fraction.
• The format “###/###” ensures that the fraction is displayed correctly.
• SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”)):
• This formula finds the location of the “/” character within the formatted fraction.
• It starts searching from the left side of the text.
• LEFT(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))-1):
• The LEFT function extracts the portion of the text from the left side up to the specified location (just before the “/”).
• This gives us the numerator of the ratio.
• MID(TEXT(ROUND(B5/C5,5),“###/###”),SEARCH(“/”,TEXT(ROUND(B5/C5,5),“###/###”))+1,3):
• The MID function extracts a specified section of text from a particular position.
• In this case, it extracts the three characters after the “/” (the denominator of the ratio).
• 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):
• Finally, this function divides the extracted numerator by the extracted denominator.
• The result is the ratio expressed as “a:b.”

Read More: How to Graph Ratios in Excel

## Related Articles

<< Go Back to Ratio in Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio