Home > Templates > Templates > Calories 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 the basic version, or download the Recipe Calculator version, so you can calculate calories for your favourite recipes.

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

▶

Warning: Use this workbook for entertainment purposes only, or in consultation your medical professional.

 

▶

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 facts.

2) Video: Excel Calorie Counter Demo

To get a quick demonstration of how the Microsoft Excel calorie counter works, you can watch my 1-minute video, below.

 

3) Daily Food Tracker

In the calorie counter workbook, the main sheet is named FoodEntry. Each day, follow the steps below, to track the calories from your daily meals and snacks..

▶

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.

3.1) Date and Target

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

3.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.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.

3.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.

3.5) Calories

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

At the top of the worksheet, in cell F2, a formula calculates the number of calories remaining, by subtracting the total daily calorie count, from your daily intake goal.

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

4.1) How the AddData Macro Works

Here's what the AddData macro does, to save the food data that you enter each day:

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

4.2) Stored Daily 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

4.2.1) Workbook for 2 people

NOTE: In the calorie counter 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

5) Built-in Food List

To see the built-in list of foods and their calories, go to the FoodList sheet, where the food data table is stored.

In the basic version of the Calorie Counter workbook, there are 5 columns with food information - Category, Food Item, Measure and Calories

5.1) Enhanced Food List

In the Calorie Counter with Recipe Calculator sample file, there are 9 columns with food nutritional data -- Category, Food Item, Measure, Calories, Protein, Fat, Carbs and Fibre (you can change the spelling to Fiber, if you prefer!)

6) Add/Edit Items in Food List

While you're using the Calorie Counter workbook, you can:

• add new foods to the FoodList table
• edit any of the existing food items

There are instructions for both tasks, in the sections below

▶

For more nutrient information, try one of these websites: Nutrition Value website Nutritionix website MyFoodData website

6.1) Add New Item to Food List

To add a new food item to the food list, follow the steps below:

1. First, go to the FoodList sheet, scroll down to the end of the food list table
2. In the first blank row below the table, type a category name, such as Vegetables
3. Press Tab, to go to the cell to the right. The table will automatically expand to include the new item's row
4. Type the name of the new food item, then press Tab
5. Type the measure for 1 serving of the food item, such as 1 piece or 15 ml, then press Tab
6. Type the number of calories in 1 serving
• In some workbooks, there multiple nutrient columns, so fill in those columns too
7. When all the nutrient data is entered, press the Enter key, to go to the next row.

▶

The food list must be sorted by Category and Food Item, or the dependent drop down list on the FoodEntry sheet will not work correctly. Continue to the next section for details.

6.2) Sort the Food list

After you add new food items, or make changes to the existing food items, you'll need to sort the list.

6.2.1) Sort Button

In some workbooks, there is a Sort List button at the top of the FoodList sheet.

• To sort the list, click the Sort Food button.

If there's no button on the FoodList sheet, go to the next section for sorting instructions.

6.2.2) Manually Sort the Food List

If there's no button on the FoodList sheet, follow the steps below, to manually sort the Food list

• First, select any cell in the food list table
• Next, 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

6.3) Edit Item in Food List

To edit an existing item in the food list, follow the steps below:

1. First, go to the FoodList sheet
2. Next, click the drop down arrow in the Food Item column heading
3. In the Search box, type all or part of the food name
4. Click the OK button, to see the filtered list of items
5. Find the specific item that you want to update
6. In that item's row, change any of the information
7. Next, click the drop down arrow in the Food Item column heading
8. In the menu, click Clear Filter From Food Name
9. Important: If you changed the item name, be sure to sort the food list.

7) 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. On that sheet you can see a pivot table that summarizes all of your stored food data.

7.1) Refresh the Pivot Table

To see the latest data in the pivot table, follow these steps:

• Go to the FoodPivot sheet
• Right-click on any cell in the pivot table
• Click the Refresh command, to update the data

7.2) Use the Pivot Table

You can leave the pivot table as it is, or make changes to the pivot table. Here are a few changes that I like to make in my pivot tables:

• Add another Report Filter at the top of the pivot table, and use it to focus on specific parts of the food data, such as category, or date.
• Choose different ways to summarize the data, by using Average or Max, or other functions.
• Use different ways to show the values, such as % of Column, like I did in the screen shot above.

You could also create more pivot tables to summarize your daily food data, in different ways. For example:

• Put the date in the row area, and group the dates, to create a weekly calorie tracker
• Put the Category in the row area, to see the total calories intake for each one
• For the Enhanced food list, put one of the nutrition types in the Values area, along with Calories

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.

8) Use the 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.

8.1) Enter a Recipe Name

At the top of the RecipeCalc sheet:

• Select a category for the new recipe
  • I use a category called "_MY_RECIPES"
  • That makes it easy to find the recipes that I added to the food list
  • The underscore at the start sorts to the top of the list!
• Enter the recipe name
• Enter the number of servings (cell J6)

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

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

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

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

8.4) Add 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.

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

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

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

9) 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 background information on how the calculations work

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

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

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

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

Here's how the formula works:

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

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

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

And here's how the formula works:

• 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

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

10.1) AddData Macro VBA Code

Here is the VBA code for the macro that saves the daily food data.

Note: In the section below, there's a video where I show how to add code to an Excel workbook.

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

10.2) 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 Files ✅

• 1) Excel Calorie Counter - Basic-- 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.
• 4) Recipe Calc & More Nutrients : Get this Calorie Counter has a Recipe Calculator and 12 nutrient columns - Calories, Protein, Carbohydrate, Fat, Iron, Sodium, Potassium, Phosphorus, Calcium, Cholesterol, Sugar, 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.

Get Monthly Excel Tips!

Don't miss my monthly Excel newsletter! You'll get quick tips, article links, and a bit of fun. Add your email, and click Sign Up.

Next, when you get my reply, click the Confirm button. I add this step to protect you from spam!

More Tutorials

Excel Weight Loss Tracker

Calorie Burn Calculator

Weekly Meal Planner

Holiday Dinner Planner

Christmas Planner

Excel CONVERT Function