Lesson 65: How to create a BMI spreadsheet in Excel

I will show you how to create a BMI spreadsheet in Excel. Body mass index (BMI) is a measure of body fat based on weight and height that applies to both men and women.

How to create a BMI spreadsheet in Excel

1. Create a drop-down list in cell C5.

2. Insert the IF function in cells D7, D9 and D10 to display the units of measure.

3. Select Metric in Units. The IF function in cell D10 will return a blank cell. Create a conditional formatting rule that changes the format of cell C10 when you select Metric in Units (steps 6, 7, and 8).

4. Create BMI formula.

Explanation: C9 * 12 + C10 calculates height in inches and C9/100 calculates height in meters. Using standard units, BMI = 703 * pounds/inch^2. Using metric units, BMI = kg/m^2.

5. Enter 250 pounds weight. Excel calculates the body mass index (BMI) and automatically changes the background color of the area C13:E13. This feature is called conditional formatting (steps 6, 7, and 8).

6. On the Home tab, in the Styles group, select Conditional Formatting.

7. Click Manage Rules.

8. In “Show Formatting Rules Manager” select “This Worksheet”, to see all conditional formatting rules in this worksheet.

Note: select a rule and click Edit Rule to edit it however you want. The first 4 rules correspond to the BMI categories (underweight, normal weight, overweight and obese). Rule 5 changes the format of cell C10 when you select Units (step 3).

In addition, you can also see many other excel articles here