How to Calculate BMI with Formula in Excel (3 Cases)

Get FREE Advanced Excel Exercises with Solutions!

BMI is an important term to assess an individual’s body fat based on height and weight. It can be used to screen people who are underweight, overweight, or obese.

In this Excel tutorial, you will learn to calculate BMI with different formulas. You will also get the BMI calculator and its user manual. You will also learn to determine the BMI z-score which can help you determine the relative position of BMI.

The screenshot below shows the BMI calculator, where you need to insert the height and weight of a person and press Enter to calculate the BMI.

BMI calculator


What Is BMI?

BMI (Body Mass Index) is a numerical value derived from the weight and height of a person. It is a measurement of body fat. Although it has some limitations, people use BMI to determine whether they are underweight, normal, overweight, or obese. It helps to see if your weight is in a healthy range.

BMI Formula in Metric and Imperial Units

The Body Mass Index (BMI) is calculated by dividing the weight of a person by the square of the height. The units of weight and height can vary in the world based on region.

The Imperial system exists in most of the British-ruled countries. The metric system is the modern unit system. Most countries are now replacing the imperial system with this metric unit system.

The formula for calculating BMI in metric unit is:

BMI=Weight in Kilograms/(Height in Meters)2

BMI Metric unit formula

The formula for calculating BMI in imperial unit is:

BMI=(Weight in Kilograms/(Height in Meters)2)*703

bmi imperial unit formula

In the imperial formula, the multiplication by 703 is a conversion factor to adjust the units.


BMI Ranges and Their Meanings

There are 4 general BMI ranges. We can show them in the table below:

Range Meaning
Less than 18.5 Underweight
Between 18.5 to 24.9 Normal weight
Between 25 to 29.9 Overweight
30 or greater Obesity

3 Cases of Calculating BMI with Formula in Excel

Calculation of BMI depends on the units of weight and height. Based on the units, you need to use different formulas to calculate BMI in Excel. Here are 3 different cases of calculating BMI in Excel:

1. Calculate BMI in Metric Unit

To calculate the BMI in metric units in Excel easily, you can use an Arithmetic Formula. In this case, the units of weight and height are given in meters and kilograms respectively.

Suppose, we have a dataset in the range B5:F14 containing the name, height and weight of different persons. We will calculate the BMI of the individuals in the range E5:E14.

To calculate BMI in metric units, follow the steps below:

  1. Select cell E5 and type the following formula: =D5/C5^2
    Here, D5 is the weight and C5 is a person’s height. Both quantities are given in metric units.
  2. Press Enter to get the BMI.arithmetic formula for BMI
  3. Drag down the Fill Handle to get BMI for other persons.drag down arithmetic formula for BMI
  4. To calculate Body Status (if a person is normal, overweight, or obese), you can use the following formula: =IF(E5<=18.5,"Underweight",IF(E5<=24.9,"Normal",IF(E5<=29.9,"Overweight","Obesity")))
    The IF function returns a body status based on the cell value of E5. If E5<=18.5, it returns ‘Underweight’. If E5 is between 18.5 and 24.9, it returns ‘Normal’. If E5 is between 24.9 and 29.9, it returns ‘Overweight’. If it is greater than 29.9, it returns ‘Obesity’.arithmetic formula for BMI status
  5. Press Enter and drag down the Fill Handle to get body status for individuals.result after using arithmetic formula for BMI

2. Calculate BMI in Imperial Unit

In the imperial unit, the weight is provided in pounds and the height is provided in feet or inches. To adjust the units, we need to use a slightly different formula this time. Follow the sections below to calculate BMI in the imperial units:

2.1 BMI from Inches and Pounds

If the height is given in inches and weight in pounds, then you need to adjust the basic arithmetic formula with a factor. To calculate BMI from inches and pounds:

  1. Select Cell E5 and type the following formula: (=D5/C5^2)*703
    Here, D5 is the weight and C5 is a person’s height. The constant 703 is used to adjust the units of measurement.
  2. Press Enter and drag down the Fill Handle to get results in the other cells.
  3. calculating BMI in imperial units Use the following formula in Cell F5 to get the body status of an individual: =IF(E5<=18.5,"Underweight",IF(E5<=24.9,"Normal",IF(E5<=29.9,"Overweight","Obesity")))calculating BMI status for individuals

As a result, you will get the BMI and Body Status of different people in Excel.

result after using arithmetic formula for BMI

Alternative Method: Use of CONVERT Function

You can use the CONVERT function to convert inches to meters and pounds to kg. After that, you can use the basic formula to get the BMI in Excel.

Let’s follow the steps below to see how we can use the CONVERT function to calculate BMI in Excel:

  1. Create two helper columns: one to store the heights in the meter unit and another one to store the weights in the kilogram unit. Here, columns E and F are our helper columns.
  2. Now, select cell E5 and type the following formula: =CONVERT(C5,"in","m")
    Here, this formula converts the value of cell C5 from inches to meter unit.use of CONVERT function to change inches to meter
  3. To convert weight from pounds to kilograms, use the following formula in Cell F5: =CONVERT(D5,"lbm","kg")use of CONVERT function to change pound to kg
  4. After the conversion, select Cell G5 and type the following formula: =F5/E5^2
  5. Press Enter and drag down the Fill Handle to BMI for all individuals.bmi calculation in excel

2.2 BMI from Feet, Inches and Pounds

Sometimes, the height is given in feet and inches and the weight is given in pounds. In that case, you can directly use the CONVERT function and calculate BMI in Excel.

Suppose, we have a dataset that contains Height (in feet), Height (in inches), and Weight (in pounds) in the range C5: E14. To calculate BMI from feet, inches, and pounds, follow the steps below:

  1. Select Cell F5 and type the following formula: =CONVERT(E5,"lbm","kg")/(CONVERT(C5,"ft","m")+CONVERT(D5,"in","m"))^2
    The CONVERT(E5,”lbm”,”kg”) part converts pounds to kilograms.
    The (CONVERT(C5,”ft”,”m”)+CONVERT(D5,”in”,”m”)) part converts feet and inches to meters and adds them up.
  2. Press Enter to see the BMI in Cell F5.bmi calculation from feet, inches and pounds
  3. Drag down the Fill Handle to get the BMI of the other individuals.result after calculating BMI from feet, inches and pounds

3. Calculate BMI in Cm and Kg

Sometimes the units of height and weight are given in cm (centimeter) and kg (kilograms) respectively. In that case, you can use a different formula to get the BMI quickly.

You can use the following steps to calculate BMI in Excel if height and weight are given in centimeters and kilograms respectively:

  1. Select cell E5 and type the formula: =(D5/C5/C5)*10000
  2. Press Enter to get the corresponding BMI in Cell E5.calcualating BMI from cm and kg
  3. Drag down the Fill Handle to BMI for the rest of the people.result after calcualating BMI from cm and kg

You can also calculate the BMI percentile in Excel with the PERCENTRANK.INC function. The BMI percentile is used to compare an individual’s BMI to others in a dataset. It denotes a relative value comparing the items in the whole dataset.
To calculate the BMI percentile, you can use the following formula: =PERCENTRANK.INC($E$5:$E$14,E5)

The PERCENTRANK.INC function returns the rank of a value in a dataset as a percentage. Here, the E5:E14 array should be inside an absolute reference.calculating BMI percentile


How to Use a BMI Calculator in Excel

In this section, we will show how to use a BMI calculator in Excel. You can see a screenshot of the calculator below.

BMI calculator use

Follow the steps below to use the BMI calculator efficiently:

  1. Download the provided Excel file.
  2. After that, right-click on the file and select Properties from the context menu.opening properties dialog box
  3. Then, check the Unblock box > Apply > OK.Unblocking macro file
  4. Click on the Enable Content security warning.enable content security warning
  5. Select the desired unit from the drop-down in which you want the BMI.selecting unit system
  6. Insert the desired inputs and press Enter.BMI calculation

You can follow the above-mentioned steps to use the BMI calculator easily.

Download BMI Calculator

The calculator is given below:

Note: You can also use VBA to create a BMI calculator in Excel.


How to Calculate BMI Z Score in Excel

The Z Score denotes a value’s position or rank in the dataset relative to the mean. Calculating BMI and Z Score will determine the relative position of that BMI in a dataset.

The formula for calculating Z-score is: (x-µ)/σ

  • x = The raw data value
  • µ = Mean/Average of the dataset
  • σ = The standard deviation of the given dataset

We can follow some basic steps to calculate the BMI z-score in Excel. For example, we have a dataset of name, height, weight, and BMI in the range B5:E14. We will calculate the z-score in the range F5:F14.

dataset for calculating BMI z score

Follow the steps below to calculate BMI Z-score in Excel:

  1. Select Cell I4 and type the formula: =STDEV.P(E5:E14)
  2. Press Enter to get the standard deviation of the calculated BMIs.calculating standard deviation
  3. Now, select Cell I5 and type the following formula: =AVERAGE(E5:E14)
  4. Hit Enter to get the average BMI of the dataset.calculating mean
  5. After that, type the formula in Cell F5 to get the Z-score for the first BMI: =(E5-$I$5)/$I$4
    Here, the mean value and standard deviation have the absolute reference.calculated z score for each BMI

Download Practice Workbook


Conclusion

In this article, we have shown 3 cases of calculating BMI with formula in Excel. We have also added a BMI calculator that you can use to determine the BMI quickly. You can also calculate the z-score of BMI after reading the article. We get different formulas for calculating BMI because of different units of height and weight. You can get a clear concept about the formulas in this article. If you have any queries or suggestions, please let us know in the comment section.


<< Go Back to Formula List | 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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo