Contextures

Excel Calorie Counter

Get this free Excel calorie counter workbook. Choose from the list of foods, and add your favourite foods, and their nutrition information. Get the basic version, or the recipe calculator version.

Excel Calorie Counter Intro

Use the basic Calorie Counter workbook to keep track of the food that you eat, and the nutrients that it contains.

There is also an enhanced Calorie Counter workbook that contains a recipe calculator. Input all of the recipe ingredients, and Excel calculates the calories, carbs, protien, fibre and fat per serving. After you calculate the recipe's nutrients, you can add that as an item in the workbook's food list.

Calorie Counter Warning

I'm not a medical professional, so use this workbook for entertainment purposes only, or in consultation with a medical professional.

Target Calories: Check with your medical professional for advice on your target calories.

Food Data: Most of the food data was obtained from Health Canada, so the ingredients and calorie counts might be different where you live. Check the product labels for accurate numbers.

If you're looking for more nutrient information, to add items to the food list, you can try these websites:

Daily Food

The main sheet is named FoodEntry. Each day, enter the date, and thetarget calories that your medial professional recommends.

enter your target calories

Then, select the time (Breakfast/Lunch/Dinner/Snack) and the food category.

select a food category

In the Food Item column, select one of the foods from that category. Dependent data validiation is used here, to limit the selections, based on the choice in Category column.

select a food from the category

Finally, enter the quantity that you are eating, based on the amount that appears in the Measure column. In this example, the amount is 175 mL (about 6 oz).

Type a 1 in the Qty column, if you are eating a serving of that size. Or, type a different number, such as 0.5 or 1.5, if you are eating more or less than that.

After you enter a quantity, the Calories column will show the total calories in your serving.

calories are calculated

Save the Food Data

At the end of each day, click the "Save Daily Data and Clear" button, at the top of the FoodEntry sheet.

calories are calculated

That clears the green cells on the FoodEntry sheet, so you can start fresh the next day.

All the food data is pasted onto the DailyRecord sheet, below any previous entries.

calories are calculated

The Food List

To see the stored list of foods and their calories, go to the FoodList sheet. You can add new foods, or edit the existing foods.

IMPORTANT: After you make changes, be sure to sort the list again, by Category and Food Item. The list must be sorted, or the dependent drop down list on the FoodEntry sheet will not work correctly.

calories are calculated

Food Summary

On the FoodPivot sheet, you can see a pivot table that summarizes the stored food data.

Choose different ways to summarize the data, by using Average or Max, or other functions. Or, use different ways to show the values, such as % of Column, as in the screen shot below.

calories are calculated

Recipe Calculator

The enhanced version of the Excel Calorie Counter has another feature -- a Recipe Calculator.

Enter a Recipe

There are 3 sample recipes in the sample file, and you can add as many more as you need.

At the top of the RecipeCalc sheet:

  • Select a category for the new recipe (I use "_MY_RECIPES", so it's easy to identify the ones that I entered)
  • Enter the recipe name
  • Enter the number of servings (cell J6)

Next, in the lower section:

  • Select a category for the first ingredient
  • Select the ingredient name

ExcelCalorieCountRecipe01

Enter the Amounts

Next, you'll enter the amounts for the first ingredient:

  • In columns D:E, enter the amount required for this recipe, and the unit of measurement
  • Next, in columns F:G, enter the amount, and unit of measurement, shown in column H

For example:

  • In the first row shown below, the recipe calls for 1.25 pounds (lb) of chicken.
  • The Food List has the nutrient information for 100 grams (g) of chicken.

ExcelCalorieCountRecipe04

Multiplier

In column N, a formula calculates a multiplier, based on the recipe unit (E9) and the measurement unit (G9). This formula uses the Excel CONVERT function.

Here is the formula from cell N9:

=IF(E9="Whole",D9/MAX(1,F9), IF(COUNTA(D9:G9)<4,1, CONVERT(D9,VLOOKUP(E9,UnitLU,2,0), VLOOKUP(G9,UnitLU,2,0))/F9))

Ingredient Information

In columns H:M, the grey cells show the nutrient information for the selected ingredient. These cells have formulas that use the VLOOKUP function, and the MATCH function. For example, this formula is in cell H9:

=IF($C9="","", VLOOKUP($C9,FoodLookup, MATCH(H$8,FoodList!$B$1:$H$1,0),0))

  • The VLOOKUP function looks for the selected ingredient (C9) in the first column of the range named FoodLookup.
  • To get the column number where the "Measure" is stored, the MATCH function looks for the heading (H8), in the food list headings (B1:H1)

In columns I:M, the results of the VLOOKUP function are multiplied by the multiplier in column N. For example, here is the formula for Calories, in cell I9:

=IF($C9="","",VLOOKUP($C9,FoodLookup,MATCH(I$8,FoodList!$B$1:$H$1,0),0)*$N9)

Add the Remaining Ingredients

Repeat the above steps, to enter all of the remaining key ingredients, as shown below.

NOTE: You don't need to enter herbs, spices, water, or other ingredients that won't impact the calorie count or nutrition calculations.

ExcelCalorieCountRecipe01

Total Nutrients

As you add ingredients to the recipe calculator, their nutrients are included in the total row, at the top of the RecipeCalc sheet. For example, here is the formula for total calories, in cell E6:

=ROUND(SUM(I$9:I$27)/$J$6,0)

  • The SUM function calculates the total calories for all the ingredients. (I9:I27)
  • That total is divided by the number of servings in cell J6
  • Finally, that result is rounded to 0 decimal places, by the ROUND function

Add Recipe to Food List

After you enter all the ingredients, the top section shows the calorie and nutrition calculations per serving.

To add the completed recipe to the food list:

  • Select the outlined cells in the top section -- cells B6:I6
    • NOTE: The ingredient list is not saved - it is only needed to calculate the total nutrients
  • Copy the selected cells
  • Go to the FoodList sheet, and scroll down to the end of the food list
  • In the first blank row below the list, right-click on the cell in column A
  • In the pop up menu, click Paste Values
    • paste recipe as values

Sort the List

IMPORTANT: After you add a new recipe to the food list,be sure to click the SORT LIST button, at the top of the FoodList sheet. That button runs a macro to sort the list , by Category and Food Item.

The list must be sorted, or the dependent drop down list on the FoodEntry sheet will not work correctly.

click the Sort List button

Select Your Recipes

After your recipes are added to the list, you can select them in the FoodEntry worksheet, where you enter your daily food choices.

  • Choose the category that you used for your recipe, such as _MY_RECIPES
  • Then, select the name of your recipe in the Food Item list

ExcelCalorieCountRecipe02

Download the Excel Calorie Counter

Excel Calorie Counter -- Download the basic Excel Calorie Counter workbook. The zipped file is in xlsm format and contains macros. Enable macros when you open the file, so the Save Data button will work correctly.

With Recipe Calculator: Download the Excel Calorie Counter with Recipe Calculator. This workbook has the same features as basic Excel Calorie Counter, plus a Recipe Calculator --calculate nutrients per serving in your favourite recipes, and add those to the Food List. The zipped file is in xlsm format and contains macros. Enable macros when you open the file, so the Save Data button will work correctly.

More Tutorials

Excel Weight Loss Tracker

Calorie Burn Calculator

Weekly Meal Planner

Holiday Dinner Planner

Christmas Planner

calorie calculator

calorie calculator

 

Last updated: March 3, 2021 8:25 PM