How to Convert Ratio to Decimal in Excel (3 Handy Methods)

Get FREE Advanced Excel Exercises with Solutions!

If you want to convert a ratio to a decimal number, you are in the right place. This article is for you. Here we will take you through 3 easy and convenient methods on how to convert the ratio to decimal in Excel.


How to Convert Ratio to Decimal in Excel: 3 Methods

Here, we have a dataset of the Ratio of Male to Female Students based on the corresponding Class.

Convert Ratio to Decimal in Excel

Now, we can convert it into decimals in two easy but handy steps. At first, we have to convert the ratio into a fraction. Then, from fractions, we can get our desired value in decimal.


1. Converting Ratio to Decimal in Excel Using Format Cells Option 

For the first method, we will use the Custom Format Cells option to convert a ratio to decimal in Excel. To do that, please follow the below steps.

  • Firstly, select the D5 cell.
  • Then insert the following formula, and tap the ENTER key.

<span style="font-size: 14pt;"><strong>=SUBSTITUTE(C5,":","/")</strong></span>

Here, C5 is the starting cell of the Ratio of Male to Female Students.

Convert Ratio to Decimal in Excel

This will give us the value in fractional format. Then, we’ll get the fractional values of other rows by utilizing the Fill Handle tool to cell D14.

Convert Ratio to Decimal in Excel

  • Secondly, copy the cells in the D5:D14 range and paste them as Values in the cells in the E5:E14 range.

Convert Ratio to Decimal in Excel

  • Thirdly, select the cells in the E5:E14 range and then select the Number from the Ribbon in the Home tab.

  • Then, you’ll see the Format Cells dialog box. Now click on Custom > click on 0.00 > select OK.

Convert Ratio to Decimal in Excel

  • Next, you’ll get the value in decimal by double-clicking on cell E5 and pressing the ENTER key.

Convert Ratio to Decimal in Excel

And, we have to double-click and press ENTER on each cell of column E to get the same result.

Convert Ratio to Decimal in Excel

Finally, the output will be as follows.

Note: This method can be applied to a smaller dataset. Because to get decimals from fractions, here you have to double-click and press ENTER manually for each cell which is very tiresome. So, here we are giving another method below to be relieved from this trouble.

Read More: How to Calculate Ratio Percentage in Excel


2. Converting Ratio to Decimal Applying VALUE Function

Here, we’ll do exactly the same as method 1 until we get the value in fractional form (in column D). Then, click on cell E5. Then write down the formula as follows, and press the ENTER key. In this case, we’re applying the VALUE function which converts a text value into a number. So, the formula for the E5 cell will be as follows.

=VALUE("0 "&D5)

Here, D5 refers to Fraction.

Convert Ratio to Decimal in Excel

That’s how we can get the decimal value. Now, using the Fill Handle tool and dragging it down to cell E14, we can get other values as well.

Read More: How to Use Interest Coverage Ratio Formula in Excel


3. Getting the Ratio First and Then Convert It to Decimal

In the above 2 methods, we learned how to convert a given ratio to decimal. Here we’ll see how to get ratios from two columns of numbers and then convert the ratio into decimals.

Here we have a dataset of 10 individuals and their respective Ages.

From this, we’ll calculate the ratio of their ages and then convert it to decimals.

Steps:

  • At first, click on cell F5.
  • Then write down the formula as follows, and press the ENTER key.

=C5/GCD(C5,E5)&":"&E5/GCD(C5,E5)

Here, C5 & E5 refer to Age.

Convert Ratio to Decimal in Excel

  • Then, drag down the Fill Handle tool to cell F14 to get the remaining values in the ratio.

  • So, you have to follow the steps from method 2 to get the fractional values, and lastly in decimal format.

Convert Ratio to Decimal in Excel

Eventually, you’ll get the following output.


Download Practice Workbook


Conclusion

Here we’ve tried to show you 3 methods of converting a ratio to decimal in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section.


Related Articles


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo