How to Calculate BMI in Excel: Formula, Template & Step-by-Step Guide

Short answer: in a spreadsheet with weight in A2 and height in B2, the metric BMI formula is =A2/(B2/100)^2 (kg and cm) and the imperial one is =(A2/(B2^2))*703 (lbs and inches). Everything else in this guide is about doing it cleanly for a whole list of people at once.

BMI (Body Mass Index) is a simple ratio, which makes it one of the easiest health metrics to automate in a spreadsheet. Whether you are tracking BMI for a class roster, a clinic's patient list, an HR wellness program, or just your own household, Excel handles it in one formula column. No macros, no add-ins.

This guide gives you the exact formula, a category-labeling trick, five worked examples, and a copy-paste layout for both metric (kg/cm) and imperial (lbs/inches) units. The formulas are identical in Google Sheets, so nothing here is locked to Microsoft Excel.

Table of Contents

• The BMI Formula
• Metric BMI Formula in Excel (kg and cm)
• Imperial BMI Formula in Excel (lbs and inches)
• Auto-Classifying BMI With a Nested IF
• Building the Spreadsheet Step by Step
• BMI Categories Reference (WHO)
• A Copy-Paste BMI Template
• Summarizing a Whole Roster
• Worked Examples
• Common Mistakes
• Frequently Asked Questions
• References

The BMI Formula

BMI is calculated the same way everywhere in the world. In metric units it is weight in kilograms divided by height in meters squared:

Metric: BMI = weight (kg) ÷ height (m)²

In imperial units, the equivalent formula multiplies by a 703 conversion factor so the result lands on the same scale:

Imperial: BMI = (weight (lb) ÷ height (in)²) × 703

Excel does not care which version you use. You just need weight and height in consistent cells, and one formula that matches those units. The number you get out is the same either way, and it maps to the same categories, so pick whichever units your data is already in.

Metric BMI Formula in Excel (kg and cm)

Most weight-and-height data is recorded in kilograms and centimeters, not meters, so the practical formula does the centimeter-to-meter conversion inside itself. Assume:

• A2 = weight in kilograms
• B2 = height in centimeters

Because the standard formula needs height in meters, divide the centimeter value by 100 inside the formula:

=A2/(B2/100)^2

That single formula returns BMI directly. To round it to one decimal place, which is how BMI is normally reported, wrap it in ROUND:

=ROUND(A2/(B2/100)^2, 1)

If your height is already stored in meters (like 1.70 rather than 170), drop the /100 and use =ROUND(A2/B2^2, 1) instead. Matching the formula to your actual units is the single most common place people slip up, covered in the mistakes section below.

Imperial BMI Formula in Excel (lbs and inches)

For pounds and inches, use the 703 version. Assume:

• A2 = weight in pounds
• B2 = height in inches

=(A2/(B2^2))*703

Rounded to one decimal:

=ROUND((A2/(B2^2))*703, 1)

If your height is recorded in feet and inches (say 5 ft 8 in), convert to total inches first (5 × 12 + 8 = 68) in a helper column, then point the BMI formula at that. Mixing feet into an inches formula is the imperial version of the meters-versus-centimeters trap.

Auto-Classifying BMI With a Nested IF

A raw BMI number is only half the job. What most people actually want is the label: underweight, normal, overweight, or obese. A nested IF converts the number into that label without a lookup table.

Assuming the calculated BMI sits in D2, put this in the Category column (E2):

=IF(D2<18.5,"Underweight",IF(D2<25,"Normal weight",IF(D2<30,"Overweight","Obese")))

Read it as a chain of questions from the lowest threshold up. Is BMI under 18.5? If yes, "Underweight." If not, is it under 25? If yes, "Normal weight." Under 30? "Overweight." Anything left over falls into the final "Obese" bucket. The order matters: each check only runs when all the earlier ones fail, which is why you go low to high.

Building the Spreadsheet Step by Step

1. Set up your columns. In row 1, add headers: Name, Weight, Height, BMI, Category.
2. Enter the BMI formula. In the BMI column (say D2), enter the metric or imperial formula above, pointing at your weight and height columns.
3. Fill down. Select D2, grab the fill handle at the bottom-right corner of the cell, and drag it down through every row. Excel adjusts the cell references automatically, so row 3 reads from A3 and B3, and so on.
4. Add the classification column. Put the nested IF from the section above in E2 and fill it down the same way.
5. Format for readability. Select the BMI column and set the number format to one decimal place (Home → Number → Decrease Decimal, or Format Cells → Number → 1 decimal). This is cosmetic if you already wrapped the formula in ROUND, but it keeps the display tidy.
6. Optional: conditional formatting. Highlight the Category column, go to Home → Conditional Formatting → New Rule, and color-code by text value (red for "Obese," green for "Normal weight"). On a large roster, that turns a wall of labels into a scan-in-one-glance overview.

