Excel Daily Calorie Counter 🥕

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.

Excel Calorie Counter Intro

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.

Calorie Counter Warning

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:

• https://nutritiondata.self.com/ (type a food in the search box at top right)
• mynetdiary.com (sign-up is required, free membership available)
• https://www.exercise4weightloss.com/weight-watchers-points.html (pdf files with restaurant foods at bottom of page)

Daily Food Tracker

The main sheet is named FoodEntry. Follow these steps each day, to track your calories.

• NOTE: In the workbook for 2 people, there are 2 FoodEntry sheets. One has green cells for data entry, and the other has orange cells for data entry.

1) Date and Target

Each day, enter the date, and the target calories that your medial professional recommends as your daily calorie intake.

2) Meal and Category

Next, select the meal type (Breakfast/Lunch/Dinner/Snack)

Then, in the next column, use the drop down list to select a food category.

3) Food Item

In the Food Item column, click the drop down arrow, then select one of the foods from that list.

• The drop down list was created with dependent data validation. The drop down list limits the food item selections, based on the Category selected in each row..

4) Number of Servings

Finally, enter the number of servings that you ate, based on the single serving amount that appears in the Measure column.

• In the example shown below, the measure is 175 mL (about 6 oz).
• Type a 1 in the Qty column, if you are eating a single serving of that size.
• Or, type a different number, such as 0.5 or 1.5, if you are eating more or less than a single serving size.

5) Calories

After you enter a quantity, the Calories column will automatically show the total calories in your serving of the selected food item.

Save Daily Food Data

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:

• Checks cell B2, and shows a message if no date was entered
• Copies all the food entries from the green cells
• Pastes the food entries onto the DailyRecord sheet, below any previous entries
• Adds the entry date in column A for each row that was pasted
• Clears the green cells on the FoodEntry sheet (Date and food data), so you can start fresh the next day.
• Refreshes the pivot table report on the FoodPivot sheet

Stored Food Data

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.

• One has an Excel table with green formatting
• The other has an Excel table with orange formatting

Built-in Food List

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!)

Add More Items to Food List

You can add new foods to the FoodList table, or edit the existing food items.

To add a new food item, follow these steps:

• Scroll down to the end of the food list table
• In the first blank row below the table, type a category name, such as Vegetables
• Press Tab, to go to the cell to the right. The table will automatically expand to include the new item's row
• Type the name of the new food item, then press Tab
• Type the measure for 1 serving of the food item, such as 1 piece or 15 ml, then press Tab
• Type the number of calories in 1 serving, then press Enter

• Next, follow the steps below, to sort the list -- otherwise the Food Entry drop downs won't work correctly

Sort the list

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

• Select any cell in the food list table
• On the Excel Ribbon, click the Data tab, then click the Sort button
• In the Sort By drop down, choose Category
• Click the Add Level button
• In the Then By drop down, choose Food Item
• Click OK, to sort the list

Check the Food Summary

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.

• Use the filter at the top of the pivot table, to focus on specific parts of the 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.

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.

Recipe Calculator

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.

Enter a Recipe Name

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)

Enter Ingredient Details

2) Next, in the lower section, start to enter the ingredient details:

• From the drop down in column B, select a category for the first ingredient
• From the drop down in column C, select the ingredient name
  • Note: This drop down only shows items from the selected category

Enter Ingredient Amounts

Next, you'll enter the amounts for the first ingredient. There are two amounts required:

• Recipe Amt: The ingredient quantity shown in your original recipe
• Meas Amt: The food item measurement stored in the workbook's food list

Recipe Amount

In columns D and E, you'll enter the ingredient information from your original recipe.

• Column D: Enter the number for the amount required
• Column E: From the drop down list, select the ingredient's unit of measurement

For example, in the screen shot shown below, the recipe calls for 1.25 pounds (lb) of chicken.

• Column D: 1.25
• Column E: lb

Measurement Amount

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.

• Column F: Enter the number from column H
• Column G: From the drop down list, select the unit of measurement shown in column H

For example, in the screen shot shown below, the Food List has the nutrient information for 100 grams (g) of chicken

• Column F: 100
• Column G: g

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.

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 thick border 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
  • 

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.

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

How Recipe Calculator Formulas Work

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

Units Lookup List

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.

• The first column, Unit, is a named range, UnitsList. That list is used for the measurement drop downs on the Recipe Calculator sheet.
• The entire data section of the table is a named range UnitLU. That range is used in the Recipe Calculator formulas, described below.

Multiplier

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))

Convert Function

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.

For example:

• The recipe calls for 1.25 lb of the first ingredient, and the stored serving size is 100 g.
• The CONVERT function converts the number (1.25) from pounds to grams
• Then the formula divides by the number of grams in a single serving
• The multiplier formula calculates that 1.25 lb is equal to 5.67 servings of the stored ingredient size.

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)

Use the Multiplier

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)

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

Excel Calorie Counter Macro

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:

• Gets the number of entries from cell G30 (named DailyItems)
• Gets the entry range (6 columns), and the input range (4 columns), based on that item count
• Checks cell B2, which is named "FoodData", to see if it is empty
  • If empty, shows a message, "Please enter a date", and stops the macro
• Copies the items in the entry range
• Pastes (as Values) the food entries onto the DailyRecord sheet, below any previous entries
• Adds the entry date in column A for each row that was pasted
• Clears the green cells on the FoodEntry sheet (Date and food data), so you can start fresh the next day.
• Refreshes the pivot table report on the FoodPivot sheet

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

Copy Excel VBA Code to a Regular Module

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.

Get the Excel Calorie Counter

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.

More Tutorials

Excel Weight Loss Tracker

Calorie Burn Calculator

Weekly Meal Planner

Holiday Dinner Planner

Christmas Planner