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.
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.
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:
The main sheet is named FoodEntry. Each day, enter the date, and thetarget calories that your medial professional recommends.
Then, select the time (Breakfast/Lunch/Dinner/Snack) and the 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.
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.
At the end of each day, click the "Save Daily Data and Clear" button, at the top of the FoodEntry sheet.
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.
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.
On the FoodPivot sheet, you can see a pivot table that summarizes the stored food data.
The enhanced version of the Excel Calorie Counter has another feature -- a Recipe Calculator.
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:
Next, in the lower section:
Next, you'll enter the amounts for the first ingredient:
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))
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))
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:
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.
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:
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:
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.
After your recipes are added to the list, you can select them in the FoodEntry worksheet, where you enter your daily food choices.
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.
Last updated: March 3, 2021 8:25 PM