Get this free Excel calorie counter workbook to track your daily food. Choose from the built-in list of foods, and add your favourite foods with their nutrition details. Get basic version, or Recipe Calculator version.
Use the basic Calorie Counter spreadsheet to keep track of the food that you eat each day, and the nutrients that the food 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, protein, 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 am 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 food product labels for accurate nutrition 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. Follow these steps each day, to track your calories.
Each day, enter the date, and the target calories that your medial professional recommends as your daily calorie intake.
Next, select the meal type (Breakfast/Lunch/Dinner/Snack)
Then, in the next column, use the drop down list to select a food category.
In the Food Item column, click the drop down arrow, then select one of the foods from that list.
Finally, enter the number of servings that you ate, based on the single serving amount that appears in the Measure column.
After you enter a quantity, the Calories column will automatically show the total calories in your serving of the selected food item.
At the end of each day, click the "Save Daily Data and Clear" button, at the top of the FoodEntry sheet.
That button runs a macro, named AddData, to save the food data, and clear the green cells.
Here's what the AddData macro does:
Here is a screen shot of stored records on the DailyRecord sheet. The dates are stored in column A, which is not shown
NOTE: In the workbook for 2 people, there are 2 DailyRecord sheets, with colour coding.
To see the built-in list of foods and their calories, go to the FoodList sheet.
In the basic version of the Calorie Counter workbook, there are 5 columns with food information - Category, Food Item, Measure and Calories
In the Calorie Counter with Recipe Calculator sample file, there are 9 columns with food information -- Category, Food Item, Measure, Calories, Protein, Fat, Carbs and Fibre (you can change the spelling to Fiber, if you prefer!)
You can add new foods to the FoodList table, or edit the existing food items.
To add a new food item, follow these steps:
Next, follow the steps below, to sort the list -- otherwise the Food Entry drop downs won't work correctly
IMPORTANT: The list must be sorted by Category and Food Item, or the dependent drop down list on the FoodEntry sheet will not work correctly.
After you add new food items, or make changes to the existing food items, follow these steps, to sort the list
At the end of each day, you click a button to store that day's food data on the DailyRecord sheet.
To see a summary of the food data, go to the sheet named FoodPivot sheet.
On that sheet you can see a pivot table that summarizes the stored food data.
NOTE: In the workbook for 2 people, there are 2 FoodPivot sheets. One has a pivot table with green formatting, and the other has a pivot table with orange formatting.
The enhanced version of the Excel Calorie Counter has an additional feature to help with your calorie tracking -- a Recipe Calculator. Use this tool to add your own recipes to the food list, with nutrition information.
There are 3 sample recipes in the sample file, and you can add as many more as you need. Follow the steps below, to add a new recipe to the food list.
At the top of the RecipeCalc sheet:
2) Next, in the lower section, start to enter the ingredient details:
Next, you'll enter the amounts for the first ingredient. There are two amounts required:
In columns D and E, you'll enter the ingredient information from your original recipe.
For example, in the screen shot shown below, the recipe calls for 1.25 pounds (lb) of chicken.
After you select an ingredient in column C, its information from the food list appears in the grey columns at the right side of the list.
For this next step, you'll use the measurement information that appears in column H.
For example, in the screen shot shown below, the Food List has the nutrient information for 100 grams (g) of chicken
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.
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.
Read this section if you're interested in seeing the formulas that are in the Recipe Calculator, with a few notes on how the formulas work.
You don't need to read this section! It's just background information on how the calculations work
The recipes that you enter might use the same measurement system that's used in the Built-in Food list (metric). Or, your recipes might use a different measurement system, such as Imperial or US.
On the sheet named Lists, there is a list of measurement units, in an Excel table.
In the Recipe Calculator, a formula in column N calculates a multiplier, in case the recipe amounts need to be converted to a different measurement system. Here is the formula in 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))
The Multiplier formula uses the Excel CONVERT function, to convert the recipe number, in column D, to the equivalent amount in the food list units.
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:
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:
Here is the Excel VBA code for the AddData macro that runs, when you click the Save Daily Data and Clear button on the FoodEntry sheet.
Here's what the AddData macro does:
Sub AddData() Dim lRow As Long Dim lRowNew As Long Dim wsData As Worksheet Dim wsEntry As Worksheet Dim rEntry As Range Dim rInput As Range Dim ItemCount As Long Set wsData = wsRecord Set wsEntry = wsInput ItemCount = wsEntry.Range("DailyItems").Value Set rEntry = wsEntry.Range("InputStart") _ .CurrentRegion.Offset(1, 0).Resize(ItemCount) Set rInput = rEntry.Resize(, 4) lRow = wsData.Cells(Rows.Count, 1).End(xlUp).Row + 1 With wsEntry If .Range("FoodDate").Value = "" Then MsgBox "Please enter a date" .Range("FoodDate").Activate GoTo exitHandler End If rEntry.Copy wsData.Cells(lRow, 2).PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, Transpose:=False lRowNew = wsData.Cells(Rows.Count, 2).End(xlUp).Row wsData.Range(wsData.Cells(lRow, 1), wsData.Cells(lRowNew, 1)).Value _ = wsEntry.Range("FoodDate").Value .Range("FoodDate").ClearContents rInput.ClearContents .Range("FoodDate").Activate End With wsPivot.PivotTables(1).RefreshTable exitHandler: Set wsData = Nothing Set wsEntry = Nothing Set rEntry = Nothing Set rInput = Nothing Exit Sub errHandler: MsgBox "Could not copy data to database." GoTo exitHandler End Sub
To see the steps for pasting a macro into a workbook, and running the macro, please watch this short video tutorial. The written instructions are on the Add Code to Excel Workbook page.
1) Excel Calorie Counter -- Download the basic Excel Calorie Counter workbook. The food list in this workbook has calorie information only. 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.
2) Excel Calorie Counter - No Macros-- Get the No Macros Excel Calorie Counter workbook. The food list in this workbook has calorie information only. The zipped file is in xlsx format and does not contain any macros. Manually copy daily data, and paste as values, onto the record sheet.
3) With Recipe Calculator: Choose the 1 person version or the 2 person version of the Calorie Counter with Recipe Calculator workbook. Same features as basic Excel Calorie Counter, plus a Recipe Calculator, and additional nutrient columns - Calories, Protein, Fat, Carbs and Fibre. The zipped files are in xlsm format and contain macros. Enable macros when you open the file, so the Save Data buttons will work correctly.
NOTE: Also, see another calorie calculator on the Calorie Burn Calculator page. It has instructions and a sample file.
Last updated: May 22, 2022 3:47 PM