# How to Calculate BMI in Excel (2 Methods)

## BMI Calculation

BMI stands for Body Mass Index. It’s a simple method that uses height and weight to determine a person’s body status. By calculating BMI, we can assess whether an individual is normal, overweight, underweight, or obese. However, it’s important to note that BMI is not a diagnostic tool. Let’s explore the generic formula used for BMI calculation.

## Dataset Overview

We’ll use the below Sample Data Set containing a list of people’s names, heights in meters, and weights in kilograms.

### Method 1 – Using the IF Function to Calculate BMI in Excel

In this method, we’ll calculate the Body Mass Index (BMI) using the standard formula and then determine the body status using the IF function. Follow these steps:

• Open your Excel file with the relevant data (names, heights, and weights).
• Calculate BMI:
• Select cell E5.
• Enter the following formula:
`=D5/C5^2`
• Here, D5 represents the weight in kilograms, and C5 represents the height in meters.

• Press Enter. Cell E5 will now display the BMI for the first person.

• Fill down:
• Use the Fill Handle tool (located at the bottom-right corner of cell E5) to drag down from cell E5 to E14. This will populate the BMI results for the other individuals.

• Determine Body Status:
• To categorize individuals as normal, overweight, or obese based on their BMI, use the IF function.
• Select cell F5.
• Enter the following formula:
`=IF(E5<=18.5,"Underweight",IF(E5<=24.9,"Normal",IF(E5<=29.9,"Overweight","Obesity")))`
• Adjust the BMI thresholds as needed.

• Press Enter. Cell F5 will now display the body status for the first person.

• Fill down:
• Use the Fill Handle tool to drag down from cell F5 to F14. This will provide body status results for all individuals.

### Method 2 – Run a VBA Code to Calculate BMI in Excel

The VBA code implementation provides an alternative approach to accomplish this task. By applying the VBA code, we can quickly determine both the Body Mass Index (BMI) and the corresponding body status. Using VBA code in Excel for BMI calculation is highly effective compared to other methods.

Steps:

• Access the Developer Tab:
• First, go to the Developer tab in Excel.
• Click on the Visual Basic option.

• Open the Visual Basic Application Window:
• The Visual Basic Application window will open, allowing you to work with VBA code.
• Insert a New Module:
• Click on the Insert tab within the Visual Basic Application.
• Select Module to create a new module.

• Paste the VBA Code:
• Paste the following VBA code into the newly created module:
``````Sub BMI_Status()
Dim Weight, Height, bmi, x As Range
Weight = "D5:D14"
Height = "C5:C14"
Dim Rng As Range
Set Rng = Range("E5:E14")
For i = 1 To Rng.Rows.Count
Rng.Cells(i, 1) = Rng.Cells(i, 0) / ((Rng.Cells(i, -1)) ^ 2)
If Rng.Cells(i, 1) <= 15 Then
Rng.Cells(i, 2) = "Under Weight"
ElseIf Rng.Cells(i, 1) > 15 And Rng.Cells(i, 1) <= 25 Then
Rng.Cells(i, 2) = "Optimum weight"
Else
Rng.Cells(i, 2) = "Over weight"
End If
Next i
End Sub``````

• Run the Program:
• Finally, press F5 to execute the program.
• The BMI values and corresponding body status will be calculated for the specified range (cells E5 to E14).

Notes:

1. Input Variables and Syntax:
• When writing the Excel VBA code to determine BMI and body status, pay close attention to your input variables and their syntax. Ensure that you use the correct cell references for weight and height.
2. Body Status Categories:
• While using the IF function, decide how many body status categories you want to define. Consider whether you’ll classify individuals as underweight, normal weight, or overweight based on their BMI.

<< Go Back to BMI | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bishawajit Chakraborty

Bishawajit Chakraborty, a Rajshahi University of Engineering & Technology graduate with a B.Sc. in Mechanical Engineering, has been associated with ExcelDemy since 2022. Presently, he is a content developer, specializing in Excel Power Query, Data Analysis and VBA. It is worth mentioning that he has authored more than 90 articles on VBA content development. His profound interest lies in the fields of data analytics and data science. He possesses expertise in VBA, Power BI, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF