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

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.

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)²

The formula for calculating BMI in imperial unit is:

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

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:

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.
3. Drag down the Fill Handle to get BMI for other persons.
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’.
5. Press Enter and drag down the Fill Handle to get body status for individuals.

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

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

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.
3. To convert weight from pounds to kilograms, use the following formula in Cell F5: =CONVERT(D5,"lbm","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.

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.
3. Drag down the Fill Handle to get the BMI of the other individuals.

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.
3. Drag down the Fill Handle to BMI for the rest of the people.

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.

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.

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.
3. Then, check the Unblock box > Apply > OK.
4. Click on the Enable Content security warning.
5. Select the desired unit from the drop-down in which you want the BMI.
6. Insert the desired inputs and press Enter.

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.

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.
3. Now, select Cell I5 and type the following formula: =AVERAGE(E5:E14)
4. Hit Enter to get the average BMI of the dataset.
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.

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