For a metric BMI calculation in Excel, put weight in kilograms in B2 and height in meters in C2, then enter =B2/(C2^2). For pounds and inches, with weight in A2 and height in B2, use =703*A2/(B2^2).
How the BMI formula works
Body mass index (BMI) is calculated by dividing a person’s weight by the square of their height. In metric units, that means kilograms divided by meters squared. The result is a number, not a percentage, and its unit is kg/m². Excel uses the caret character to raise a value to a power, so C2^2 means the height in C2 multiplied by itself.
The imperial version needs a conversion factor because pounds and inches are not metric units. The commonly used factor is 703. Thus, BMI is 703 multiplied by weight in pounds, divided by height in inches squared. The factor is a rounded conversion constant; it makes the imperial calculation comparable to the metric formula.
These formulas calculate BMI from the values in the referenced cells. They do not determine whether a person is healthy, diagnose a condition, or account for the context behind the number.
Set up the spreadsheet
A simple table makes it easier to enter measurements consistently and copy the calculation down for multiple rows. In a new worksheet, use headings such as these:
- A1: Name or ID
- B1: Weight (kg)
- C1: Height (m)
- D1: BMI
Enter a person’s measurements on row 2. With weight in kilograms in B2 and height in meters in C2, enter =B2/(C2^2) in D2. Press Enter to calculate the result. To apply the same calculation to more records, select D2 and drag the fill handle—the small square at the lower-right corner of the cell—down the column. Excel adjusts the row references as it fills.
For clarity, label units in the column headings rather than relying on memory. A value of 1.70 is plausible as a height in meters, while 170 is plausible as a height in centimeters; using the wrong scale produces a drastically incorrect result. Keep the input and result columns separate so the original measurements remain visible.
Choose the right units
Use the metric formula when weight is in kilograms and height is in meters. If height is recorded in centimeters, convert it to meters within the formula. With weight in kilograms in B2 and height in centimeters in C2, enter =B2/((C2/100)^2). The parentheses ensure Excel converts the height before squaring it.
For imperial measurements, record weight in pounds and height in inches. With pounds in A2 and inches in B2, enter =703*A2/(B2^2). If you have height in feet and inches, convert the full height to inches first. For example, 5 feet 6 inches is 66 inches; do not enter 5.6, because decimal feet are not the same as feet-and-inches notation.
Avoid mixing units in a single calculation. The metric formula will not work correctly if the weight is in pounds or the height is in centimeters without conversion. If a worksheet will be used by several people, state the required units in the headings and add a note above the table.
Worked examples and checking the result
Suppose a measurement is 68 kg and 1.65 m. With weight in B2 and height in C2, the formula =B2/(C2^2) returns approximately 24.98. If the same height is entered as 165 cm, the centimeter formula =B2/((C2/100)^2) also returns approximately 24.98. Those two results should agree, apart from any difference caused by rounding the measurements entered.
For an imperial example, a weight of 150 lb and a height of 66 in gives approximately 24.21. With pounds in A2 and inches in B2, =703*A2/(B2^2) calculates 703 × 150 ÷ 66², or about 24.21. This is a useful check that the pounds-and-inches layout and the conversion factor have been applied correctly.
Use Excel’s number format to control how the result appears. Select the BMI cells, then choose Home > Number and adjust the displayed decimal places. Showing one or two decimal places is often convenient, but formatting changes only what is displayed: Excel retains the underlying calculation precision. Do not round the BMI inside the formula unless a particular reporting rule requires it.
Make the worksheet easier to use
For a single calculation, a direct formula is usually easiest to understand. For a list, turn the data range into an Excel table by selecting it and choosing Insert > Table. When you add a new row, Excel can extend calculated columns automatically. Use descriptive headings such as Weight (kg), Height (cm), and BMI rather than vague labels such as Weight and Result.
You can also prevent some incorrect entries with data validation. Select the input cells, then choose Data > Data Validation and set an appropriate rule, such as allowing a decimal number greater than zero. This does not confirm that a measurement is realistic or that the units are correct, but it can catch blanks or negative values. Set sensible limits only when they fit the intended dataset; overly narrow limits can reject valid records.
If blank input cells should produce a blank result instead of a visible error or zero, use an error-handling formula. With weight in B2 and height in C2, enter =IFERROR(B2/(C2^2),""). This returns an empty display when the calculation produces an error, including division by zero. It does not validate the data: text, mistaken units, or an implausible but technically valid number still need attention. For a basic personal worksheet, the direct formula may be preferable because Excel errors can make missing or invalid inputs easier to notice.
What a BMI result does and does not mean
For adults, commonly used BMI categories are underweight below 18.5, healthy weight from 18.5 to below 25, overweight from 25 to below 30, and obesity at 30 or above. These are screening categories, not individualized health conclusions. A BMI near a boundary can also appear on either side depending on rounding, so category decisions should use the unrounded value when appropriate.
BMI is a population-level measure based on height and weight. It does not directly measure body fat or show where fat is distributed. It can be less informative for some individuals, including people with substantial muscle mass, and it does not account for every factor that affects health. A spreadsheet can calculate the index accurately while still giving an incomplete picture of an individual’s health.
Adult cutoffs should not be applied to children and teenagers in the same way. Their BMI is generally interpreted using age- and sex-specific percentiles, so a simple adult category formula is not an appropriate substitute. For personal health decisions or questions about a particular result, discuss the result and its context with a qualified healthcare professional.
Common spreadsheet mistakes
- Using the wrong height scale: Entering 165 as though it were meters instead of centimeters makes the calculation meaningless. Use the centimeter formula or convert to meters.
- Squaring only part of the height: Keep the height in parentheses before applying the exponent. The formula should square the complete height value.
- Entering feet and inches as a decimal: Convert feet and remaining inches to total inches first. For instance, 5 feet 6 inches is 66 inches, not 5.6 inches.
- Leaving out the imperial factor: The pounds-and-inches calculation needs the factor 703. A metric formula cannot be used unchanged with imperial values.
- Rounding measurements too early: Use the most precise trustworthy measurements available, then format the displayed result. A rounded height can noticeably affect BMI because height is squared.
- Copying a formula with shifted references: If you copy a formula across columns rather than down rows, Excel may point to the wrong inputs. Check the cell references in a few copied rows.
If a result looks unexpectedly large or small, first inspect the units and the actual cell references. Then check that height is positive and that the cells contain numbers rather than text. A formula can be syntactically correct and still calculate the wrong answer when the inputs or their units are wrong.
Frequently Asked Questions
What is the simplest BMI formula for Excel?
With weight in kilograms in B2 and height in meters in C2, use =B2/(C2^2). Both measurements must use those units for this version of the formula.
How do I calculate BMI in Excel with height in centimeters?
With weight in kilograms in B2 and height in centimeters in C2, use =B2/((C2/100)^2). Dividing centimeters by 100 converts the height to meters before squaring it.
How do I calculate BMI using pounds and inches?
With weight in pounds in A2 and height in inches in B2, use =703*A2/(B2^2). Convert feet and inches to total inches before entering the height.
Can I show BMI to one decimal place?
Yes. Select the result cells and use Home > Number to set the displayed decimal places. This changes the presentation, not the stored calculation value.
Can I use an adult BMI category formula for a child?
No. Adult BMI cutoffs are not interpreted the same way for children and teenagers; age- and sex-specific percentiles are generally used. Ask a healthcare professional how a child’s result should be assessed.
Related
- Protect Sheet vs. Protect Workbook in Excel: Key Differences
- Protect Excel Sheets Except for a Few Cells
- Named Range in Excel: How to Create and Use One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDIRECT Function: Reference Another SheetYes. In Excel, INDIRECT can turn a text string containing a worksheet reference into a usable reference. For…
- Lock a Number in Excel: Keep It Fixed in FormulasTo keep a number fixed when you copy a formula, make its cell reference absolute by adding dollar…
- Excel Formula Bar Disappeared? How to Show It AgainIf the Excel formula bar disappeared, turn it back on from View > Show > Formula Bar in…
- Excel COUNTIFS With Multiple Criteria in One RangeYes. Excel’s COUNTIFS can apply more than one condition to the same range by repeating that range for…
- Excel Data Validation Custom Formula: Rules and ExamplesIn Excel for Microsoft 365 on Windows, select the cells to control, choose Data > Data Validation, set…
- Excel LET Function: Syntax, Examples, and Common ErrorsExcel’s LET function lets you give names to values or calculations inside a formula, then use those names…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.