Frequently, you might need to calculate the 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

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

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, insert 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 H****a****ndle**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 Calculate Average Ratio in Excel

### 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 the GCD column and** C5 **and **D5** denote 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 of 3 Numbers in Excel

### 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 the 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, 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`

**Download Practice Workbook**

## Conclusion

So, I hope you found this article helpful. Moreover, please leave a comment below if you have any queries.