Frequently, you might need to calculate male female ratio, gender diversity ratio, or sex ratio for study or any other purpose in Excel. The data types given may be different in many cases. In this article, I have shown how to calculate the male-female ratio from different sets of data steps with easy and quick steps.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

## 3 Methods to Calculate the Male-Female Ratio in Excel

In this section, I’ll show you 3 easy methods to calculate the male-female ratio in Excel.

### 1. When the Number of Population Is Provided

Suppose you have a dataset where the number of **Male **and **Female **populations are provided. Now, you need to find out the *Male *and *Female *ratio. I am showing you two ways to do this.

#### 1.1. Applying the GCD Function to Calculate Male-Female Ratio

With the help of the **GCD function**, you can find out the **ratio** with very few quick steps.

__Step 01__**:**First, create a new column for**GCD**(Greatest Common Divisor) .__Step 02__**:**Click on cell**E5**and insert the following formula in the formula bar.

`=GCD(C5,D5)`

Here, **E5** is the starting cell for finding out the **GCD **value

__Step 03__**:**Now, drag the**Fill Handle**icon to copy the formula to other cells.

__Step 04__**:**Select the**F5**cell and in the formula bar, inserts the following formula:

`=C5/E5&":"&D5/E5`

Here, **C5 **and **D5 **refer to the number of *Males *and *Females *respectively. Besides, the **E5 **is the starting cell of the *GCD*.

__Step 05__**:**Similarly as in step 03, drag the**Fill Handle**for the rest of the cells in the column which will result in finding out your desired**ratios**.

#### 1.2. Using TEXT and SUBSTITUTE Functions to Calculate Male-Female Ratio

You can also use the **TEXT function** and the **SUBSTITUTE function** to find out the male and female ratio.

__Step 01__**:**Create a new column for*TEXT*.Next, click on cell__Step 02__:**E5**and insert the following formula in the formula bar.

`=TEXT(C5/D5,"#/####")`

__Step 03__**:**Now, drag the**Fill Handle**for the remaining cells of the column.

__Step 04__**:**At this point, select cell**F5**and type in the following formula in the formula bar.

`=SUBSTITUTE(E5,"/",":")`

Here, **F5** is the starting cell for finding out the male to female ratio. E5 cell denotes the first cell of the *Text *column.

__Step 05__**:**Finally, drag the**Fill Handle**for the rest of the cells and you get your required ratios.

**Read More: ****How to Convert Percentage to Ratio in Excel (4 Easy Ways)**

**Similar Readings**

**How to Calculate Sharpe Ratio in Excel (2 Common Cases)****Debt Service Coverage Ratio Formula in Excel****Calculate Sortino Ratio in Excel (2 Methods)****How to Calculate Odds Ratio in Excel****Convert Ratio to Decimal in Excel (3 Handy Methods)**

### 2. When the Male or Female Population % is Given

When you have the male or female population% given and you need to find out the ratio of male to female from that, you can follow the following steps to do it efficiently in excel.

Create a new column for__Step 01__:*GCD.*__Step 02__**:**Now, select cell**D5**and write down the following formula in the formula bar.

`=GCD(C5,(100-C5))`

__Step 03__**:**At this point, drag the**Fill Handle**for the remaining cell in the column.

__Step 04__**:**After that, select the**E5**cell and insert the following formula in the bar.

`=C5/D5&":"&(100-C5)/D5`

Here **E5** cell denotes the first value in GCD column and** C5 **and **D5** denotes the *Male *and *Female* number respectively.

__Step 05__**:**At last, drag the**Fill Handle**for the rest of the cells of column**E**.

**Read More: ****How to Calculate Ratio Percentage in Excel (4 Easy Methods)**

### 3. Using COUNTIFS Function to Calculate Male-Female Ratio

Suppose, you have a list of people consisting of genders male and female. In that list some of the male or female members are active and some are inactive. If you wish to find the male-female ratio from the list, you can follow the next quick easy steps with the help of the **COUNTIFS function**.

__Step 01__**:**Create new cells for ‘Total Active Male’, ‘ Total Active Female’ , ‘GCD’ and ‘Ratio’ at**G7**,**G8**,**G9,**and**G10**respectively. Click on**I7**and insert the following formula.

`=COUNTIFS(D5:D14,"Active",E5:E14,"Male")`

` `

Here, **D5** and **D14** is the first and last cell for Active column and **E5** and **E14** is the last cell for Gender Column

__Step 02__**:**Now, click on the cell**I8**and type in the following formula:

`=COUNTIFS(D5:D14,"Active",E5:E14,"Female")`

__Step 03__**:**Similarly, select cell**I9**and insert the following formula in the formula bar.

`=GCD(I7,I8)`

Here, the cell, **I9**, gives us the *GCD *between total active male and total active female members.

__Step 04__**:**Finally, click on**I10**and put in the formula given below and you will have your male and female ratio.

`=I7/I9&":"&I8/I9`

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

## Conclusion

So, I hope you found this article helpful. Moreover, please leave a comment below if you have any queries. Also, you can visit our website **ExcelDemy** for other articles.

## Related Articles

**How to Calculate Current Ratio in Excel (2 Suitable Examples)****Calculate Ratio of 3 Numbers in Excel (3 Quick Methods)****How to Calculate Compa Ratio in Excel (3 Suitable Examples)****Calculate Average Ratio in Excel (2 Simple Ways)****Use Interest Coverage Ratio Formula in Excel (2 Easy Methods)****How to Graph Ratios in Excel (2 Quick Methods)**