BMI Categories Reference (WHO)

The thresholds in the nested IF come straight from the World Health Organization's standard adult classification. Here they are in full, so you can adjust the formula if you need finer obese sub-classes:

These bands are for adults aged 20 and over. BMI is a screening tool, not a diagnosis, and it does not distinguish muscle from fat. If you are of Asian descent the risk thresholds shift lower, which is why the Asian BMI Calculator uses adjusted bands rather than these standard ones.

A Copy-Paste BMI Template

To skip the setup, build the sheet from this layout. Put the headers in row 1, then the two formulas in row 2, and fill both down as far as your data goes.

Row 1 headers: Name | Weight (kg) | Height (cm) | BMI | Category

Cell D2 (BMI), metric:

=ROUND(A2/(B2/100)^2, 1)

Cell E2 (Category):

=IF(D2<18.5,"Underweight",IF(D2<25,"Normal weight",IF(D2<30,"Overweight","Obese")))

Prefer imperial? Change the headers to Weight (lb) and Height (in) and swap D2 for =ROUND((A2/(B2^2))*703, 1). The Category formula stays exactly the same, because it reads the finished BMI number, not the units that produced it.

That is the entire template. There is nothing to download and no macro to enable, and it works identically in Excel, Google Sheets, and LibreOffice Calc. If you only need a single result rather than a whole column, the BMI Calculator returns it instantly without any setup.

Summarizing a Whole Roster

Once every row has a category, the obvious next question is how many people fall into each band. You do not need a pivot table for that. A single COUNTIF per category tallies the labels the nested IF already produced.

If your categories run from E2 to E31 (30 people), count each band like this:

=COUNTIF(E2:E31,"Overweight")

Repeat it with "Underweight", "Normal weight", and "Obese" to get a full breakdown in four cells. To express one band as a share of the group, divide by the total count of non-blank categories:

=COUNTIF(E2:E31,"Obese")/COUNTA(E2:E31)

Format that result as a percentage and you have an instant distribution summary that updates itself the moment anyone's weight or height changes. Pair those four counts with a simple bar or pie chart (Insert → Chart) and a 500-row roster becomes a one-glance overview of where the group sits. This is the point at which a spreadsheet clearly beats calculating entries one at a time: the summary is a byproduct of the same column you already built.

Worked Examples

Example 1: A Single Metric Entry

A person weighs 70 kg and is 170 cm tall. With weight in A2 and height in B2, the formula =ROUND(A2/(B2/100)^2, 1) computes 70 ÷ (1.70)² = 70 ÷ 2.89 = 24.2. That lands in the Normal weight band (18.5 to 24.9).

Example 2: A Single Imperial Entry

A person weighs 160 lb and is 68 in tall (5 ft 8 in). Using =ROUND((A2/(B2^2))*703, 1): (160 ÷ 68²) × 703 = (160 ÷ 4,624) × 703 = 24.3. Also Normal weight, and notice it matches the metric scale, that is the whole point of the 703 factor.

Example 3: Number Plus Category in One Row

A person weighs 85 kg at 175 cm. The BMI formula gives 85 ÷ (1.75)² = 85 ÷ 3.0625 = 27.8. Feeding that into the nested IF: 27.8 is not under 18.5, not under 25, but is under 30, so the Category cell returns "Overweight." One row, both outputs, no manual lookup.

Example 4: Handling a Blank Row Without Errors

Halfway down a 500-row roster, one patient's height is missing. A plain =A2/(B2/100)^2 would return #DIV/0! because it divides by zero. Wrapping it as =IF(B2=0,"",ROUND(A2/(B2/100)^2,1)) checks for a blank height first and leaves the cell empty instead, so one missing value does not litter the sheet with red errors.

Example 5: A Whole Roster at Once

You have 30 students. Enter the BMI formula in D2 and the Category formula in E2, select both cells, then double-click the fill handle. Excel copies both formulas down all 30 rows in one action, each row reading its own weight and height. A third student at 55 kg and 160 cm resolves to 55 ÷ (1.60)² = 21.5, Normal weight, at the same instant as everyone else. This batch behavior is the real reason to do BMI in a spreadsheet instead of one entry at a time.

Common Mistakes

The #DIV/0! error. This means the height cell is blank or zero, so the formula divides by zero. Add a guard: =IF(B2=0,"",ROUND(A2/(B2/100)^2,1)) leaves the cell blank instead of erroring when data is missing.

