Excel Calorie Counter

Excel Calorie Counter

On Monday I described the horrible side effects of Christmas cookies, and posted an Excel Weight Loss Tracker workbook. Now, here is an Excel Calorie Counter to add to your tool kit.

Weight Loss Tracker

As Dick Kusleika pointed out in the comments for the Weight Loss Tracker, simply downloading the workbook doesn’t have any effect on your weight. Apparently it takes a bit of effort, including dietary changes and maybe some <gasp> exercise. Can’t Microsoft invent something to make weight loss easier? I don’t even care if it has menus or a Ribbon!

Excel Calorie Counter Food Data

Bill Gates isn’t answering my phone calls, so I created an Excel Calorie Counter that I can use in the meantime. As soon as Bill sends me something better, I’ll let you know.

Here’s a 1-minute video that shows how the Excel Calorie Counter works. There are written steps below the video.

Building the Calorie Counter

To start, I copied some food data from the Health Canada website. I created a list of foods in an Excel workbook, with categories, food items, measurements and calorie counts.

You can add items to the list, and sort the list by category when you’re finished adding food items.

ExcelCalorieTracker05

Metric Measurements

The measurements are metric, so I added an approximate conversion list too, in case you don’t speak metric. There’s also a table where you can do your own conversions, by typing a number in the green cells. The CONVERT function calculates the amount in the adjacent white cell.

ExcelCalorieTracker06

Add More Columns to the Food Lookup Table

The food lookup table in the sample file only has calories list. If you want to add more columns to the lookup table, such as fat or carbs, you’ll also need to change the named range for the lookup table.

  • In Excel 2007, click the Formulas tab, and click Name Manager (In Excel 2003, click Insert | Name | Define)
  • In the list of names, click on  FoodLookup
  • In the Refers To box, at the bottom, change the number of columns, from 3, to the number of columns in your revised table.

=OFFSET(FoodList!$B$1,1,0,COUNTA(FoodList!$B:$B)-1,3)

  • Click the check mark at the left, to confirm the change, and click Close

Also, see below, for adding more columns to the Daily Calorie Counter

The Daily Calorie Counter

In the main sheet, named FoodEntry, you can enter the date, your target calorie count, and all the foods that you eat. There are only 22 rows for data entry, so that should help limit your food intake!

In the green data entry cells, there are drop down lists, created with Excel’s data validation. The Meal/Snack and Category source lists are stored in named ranges on the Lists sheet.

There’s a dependent data validation drop down list in the Food Item column, based on the Category that you select. This list comes from the FoodList sheet, so it’s important to keep the FoodList sheet sorted by category. Otherwise, the wrong items might appear in the Food Item drop down list.

ExcelCalorieTracker01

Add the Quantity

After you select a food item, its measurement appears, and you can enter the quantity that you ate, based on that measurement. For example, if the measurement is 125mL, and you ate 250mL, type 2 as the quantity.
Once the quantity is entered, the total calories for that food item are calculated.

ExcelCalorieTracker02

NOTE: If you can’t find a specific food, just type it in the cell, and enter the calorie count manually. Or, add the food item to the lookup table on the FoodList worksheet.

Add More Columns to the Daily Calorie Counter

The Daily Calorie Counter in the sample file only has calories listed. If you added more columns to the lookup table, such as fat or carbs, you’ll also need to add those columns to the Daily Calorie Counter sheet.

  • On the FoodEntry sheet, insert extra columns to the right of column G
  • Select cells G4:G26, and drag to the right, creating an extra column for each of your new columns
  • The heading cells should automatically fill in, based on the headings on the Food Lookup table.
  • Select cell G5, and change the formula, so it includes the heading cells for all the columns in your food lookup table. In the sample file, the headings are in $B$1:$D$1. With one additional column, you would change that to $B$1:$E$1

=IF($D5=””,””,$E5*VLOOKUP($D5,FoodLookup,MATCH(H$4,FoodList!$B$1:$D$1,0),0))

  • Copy the revised formula down to the last row, and right, to the last column.

