Excel Calorie Counter

On Monday I described the horrible side effects of Christmas cookies, and posted an Excel Weight Loss Tracker workbook.

As Dick Kusleika pointed out in the comments, 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!

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

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

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

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

_______

Related Posts Plugin for WordPress, Blogger...

Share and Enjoy

  • Facebook
  • Twitter
  • LinkedIn
  • Google Plus
  • Pinterest

29 comments to Excel Calorie Counter

  • What a fun way to help keep people committed to their New Year's resolutions! I know the folks over at Excel would love for you to share your expertise and ideas with the community at the Office page on Facebook.

    Check it out at http://www.facebook.com/office.

    Cheers,

    Kim
    MSFT Office Outreach Team

  • Thanks Kim, I'm glad you liked the article.

  • 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

  • Contextures Blog » Excel Calorie Burning Calculator

    [...] Excel Calorie Counter [...]

  • Kelly

    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!

  • Ray Kay

    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!

  • Barks

    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

  • Barks

    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

  • Thanks Barks and Ray Kay -- I've fixed the Excel 2003 version of the calorie counter, where the problem was.

  • Aundrea

    Can you track more then calories I would like to add a column to track fat grams

  • Contextures Blog » 30 Excel Functions in 30 Days: 03 - TRIM

    [...] 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 [...]

  • Tim

    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?

  • Gu

    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

  • Tat

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

  • rob

    Great sheet. Came in very useful for a client of mine, I appreciate your efforts.

  • Dee

    Thank you! Excactly what I was looking for!!!!

  • Autumn

    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!

    • @Autumn, I have updated the blog post to show instructions for adding more columns to the workbook.

      • Autumn

        Thank you Debra! It finally works and I can't wait to start using it.

        • @Autumn, great -- glad it's working now.

          • Autumn

            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.

          • @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.

  • Autumn

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

  • [...] with Automatic Lines » Excel Recipe Nutrients Calculator A couple of years ago, I posted an Excel Calorie Counter workbook, which has been very popular. In one of the comments, Autumn suggested that I add a recipe [...]

  • Donna

    Thank you so much for a great spreadsheet. So easy to use and manipulate - even to meet Australian needs. Love it! :)

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>