How to Convert Percentage to Ratio in Excel (4 Easy Ways)

If you are looking for ways to convert a percentage to a ratio in Excel, then this is the right place for you. Sometimes, we need to change percentage formatting to other different desired number formats. We can change the percentage format to ratio in the Excel worksheet by following some simple steps. Here, you will find 4 easy ways to convert a percent to a ratio in Excel.

Download Practice Workbook


4 Easy Ways to Convert Percentage to Ratio in Excel

Here, we have the following dataset containing the mark sheet of some students. Scores are given in percentage value. You can convert these percentage values to Ratios by using the following methods.

Convert Percentage to Ratio


1. Using Combination of Excel SUBSTITUTE & TEXT Functions

In the first method, you can use the SUBSTITUTE and TEXT functions to convert percentage values to ratios. Here, we will use the SUBSTITUTE function to substitute the divider (/) with the ratio (:). The TEXT function will help to give the format.

Steps:

  • To begin with, select any cell to place your resultant value.
  • In the cell, D5 type the following formula.
=SUBSTITUTE(TEXT(C5,"?/??"),"/",":")

SUBSTITUTE & TEXT Functions

Formula Breakdown

  • TEXT(C5,”?/??”)—–> The TEXT function will format the selected value using divider.
    • Output: “65/97”
  • SUBSTITUTE(TEXT(C5,”?/??”),”/”,”:”)—-> becomes
    • SUBSTITUTE(“65/97″,”/”,”:”)—–> The SUBSTITUTE function will replace the divider with
      • Output: 65:97

  • Press ENTER to get the ratio.

Applying SUBSTITUTE & TEXT Functions

  • Now, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.

  • Finally, values are now converted into ratio from percentage.

Converted to Ratio from Percentage

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


2. Formatting Cell as Fraction to Convert Percentage to Ratio

We can also change the percentage number format to fraction in excel by which we can get the ratio.

Steps:

  • To begin with, select a cell in the resultant column.
  • Type the following formula in cell D5.
=C5

Format Cell as Fraction

  • Press ENTER to get the value in the resultant cell.

Format Cell as Fraction

  • After that, select cell D5.
  • Open the Home tab >> from Number Format >> select Fraction

  • Now, it’ll convert the percentage to ratio.

Convert Percentage to Ratio

  • To get a more precise value, you can use the Format Cell
  • Click on the Number Format icon from the Number group of the Home

  • After appearing in the Format Cells dialog box, click on Fraction from the ‘Category box’.
  • Select Up to three digits (312/943).
  • Finally, press OK.

  • Now, drag down the Fill Handle tool to AutoFill the formula with Format.

After that, values are now converted into ratios from percentages.

Converted to Ratio from Percentage

Read More: How to Calculate Ratio Between Two Numbers in Excel (5 Ways)


Similar Readings


3. Using Custom Format Feature to Convert Percentage to Ratio

Also, there are some custom number formats in Excel by which you can convert percentage to ratio.

Steps:

  • First, select a cell in the resultant column.
  • Type the following formula in cell D5.

=C5

Using Custom Format Feature

  • Click on the Number Format icon from the Number section of the Home ribbon.

  • After appearing in the Format Cells dialog box, click on Custom from the ‘Category box’.
  • Then choose #??/?? from the ‘Type box’.
  • At last, just press OK.

Using Custom Format Feature

  • Now, drag down the Fill Handle to AutoFill the rest of the cells.

convert percentage to ratio in excel

  • The values are now converted to ratios from percentages.

Converted to Ratio from Percentage


4. Dividing Percentage Value by 100 to Get Ratio

You can also change the percentage value into a ratio by manually dividing the percentage value by 100.

Steps:

  • In the beginning, select the cells you want to put the resultant ratio values. We have selected Cell D5 to Cell D11.
  • Click on Home ribbon >> from Number group >> select Fraction

convert percentage to ratio in excel

  • Now, you can select any cell to place your resultant value.
  • Here, in cell D5 type the following formula.
=67/100

Dividing Percentage Value by 100

  • Next, press ENTER to get the ratio from the percentage.

convert percentage to ratio in excel

  • Now, do the same steps for other cells in the column too.

Dividing Percentage Value by 100

Here, we can see that if the ratio is close to 1 the resultant ratio shows as 1:1. So, to have a more precise value we can customize the Fraction format as Up to three digits.

  • Select cell D5 to cell D11.
  • Right-click on it >> select Format Cells

convert percentage to ratio in excel

  • From Number format get some custom Fraction formats in the ‘Type’ box. Choose Up to three digits (312/943).
  • After that, press OK.

Convert Percentage to Ratio

  • Finally, The values are now converted to ratios.

Converted Percentage to Ratio


Conclusion

So, in this article, we have shown you the 4 easiest ways to convert percentage to ratio in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo