The Excel formula for BMI (Body Mass Index) is =weight_kg/(height_m^2) when your data is in kilograms and meters. If your weight is in pounds and height in inches, use =weight_lb*703/(height_in^2). For example, with weight 70 kg in cell A2 and height 1.75 m in cell B2, the formula =A2/(B2^2) returns 22.86, which falls in the normal range.
What BMI Measures and Its Limitations
BMI is a screening tool that estimates body fat based on height and weight. It was developed in the 19th century by Adolphe Quetelet and later popularized by Ancel Keys in 1972. The formula is the same for adults of all ages and sexes, but it does not directly measure body fat. It cannot distinguish muscle from fat, so a muscular athlete may show a high BMI despite low body fat. Similarly, older adults often have less muscle mass, so a normal BMI might still mask excess fat. BMI also does not account for fat distribution—abdominal fat is a stronger risk factor for heart disease than fat elsewhere. For clinical use, doctors combine BMI with waist circumference, blood pressure, and other metabolic markers.
Excel Formula for Metric Units (kg and meters)
Assume your spreadsheet has weight in kilograms in column A and height in meters in column B. In cell C2, enter:
=A2/(B2^2)
This squares the height (B2^2) and divides weight by that value. For a person weighing 65 kg with a height of 1.70 m, the result is 65/(1.70^2) = 65/2.89 = 22.49. To round to one decimal place, wrap it in =ROUND(A2/(B2^2),1).
Excel Formula for Imperial Units (pounds and inches)
If your data is in pounds (column A) and inches (column B), use the conversion factor 703. In cell C2, enter:
=A2*703/(B2^2)
For a 150 lb person who is 65 inches tall, the calculation is 150*703/(65^2) = 105450/4225 = 24.96, which is near the top of the normal range. The 703 factor comes from converting pounds to kilograms (1 lb = 0.453592 kg) and inches to meters (1 in = 0.0254 m) and squaring the height conversion (0.0254^2 = 0.00064516). Dividing 0.453592 by 0.00064516 gives approximately 703.
How to Build a BMI Calculator in Excel with Categories
To make a reusable calculator, set up a small table with labeled cells. For example, put “Weight (kg)” in A1 and the value in B1, “Height (m)” in A2 and the value in B2. Then in B3, enter:
=B1/(B2^2)
To automatically show the BMI category, use a nested IF formula. In B4, enter:
=IF(B3<18.5,"Underweight",IF(B3<25,"Normal",IF(B3<30,"Overweight","Obese")))
This uses the standard WHO categories: below 18.5 underweight, 18.5–24.9 normal, 25–29.9 overweight, 30 and above obese. For a height of 1.75 m and weight of 80 kg, BMI is 26.12, so the formula returns “Overweight”.
Using the ROUND Function and Handling Errors
Raw BMI results often have many decimal places. To keep your spreadsheet clean, use =ROUND(B1/(B2^2),1) to show one decimal place. If your cells might be empty or contain text, you can prevent errors with =IF(OR(B1="",B2="",B2=0),"",B1/(B2^2)). This checks for empty cells and a zero height. If you prefer to use the POWER function instead of the caret, =B1/POWER(B2,2) works identically. Excel’s caret operator (^) is the simplest, but POWER is clearer to some users.
Common Mistakes When Calculating BMI in Excel
- Using the wrong units: Mixing kilograms with meters but forgetting the 703 factor when using pounds and inches is the most frequent error.
- Not squaring the height: Typing
=A2/B2*2instead of=A2/(B2^2)multiplies by 2 instead of squaring. - Forgetting parentheses: Without parentheses,
=A2/B2^2is correct because exponentiation occurs before division, but=A2*703/B2^2is also correct. However,=A2*703/B2*2would be wrong. - Copying formulas without absolute references: If you drag a formula down but your height is in a fixed cell, use
$B$2to lock it. - Using commas as decimal separators: In some regional Excel settings, you need semicolons between arguments. The formulas above use commas, which work in US and UK English Excel. If you see a #NAME? error, check your region’s list separator.
BMI Categories and What They Mean
The World Health Organization defines these adult BMI categories:
- Underweight: less than 18.5
- Normal weight: 18.5 to 24.9
- Overweight: 25 to 29.9
- Obese: 30 or higher
For children and teens, BMI is interpreted differently—it is plotted on growth charts that account for age and sex. Excel formulas for pediatric BMI use the same weight/height calculation, but the classification requires percentile charts. Also note that BMI is not a direct measure of health. A 2016 study in the International Journal of Obesity found that nearly 75 million American adults had a “normal” BMI but were metabolically unhealthy. Conversely, some people with a high BMI have normal blood pressure and cholesterol. Always interpret BMI alongside other health indicators.
Frequently Asked Questions
How do I calculate BMI in Excel with feet and inches?
If you have height in feet and inches, first convert to total inches. For example, if feet are in A2 and inches in B2, total inches are =A2*12+B2. Then use that total in the imperial formula: =weight_lb*703/(total_inches^2). If weight is in pounds in C2, the full formula would be =C2*703/((A2*12+B2)^2).
Can I use Excel for BMI in children?
Yes, the same formula works, but the result must be compared to age- and sex-specific percentiles from CDC or WHO growth charts. Excel does not have a built-in percentile function for BMI, so you would need to look up the percentile in a reference table or use a web-based calculator.
Why does my BMI formula return #DIV/0!?
This error appears when the denominator is zero. It happens if the height cell is empty, contains 0, or has text. Check that the height is numeric and greater than zero. You can also use the IFERROR function: =IFERROR(A2/(B2^2),"") to display a blank cell instead of an error.
Is there a built-in BMI function in Excel?
No, Excel does not have a dedicated BMI function. You must enter the formula manually or use a custom VBA function. The formulas shown above are the standard way to calculate BMI in a spreadsheet.
What is the difference between BMI and body fat percentage?
BMI is a ratio of weight to height, while body fat percentage measures the proportion of fat mass to total body weight. BMI does not differentiate between muscle, bone, and fat. Body fat percentage can be measured with calipers, bioelectrical impedance, DEXA scans, or other methods. Two people with the same BMI can have very different body fat percentages.
How do I format the BMI result to show only one decimal?
Use the ROUND function: =ROUND(A2/(B2^2),1) or change the number format of the cell to “0.0”. To do that, right-click the cell, choose Format Cells, select Number, and set Decimal places to 1.