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.

Calculate Ratio Percentage in Excel


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.

Using GCD Function to Calculate Ratio Percentage in Excel

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

Using GCD Function to Calculate Ratio Percentage in Excel

  • In cell E5, enter the formula:

=B5/C5

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

Using GCD Function to Calculate Ratio Percentage in Excel

  • 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.

Using GCD Function to Calculate Ratio Percentage in Excel

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.

Read More: How to Convert Percentage to Ratio 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 “:”).

Utilizing SUBSTITUTE and TEXT Functions to Calculate Ratio Percentage in Excel

  • 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.

. Utilizing SUBSTITUTE and TEXT Functions to Calculate Ratio Percentage in Excel

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

. Utilizing SUBSTITUTE and TEXT Functions to Calculate Ratio Percentage in Excel

  • 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.

Applying Simple Division Method to Calculate Ratio Percentage in Excel

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

Applying Simple Division Method to Calculate Ratio Percentage in Excel

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

This calculates the quotient as before.

Applying Simple Division Method to Calculate Ratio Percentage in Excel

  • 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.

Applying Simple Division Method to Calculate Ratio Percentage in Excel

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


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.

Using Combined Formula to Calculate Ratio Percentage in Excel

  • 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.

Using Combined Formula to Calculate Ratio Percentage in Excel

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

Using Combined Formula to Calculate Ratio Percentage in Excel

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

Using Combined Formula to Calculate Ratio Percentage in Excel

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


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
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

2 Comments
  1. 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)

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo