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.

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:

• • • 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:

• • • 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).

Download Practice Workbook

You can download the practice workbook from here:

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