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

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.

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

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.

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.

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

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.

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

_______

### 34 Responses

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

Cheers,

Kim
MSFT Office Outreach Team

2. Thanks Kim, I’m glad you liked the article.

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

4. Contextures Blog » Excel Calorie Burning Calculator says:

[…] Excel Calorie Counter […]

5. Kelly says:

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!

6. Ray Kay says:

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.

Thanks!

7. Barks says:

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

8. Barks says:

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

Now it should work.

Barks

9. Thanks Barks and Ray Kay — I’ve fixed the Excel 2003 version of the calorie counter, where the problem was.

10. Aundrea says:

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

11. Contextures Blog » 30 Excel Functions in 30 Days: 03 - TRIM says:

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

12. Tim says:

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?

13. Gu says:

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

14. Tat says:

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

15. rob says:

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

16. Dee says:

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

17. Autumn says:

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 says:

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

• @Autumn, great — glad it’s working now.

• Autumn says:

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.

18. Autumn says:

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

• @Autumn, thanks for the reminder! I’ll post the recipe calculator next week.

• Autumn says:

@Debra

Thank you!

But I am starting a new plan on Monday, so if it’s possible to have it Monday that would be wonderful.

Thank you!

• @Autumn, it will be in the Tuesday blog post, so I hope you can wait until then!

• Autumn says:

Tuesday will be fine. Thank you!

19. Donna says:

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

20. Autumn says:

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:

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

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

22. This link is a good example to “visualize” what 200 calories means:
http://www.boredpanda.com/what-200-calories-look-like/

23. Jay Allen says:

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.

1. March 13, 2012

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

2. January 12, 2017

[…] and reporting in Excel. I have an Excel Calorie Counter that you can download. Read about it in this blog post, and in this update […]