Height entered in the wrong unit. The most common mistake is putting height in meters (like 1.7) into a formula written for centimeters (like 170), or the reverse. Every result comes out absurd. Double-check that your units match the formula before you fill down.

Mixing metric and imperial mid-sheet. Weight in pounds paired with a metric formula, or kilograms with the 703 version, produces BMI values that are wildly wrong but not obviously errors. Keep a visible units label in the header row so you never blend systems in the same column.

Forgetting to round, then trusting the display. Setting the cell to one decimal place only changes what you see, not the stored value, so a copied or exported number can carry a long tail of digits. If the rounded figure needs to travel, round in the formula with ROUND, not just in the number format.

Treating the label as a diagnosis. A spreadsheet cheerfully prints "Obese" for a muscular athlete, because BMI cannot tell muscle from fat. The category is a screening flag, not a verdict. For body composition beyond BMI, pair it with the Lean Body Mass Calculator, and remember BMI misses where fat actually sits, which is what a waist-to-height ratio is designed to catch.

Frequently Asked Questions

What is the Excel formula for BMI?

For metric units: =A2/(B2/100)^2 where A2 is weight in kg and B2 is height in cm. For imperial units: =(A2/(B2^2))*703 where A2 is weight in lb and B2 is height in inches. Wrap either in ROUND(..., 1) to show one decimal place.

What is the imperial BMI formula in Excel (pounds and inches)?

Use =(A2/(B2^2))*703 with weight in pounds in A2 and height in inches in B2. The 703 factor rescales the pound-and-inch result onto the same BMI scale as the metric formula, so a given person gets the same BMI number in either unit system.

Can Excel automatically classify BMI as underweight, normal, overweight, or obese?

Yes. Use a nested IF that reads the calculated BMI cell (here D2): =IF(D2<18.5,"Underweight",IF(D2<25,"Normal weight",IF(D2<30,"Overweight","Obese"))). Each check only runs when the earlier ones fail, so the thresholds go from low to high.

How do I round BMI to one decimal place in Excel?

Wrap the whole formula in ROUND with a second argument of 1, for example =ROUND(A2/(B2/100)^2, 1). Rounding in the formula changes the stored value, unlike the Decrease Decimal button, which only changes how the number is displayed.

Does Google Sheets use the same BMI formula as Excel?

Yes. The syntax is identical in Google Sheets and LibreOffice Calc: =A2/(B2/100)^2 for metric and =(A2/(B2^2))*703 for imperial both work without any change, as does the nested IF for categories.

How do I calculate BMI for a large list of people at once?

Enter the formula once in the first data row, then select the cell and double-click the fill handle (or drag it down) to copy it to every row. Excel adjusts the cell references automatically, so each row reads its own weight and height. This batch fill is the main advantage of doing BMI in a spreadsheet.

How do I convert feet and inches for the imperial BMI formula?

Convert height to total inches first, in a helper column: feet times 12 plus the extra inches. For example 5 ft 8 in is 5 times 12 plus 8, which is 68 inches. Then point the BMI formula at that inches value. Feeding feet into a formula written for inches is a common source of wrong results.

Why does my BMI formula show a #DIV/0! error?

The height cell is empty or zero, and the formula divides by height squared. Guard it with an IF check: =IF(B2=0,"",ROUND(A2/(B2/100)^2,1)) returns a blank cell instead of an error when the height is missing.

Why is my BMI result wildly wrong even though there is no error?

Almost always a unit mismatch: height entered in meters into a centimeter formula, or weight in pounds paired with the metric formula. The math still runs, so no error appears, but the number is nonsense. Keep a visible units label in the header row and confirm the formula matches those units before filling down.

Can I use this same BMI formula for children or for people of Asian descent?

The raw arithmetic is the same, but the interpretation is not. Children and teens use age-and-sex BMI percentiles rather than the fixed adult bands, and Asian populations have lower risk thresholds. For the adjusted adult bands, use the Asian BMI Calculator rather than the standard cutoffs.

Do I need macros or an add-in to calculate BMI in Excel?

No. BMI is a single arithmetic formula and category labeling is a nested IF, both built into Excel, Google Sheets, and LibreOffice Calc out of the box. There is nothing to install, enable, or download.

References

• World Health Organization: Obesity and Overweight: the standard adult BMI classification bands used in this guide's formulas.
• Microsoft Support: IF function: official syntax and nesting rules for the IF formula used to label categories.
• CDC: About Adult BMI: how BMI is calculated and interpreted, including its limitations as a screening tool.