Contextures

Excel Weight Loss Tracker

Download this free Excel Weight Loss Tracker spreadsheet to enter your target weight, and weekly weight loss goals. Then record your weight each week, in pounds, kilograms or stone measurements. BMI calculation is also available.

NOTE: You can also use this weight tracker if your goal is to gain weight.

Use the Weight Tracker

Use this Excel Weight Tracker if you're trying to lose or gain weight.

  • In the tracker spreadsheet, you'll enter your target weight, and your weekly goals
  • Each week, check your weight, and record your weight in the spreadsheet
  • On the Weight Tracker dashboard, see your progress toward your target, in the weight tracker chart

This short video shows how to get started with the Excel Weight Loss Tracker. There are written steps below the video

Video Timeline
  • 00:00 Introduction
  • 00:10 Getting Started
  • 00:23 Dashboard
  • 00:43 Weight Goals
  • 01:14 Target Weight
  • 01:43 Weekly Weight
  • 02:10 Check the Dashboard

Enter Your Weight Goals

The first step in using the Excel weight tracker is to enter your personal data and weight goal information.

  • Go to the WeightGoals worksheet in the Excel Weight Tracker file
  • In the top green cell, choose the measurement system you want to use -- pounds or kilos

Warning: Consult your doctor for recommended target weight and weekly weight change goals. This workbook does not contain any qualified medical advice -- it is a tracking tool only.

NOTE: You can get the Stone weight tracker, if you prefer that measurement system

Excel weight loss tracker

Green Cells

Next, fill in the remaining green cells. Consult your doctor for recommended target weight and weekly weight change goals.

  • Start date: The date you'll start tracking your weight
  • Start weight: Your weight on the start date
  • Target weight: The weight that you want to reach
  • Lbs/kg per week: The weight change you want to achieve each week

Grey Cells

Do NOT enter anything in the grey cells. There are formulas in the grey cells, to calculate the total weight change, and the target date when you will reach your goal.

Excel weight loss goals

Start Tracking Your Weight

After your goals are entered, you'll start tracking your weekly results.

There is sample data in the workbook, so follow these steps to clear that out, and replace it with your data.

  • Go to the WeeklyWt sheet, where weekly weights are entered. This sheet also calculates your weekly weight change.
  • In the green cells, clear the sample dates and weights.
  • Do NOT clear the Tgt Wt or Wt Change columns - they contain formulas.
    • clear sample data
  • In the first Date cell, type the start date that you entered on the Goals sheet
  • In the first Wt cell, type the start weight that you entered on the Goals sheet
    • enter start date and weight
  • Optional - You can delete the unused rows in the table, but do NOT delete the first row, where the formulas are stored.
    • delete unused rows

Weekly Weight Tracking

After you've finished the inital setup for the Weight Tracker, all you have to do is enter your latest results each week.

To record your weekly weight information, follow these steps:

  • Open the Weight Tracking workbook, and go to the WeeklyWt sheet
  • Click in the Date column, in the first blank row. (The table will expand automatically, after you enter the date)
    • select date cell in blank row
  • Type the date that you recorded your weight, then press the Tab key
    • TIP: To enter the current date, use the keyboard shortcut: Ctrl + ;
  • Type your current weight, then press the Enter key
    • enter date and weight

After you enter the date and weight, the Wt Change column calculates the weight change for that week, based on the previous week's weight and the latest weight.

weight change is calculated

Weight Tracker Dashboard

To see how things are going, check the workbook's Dashboard sheet. It shows the following information:

  • Your current weight and your target weight
  • Total weight change
  • How much weight remains until you reach your goal.
  • Line chart shows the weekly recorded weights, and your target weight.

Excel weight loss chart

BMI Calculation

One version of the Excel Weight Loss Tracker also has a Body Mass Index calculator (BMI calculator). It calculates your current BMI, and target BMI, and shows the healthy weight range for your height.

The BMI table is used as a lookup table for the ratings for the current BMI and Target BMI. The Minimum and Maximum ideal weight range is calculated from your height, and the Normal range in the BMI table.

Learn more about Adult BMI from the Center for Disease Control and Prevention website. The website cautions:

  • "BMI can be a screening tool, but it does not diagnose the body fatness or health of an individual. To determine if BMI is a health risk, a healthcare provider performs further assessments."

Excel weight loss chart

How the Weight Tracker Works

You don't need to read this "How It Works" section, unless you're interested in what's happening in the background of the weight tracker.

If you'd like to know more about the formulas in the workbook, see the explanations and screen shots below. Everything is done with formulas - there are NO macros in the workbook.

Here are the four main worksheets, and the details for each sheet are shown below.

AdminLists

The weight tracker workbook has a sheet named AdminLists, where lookup tables and formulas do the background work for the weight tracker. There are 3 sections in the AdminLists sheet:

Measurement System

At the top of the AdminLists sheet, there is a lookup table for the measurement system.

  • Range D3:D4 is named MeasureList, and that list is used in the drop down on the WeightGoals sheet.
  • Cell D6 shows the measurement type that you selected

Excel weight loss chart

  • Cell F6 has a MATCH formula that finds the selected measurement system in the MeasureList, and returns its location number
    • =IF(MeasureSel="","",MATCH(D6,MeasureList,0))
  • Cell E6 has an INDEX formula that returns the short code for the measurement type - Lbs or Kgs.
    • =IF($F6="","",INDEX(E3:E4,$F6))

Goal Type: Loss or Gain

In the next section of the AdminLists sheet, there is Goal Type (Loss or Gain) information.

  • Cell D8 is named GoalType
  • Range D11:D12 is named TrackType

The Loss/Gain lookup table has descriptions that are used in the workbook's formulas, and the multiplier used in weight change calculations.

Excel weight loss chart

  • Cell D8 (GoalType) has a formula that checks your current weight and target weight, and calculates if your goal is weight loss or weight gain.
    • =IF(COUNT(WeightGoals!C4:C5)<2, "", IF(WeightGoals!C4> WeightGoals!C5, D11, D12))
  • Cell D14 has a MATCH formula that finds the Goal Type in the TrackType list, and returns its location number
    • =IFERROR(MATCH($D$8,TrackType,0),0)
  • Cells E14 and F14 have INDEX formulas that return the description text. Their IFERROR functions return an empty string for errors.
    • =IFERROR(INDEX(E11:E12, MATCH($D$8,TrackType,0)),"")
    • =IFERROR(INDEX(F11:F12, MATCH($D$8,TrackType,0)),"")
  • Cell G14 has an INDEX formula that returns the multiplier. Its IFERROR function returns a zero for errors.
    • =IFERROR(INDEX(G11:G12, MATCH($D$8,TrackType,0)),0)

Chart Title

The last item on the AdminLists sheet is the formula in cell D16, which calculates the title for the Dashboard and Dashboard Chart.

Excel weight loss chart

The formula in D16 combines text with the Goal Type and the measurement type.

  • ="Weight " & GoalType & " Tracking - " & E6

WeightGoals

There are 4 formula cells on the sheet named WeightGoals.

Two of the formulas create labels in column B

  • Cell B6 combines text with the measurement type
    • =AdminLists!$E$6 & " per week*"
  • Cell B8 combines the measurement type and goal type:
    • =AdminLists!$E$6 & " to " & AdminLists!E14

Excel weight loss chart

The other two formulas are in the grey cells, in column C

  • Cell C8 shows the total weight change goal, after the start weight and target weight have been entered
    • =IF(COUNT(C4:C5)<2,0,C4-C5)*TypeMult
  • Cell C9 shows the target date that the weight goal will be reached, after the lbs/kg per week has been entered in C6
    • =IF(C6=0,"",C3+C8/C6*7)