Also, see below, for adding more columns to the Calorie Counter Data.

Storing the Calorie Counter Data

After you’ve finished eating for the day, or at the start of the next day, you can move all the data to the Calorie Counter database. Just click the “Save Daily Data and Clear” button at the top of the FoodEntry sheet.

The daily data is copied, as values, to the DailyRecord worksheet, and stamped with the date that you entered on the FoodEntry sheet.

ExcelCalorieTracker03

Add More Columns to the Calorie Counter Data

The Calorie Counter Data in the sample file only has calories listed. If you added more columns to the lookup table, such as fat or carbs, you’ll also need to add those columns to the Calorie Counter Data sheet.

  • On the DailyRecord sheet, type the same new column headings, in the same order, that you added to the food lookup table,

Tweak the Save Daily Data Macro

When you click the “Save Daily Data” button on the Daily Calorie Counter sheet, all the data is copied to the Calorie Counter Data sheet. You’ll tweak the macro that button runs, so it copies your new columns too.

  • Right-click the “Save Daily Data” button, and click Assign Macro
  • Click the Edit button
  • In the following line of code, increase the 2 to include all your new columns. Currently, only 2 columns, F:G, will be updated in the macro.
    • Set rCalc = rEntry.Offset(0, 4).Resize(rEntry.Rows.Count – 1, 2)
  • Find the following line of code, and change its the heading reference, to match the revised formula in cell G4:
    •     rCalc.Columns(2).Formula = _
              “=IF($D5=””””,””””,$E5*VLOOKUP($D5,FoodLookup,MATCH(G$4,FoodList!$B$1:$D$1,0),0))”
  • Copy the line of code (above) that you just modified, and paste in another copy of the code, for each new column. For example, if you added one additional column, you would copy and paste that code once.
  • Revise each copied line of code, so it shows the next column number, and the cell reference for the heading in that column. For example, if there is one new column (H), and the lookup table headings are B1:E1, here is the revised copied code:
    • rCalc.Columns(3).Formula = _
              “=IF($D5=””””,””””,$E5*VLOOKUP($D5,FoodLookup,MATCH(H$4,FoodList!$B$1:$E$1,0),0))”

Summarizing the Calorie Counter Data

The stored data is in a dynamic range named FoodRecord, and there’s a pivot table based on that named range. When you click the button to save your daily records, the pivot table is automatically updated.

ExcelCalorieTracker04

I’ve add a few fields to the pivot table, to get you started on a report. You can add more fields, or rearrange things, to get the report structure that you’d like.

Download the Sample Excel Calorie Counter

You can download the latest version of the calorie counter on my Contextures website. It is in the Excel Sample Files page, in the User Forms section: UF0016 – Excel Calorie Counter With Recipe Calculator. For instructions on how to use the latest version, see this blog post: Excel Recipe Nutrients Calculator

PLEASE NOTE: As I mentioned on Monday, I’m not a medical professional, so use this workbook for entertainment purposes only. The calorie counts are from Health Canada, so the ingredients and calorie counts might be different where you live. Check the product labels for accurate numbers.

And some of the calorie counts might be incorrect – I’m pretty sure the actual calories for wine and cookies are much lower than the Health Canada estimates. 😉
_______