WeeklyWt

The WeeklyWt sheet has formulas in columns D and E. Because this is a named Excel Table, the formulas should automatically fill down when new rows are added to the table.

In the Tgt Wt column, the formula shows the Target Weight from the WeightGoals sheet

=WeightGoals!$C$5

In the Wt Change column, the formula subtracts the weight in the previous row from the weight in the formula row.

=IF(ISTEXT(C1),0,IF(C2="","",C2-C1))

Excel weight tracker calculations

Dashboard

The Dashboard sheet has several formulas, which are explained in the next section:

  • 4 formulas summarize progress (blue - C5, C7, G6, G7):
    • Current Weight
    • Target Weight
    • Weight Change
    • Remaining
  • 2 formulas to show the heading information (yellow - E2, E4):
    • Sheet title
    • Current date
  • 4 formulas to show the selected measurement type - Lbs or Kgs (orange - D6, D7, H6, H7)
  • 1 formula for Hyperlink text (purple - F9)

NOTE: The colour coding is for this screen shot only, and not in the weight tracker workbook.

Excel weight loss chart

Current Weight

The Current Weight formula uses the LOOKUP function to get the last number from the Weight column, on the WeeklyWt sheet.

  • =LOOKUP(9.99999999999999E+307, WeightData[Wt])

Target Weight

This formula shows the Target Weight from the WeightGoals sheet

  • =WeightGoals!$C$5

Weight Change

This formula shows the sum of the Weight Change from the WeightGoals sheet

  • =SUM(WeightData[Wt Change])

Remaining

This formula subtracts the Target Weight from the Current Weight, then multiplies by the TypeMult value (1 or -1)

  • =(C6-G6)*TypeMult

Chart and Sheet Title

The chart title is calculated on the AdminLists sheet, and shown at the top of the Dashboard, and as the chart title on the Dashboard.

  • =AdminLists!D16

Current Date

In cell E4, the TODAY function calculates the current date: =TODAY()

Units of Measurement

At the right of each weight, the unit of measurement is shown. The formulas refer to the Measure Selected calculations on the AdminLists sheet

  • =AdminLists!$E$6

Weight Tracker - Stone

If you prefer to track your weight in stone and pounds, there is also a version for that measurement system. The Weight Goals sheet looks slightly different, and the dashboard shows stone and pounds.

Excel weight loss chart

On the AdminLists sheet, there is a weight section, where Stone weight (decimal) is formatted to show Stone and Pounds. The formatted weights are shown on the Dashboard.

Excel weight loss chart

Download the Weight Tracker

Weight Change Tracker Lb/Kg: Use this weight tracker to record weight changes in pounds or kilograms. There are NO macros in the zipped workbook, just formulas for the weight change calculations. Weight Tracker Lb/Kg

Weight Change Tracker Stone: Use this weight tracker to record weight changes in stone. There are NO macros in the zipped workbook, just formulas for the weight change calculations. Weight Tracker Stone

Weight Loss Tracker with BMI: Calculate target weight (pounds or kilograms) based on BMI, record weekly weight, review results in dashboard. Weight Tracker BMI

More Workbooks

Calorie Burn Calculator: To calculate the calories in your food, and how long it takes to burn those calories in various activities, go to the Calorie Burner Calculator page, and get the free workbook there.

Calorie Counter With Recipe Calculator: To keep track of what you're eating each day, go to the Calorie Counter workbook page, and get the free workbook there.

 

More Tutorials

Excel Calorie Counter

Calorie Burn Calculator

Weekly Meal Planner

Data Validation Basics

Named Excel Tables

INDEX and MATCH functions

Don't Miss Our Excel Tips

Don't miss my latest Excel tips and videos! Click OK, to get my weekly newsletter with Excel tips, and links to other Excel news and resources.

Get weekly Excel tips from Debra

Excel weight tracker calculations

 


Last updated: March 22, 2021 6:54 PM