36 thoughts on “Excel Calorie Counter”

  1. I did! Hope you can continue to share your thoughts and experience with the rest of the Office community. Calorie counting and staying healthy are certainly on a lot of people’s minds right now.

    Best,

    Kim
    MSFT Office Outreach Team

  2. Thank you so much for this. I really needed to get my act together and this is so helpful, thank you for sharing your hard work!

  3. Hi,
    Just discovered your Spreadsheets – they’re just what I’ve been looking for!

    Only problem is, when I click the “Save Daily Data and Clear” button, I get the error message:

    ‘((#REF.xls could not be found. Check the spelling of the filename, and verify that the file location is correct.

    Can you advise please?

    Thanks!

  4. Very good work and an excellent demonstration of Excel skills.
    The wife is keen to use it but has the same problem as Ray Kay; Only problem is, when I click the “Save Daily Data and Clear” button, I get the error message:

    ‘((#REF.xls could not be found. Check the spelling of the filename, and verify that the file location is correct.’

    Fix this and it would be a good tool.

    Barks

  5. OK, I found the fix. Ray Kay do this.
    Right click the ‘Save & Clear’ button.
    Left click ‘Assign macro’.
    Left click ‘Add data’
    Left click ‘OK’

    Now it should work.

    Barks

  6. […] we’ll examine the TRIM function. In January, some people are trying to TRIM a few pounds, and my Excel Calorie Counter and Excel Weight Loss Tracker workbooks are popular. Unfortunately, the TRIM function won’t help […]

  7. Thanks for the spreadsheet. Wondering if you ever thought about adding a way to use the spreadsheet for two people, ie., a husband and wife who are counting calories together. I could create two separate excel files, but then i would have to continually add items to both food lists, rather than just one. Any thoughts?

  8. Hi,

    I’m very new to excel and vba so it all seems a bit intimidating. I wanted to create my own meal planner in Excel and I was recommended to check out your site. ( Here is where I first posted to Mr.Excel: http://www.mrexcel.com/forum/showthread.php?p=2631014&posted=1#post2631014 ) Your program looks really great!

    However, I wanted to create something that totals up amounts for all nutrients, and not just calories. I used a site, probably similar to what you used to copy and save food data into excel which includes details of calcium, vitamin A, etc. http://www.nal.usda.gov/fnic/foodcomp/search/

    I arranged my data little differently: The first column on the left is a list of nutrients. The following columns have the name of the food in the first row and then list the amounts of the nutrients in the first column per 100 gram serving. The last column shows the ranges recommended for my age, weight, and activity level, taken from here: http://fnic.nal.usda.gov/interactiveDRI/

    I was hoping I could learn how to create an interface that would allow me to select foods and amounts, total them, and compare nutrient totals with those recommended for my age, weight, activity level so that I could plan meals from day to day.

    Well, it looks like your program does roughly the same thing, except for that it only adds up calories. Probably if I study it and fool around with it long enough I’ll figure out how to alter and add features. But if you sent me and e-mail I could send you my tables and maybe we could figure it out together.

    Thanks a bunch,

    Gu

  9. Thanks for the spreadsheet. Its wonderful !!! To make it even better can you add extra 3 columns to calculate Proteins, carbs and fat ? Thanks

  10. I just came across this workbook the other day and, on one hand I love it, while on the other I am having a small issue with it.
    I am trying to expand the information to not only include calories, but fat, protein, carbs, etc. I have successfully added all the information I need to the list of foods, but my columns on the daily entry will not pull in information.
    All cells after the Calories column, which is listed first, are displaying a #REF error and I cannot for the life of me figure out how to fix it.
    I’m wondering if there’s anyway, as the creator, you would be willing to alter the workbook so that this works properly for me and send it to me.
    This would be very much appreciated as I have almost given up on the thing.
    Thank you!

      1. I’m also looking to add a sheet within the workbook, that would work similarly to the Daily Entry sheet, where I can input a recipe to calculate the nutritional info for 1 serving of said recipe.
        Should the same codes for the daily entry work for this as well? Or do I need to do something different?
        Thank you.

      2. @Autumn, I’ve done something similar, and will get a sample file up as soon as possible. You could use similar code, but you would just want to store the final result, with the recipe name and total nutritional information, plus number of servings, instead of saving all the ingredient rows.

  11. @Debra, Just wondering if you were able to do something where I can save the recipe totals to my food list.
    Copying the totals does work, but I was hoping there was something that would work faster and easier.
    Thank you.

      1. @Debra
        Thank you!
        I haven’t been diligent in using it, so I forgot about this.
        But I am starting a new plan on Monday, so if it’s possible to have it Monday that would be wonderful.
        Thank you!

  12. Thank you so much for a great spreadsheet. So easy to use and manipulate – even to meet Australian needs. Love it! 🙂

  13. Hi Debra,
    I have added columns to my FoodEntry sheet and am trying to fix the Save Daily Data macro.
    I am not finding the rows listed in the blog post to alter.
    For data entered, I have:
    COL E: quantity
    COL F: measure
    COL G:M : My nutrition counts
    When I click the Save Daily Data button, it is only transferring up to COL K.
    Here is the coding that is showing in mine:
    Sub AddData()
    ‘developed by contextures.com
    Dim lRow As Long
    Dim lRowStart As Long
    Dim lRowNew As Long
    Dim lRowEnd As Long
    Dim lCols As Long
    Dim lColsIn As Long
    Dim lColsC1 As Long
    Dim lRows As Long
    Dim lColStart As Long
    Dim wsData As Worksheet
    Dim wsEntry As Worksheet
    Dim rEntry As Range
    Dim rInput As Range
    Dim rCalc1 As Range
    Dim rCalc2 As Range
    Set wsData = wsRecord
    Set wsEntry = wsInput
    lRowStart = wsEntry.Range(“InputStart”).Row
    lColStart = wsEntry.Range(“InputStart”).Column
    lRowEnd = wsEntry.Range(“TotalRow”).Row – 2
    lRows = lRowStart = lRowEnd
    lCols = 10
    lColsIn = 4
    lColsC1 = 1
    Set rEntry = wsEntry.Range(wsEntry.Cells(lRowStart + 1, lColStart), wsEntry.Cells(lRowEnd, lColStart + lCols – 1))
    Set rInput = rEntry.Resize(rEntry.Rows.Count, lColsIn)
    Set rCalc1 = rInput.Offset(0, lColsIn).Resize(rInput.Rows.Count, lColsC1)
    Set rCalc2 = rInput.Offset(0, lColsIn + lColsC1).Resize(rInput.Rows.Count, lCols – lColsIn – lColsC1)
    rEntry.Select
    rInput.Select
    rCalc1.Select
    rCalc2.Select
    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
    rCalc1.Formula = _
    “=IF($D5=””””,””””,VLOOKUP($D5,FoodLookup,MATCH(F$4,FoodList!$B$1:$M$1,0),0))”
    rCalc2.Formula = _
    “=IF($D5=””””,””””,$E5*VLOOKUP($D5,FoodLookup,MATCH(G$4,FoodList!$B$1:$M$1,0),0))”
    .Range(“FoodDate”).Activate
    End With
    errHandler:
    MsgBox “Could not copy data to database.”
    GoTo exitHandler
    End Sub

  14. Dear Debra 🙂
    I’m making my own “diet plan” based on on eating less calories (300, 400, 500 or 600 cal)/day. The idea is to kill in total 20 kg respectively in 514, 386, 309 and 257 days.
    400.0 cal = 51.8 gr
    1000.0 gr = 7716.2 cal
    Overweight: 20.0 kg Diet time: 386 days
    For this purpose I find in your website an useful Exec-file (ExcelCalorieCounter2007Recipe.zip) having on missing data about “beer bottle unit” having no information of the size of the bottle. After browsing the web I guess that the bottle size is 341 ml. Hope you can add this data on the Excel-file.
    All my best for your projects 🙂
    Mario Hakulinen
    World citizen
    Fuzhou, Fujian, South-China

  15. oh wow! I wanted to make a similar spreadsheet using the USDA food list (which is in Excel) however since I am not Excel-literate I was struggling with how to easily make a page like your FoodEntry. I hope you don’t mind if I use your layout adapting it for the US and the foods I eat.

  16. Hi,
    I have download the spreadsheet but cannot get it to work. I am using Excel 2003. It opens and then immediately shuts down again. Is it anything to do with popups etc?
    TIA

Leave a Reply to Jay Allen Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.