Calories, carbohydrates, proteins, fats and fiber diary in Excel
This file will help you track what you eat. The file contains 17 food categories, eating diary and statistics. There are also some sample data for better orientation how it works. It’s very easy to use it. You simply choose food from the list and type the quantity. Excel automatically calculates everything else (like number of calories, carbohydrates, proteins, fats and fiber).
How to write down my eating
Use the worksheet called “Eating diary”. For example, you eat 150 grams of Nestle Fitness Corn flakes with 100 ml Semi-skimmed milk. Each item must be entered separately.
- Enter a date below the last line of the table.
- Select the Type. (Grains)
- Select the Item. (Nestle Fitness Corn flakes)
- Enter the Quantity in units that are in the column Units (150). Units can be changed but not in this table!
- Excel calculates everything else.
- Repeat this procedure for the next food.
Statistics
Your diet statistics are made by an ordinary Excel PivotTable on the sheet “Statistics”. It shows sum of each day.
The Pivot table doesn’t show the newly added data immediately. It has to be refreshed or you have to save, close and open again the file. To refresh the statistics table, do:
- Right-click somewhere into the statistics table.
- Select “Refresh” from the menu.
My calculations
You can use the “My calculations” worksheet if you need more than basic day statistics. This is place for your own tables and formulas.
How to save or edit food into the database
You have to save the food into the database before the first use. The file contains 17 named categories of food and 2 custom categories. Each category is on the separate worksheet. The list of categories is below.
For example, I want to save Peanuts (1 oz, Calories: 161, Carbohydrates: 4.6, Proteins: 7.3, Fats: 14, Fiber: 2.4)
- Find the right category. (Nuts & peanuts)
- Enter the name of food below the last line of the table.
- Enter the quantity.
- Enter the unit.
- Enter the rest of the values (Calories, Carbohydrates, Proteins, …).
- Now you can use Peanuts in the Eating diary.
The basic unit does not to be exactly 1 oz. You can use cups, fl oz, gram, ml or whatever you need, it is up to you. The basic value also doesn’t to be 1. You can use any number.
For example, I bought a bag of chips and eat 150 grams. On the back side of the bag is a table with nutrition. But there are values only for 30 grams. So I enter this into the database.
Crusty Croc Chips: 30 g (grams), Calories: 158, Carbohydrates: 1.7, Proteins: 14, Fats: 10, Fiber: 1.2
Food categories
| Drinks & Juices |
| Eggs |
| Food supplements |
| Fruits |
| Grains |
| Legumes |
| Meat Fish & Shellfish |
| Meat Red |
| Meat Sausage |
| Meat White |
| Milk & Milk Products |
| Nuts & peanuts |
| Oils & Fats |
| Pastry |
| Spices |
| Sweets |
| Vegetables |
| My category1 |
| My category2 |
How to change the category name
I strongly recommend to do backup copy of your Eating diary file before any change in categories.
There is the hidden sheet named Type. It contains the list of categories that can be changed. The category name is joined with the name of the source table. Both have to be exactly the same (and without spaces). The name of the source table (table with items) can be edited in Formulas >> Name Manager or in Table tools >> Design >> Table Name.
In this video example, I will rename the Drinks & Juices category to Abcd. As you see, it doesn’t matter of the sheet name. Only the source table and the item in Type sheet is important.
How to add the new category
In this example, I will create the new category called CategoryName 🙂 As in the previous, it doesn’t matter of the sheet name.
Free download
This file is free for any use.
Version with sample data: eating_diary.xslx
Clear version: eating_diary_clear.xlsx
Edit. 02/10/2015
– added new videos: How to change the category name, How to add the new category
– repaired formulas in eating_diary_clear.xlsx
– removed sheets protection – feel free to do any updates or changes you want 🙂
Aryan yadav 22 November 2023 12:15 Am
I like the sheet how thay provide sheet to know the information work you are wonderful
Thank you
Aryan yadav 22 November 2023 12:15 Am
I like the sheet how thay provide sheet to know the information work
Thank you
Aryan yadav 22 November 2023 12:15 Am
Thank you
how to DL the template?
Hi, I had to move the template to Google Sheets because Excel was behaving rather oddly by not saving the updates randomly and not retaining version history etc. (I had to reenter two days worth of data yesterday and it was not fun!)
I was able to move the Workbook to Google Sheets with all the sheets and information intact except the formulas. The main hurdle is that the Eating Diary Sheet is not connected with the other tables. Since they are not connected, I am getting Error Message: “Input must be within the specified range. ”
Could you please share how to re-establish that connection or lead me to a tutorial or drop some hints may be? And after connecting the sheets, do you think the other formulas would work or would I need to change something? Really appreciate your help! Thanks a lot!!
This is a brilliant Excel workbook to keep track of daily diet. I was easily off by a few 100 calories per day when I was logging on my own on a piece of paper. I had started to create an excel sheet to be more systematic and accurate but then I found this. I could have never come anywhere close to how amazing this document is with pivot tables and across-the-sheet automated calculations. I have tweaked it to my needs by editing the items and serving sizes etc. Awesome job and thanks for making it available for free!!
how are you
im moizuddin here, i need daily calories intake food, salad, fruits, and dry fruits , excel sheet to easy calculate.
kindly send me excel sheet to my mail id.
best regards.
Hello,
I am so thankful for you sharing this workbook.. awesome piece of work it is.
I have added some more stuff to the file and would like to share with you.. for two reasons.. one I want you to confirm what I have done is giving correct calculations.. and second being you can share it with the world to get healthier.
Hi, I am unable to download the Excel sheet. I am clicking on the link but it’s doing nothing. Could you please share how you downloaded it?
Thanks!
Sorry there was no way I could delete the first comment. It worked on MS IE. Did not work on Chrome
Hi,
Your example does what I would like to do but unfortunately it does not have the food breakdown in sufficient details for my needs. E.g. I would need to add Saturated Fats, mono sat’ fat etc.
In your notes you say the workbook is unlocked but when I check it is says it is protected and a password is need to unprotect it.
How do I unprotect it to make my modifications?
Thansk Ian
download link dont work
If you use EDGE it will open and download.. I don’t know why Chrome will not let it download.
Thanks!
i love food
YES
All values are inside of some range (or whatever it is) with blue borders. You can expand that range, add more values, etc. What is that?
I figured out, it’s a table.
Hi
It did not download?
Left-click on the link > Save link as…
I’m not a robot. Really
Thanks for the template, its really useful. but how can we add more rows, for example adding data for vitamins A. C, D etc.
Is there a similar one with standard rather than metric values? I might use it if there was. But converting everything to metric is otherwise a constant extra step for me.
Units are completely in your hands. There is no need to use metric values. Predefined values are only used as an example. BTW. there are also units like cup, teaspoon, …
How do I add Sodium to the list of nutrients?
Awesome template! Do you have free nutrition plan?
I track my keto diet with this. I can recommend it.
I believe that this will help me to low my body fat 🙂
Does anybody have list of nutritional values of common food? Like pastries, fruits, vegetables, meat,..
This really looks awesome! The only problem is I get this #NAME? error under unit, total calories etc on the eating diary and I can’t seem to find the solution! Has anyone else encountered the same issue? If yes, how did you solve it?
Hi,
I have the similar issue. I see #REF error in unit, total calories etc on the eating diary.
Hi,
as it is mentioned in video. #REF error will disappear after you enter all values: data, type and item.
Hi,
#NAME error occurs mostly when Excel doesn’t recognize formula. Are you sure that you are editing the right cells? You can try to download version with sample data: eating_diary.xslx and fill your eating again.
I love this! Thanks for making my life easier 🙂
I was about to create one myself but it would take ages…
Overweight and human
I’m sending this again…..I received no conformation of my previous message delivery…..not sure if I was successful in proving that I am, indeed, a human………..but I am…..actually I’m a space alien in need earthly food education
I am looking to find a spreadsheet to use for daily planning. I have no need for history of yesterday and before. I would like to be able to mix and match items……add or subtract…modify…..my daily menu and observe how each item reflects the daily goal in real time. Can I do this….will it work for Mac……..and where is the download button? Please advise….
I am looking to find a spreadsheet to use for daily planning. I have no need for history of yesterday and before. I would like to be able to mix and match items……add or subtract…modify…..my daily menu and observe how each item reflects the daily goal in real time. Can I do this….will it work for Mac……..and where is the download button? Please advise….
how do i download this template for my use
Hello,
there is “Free download” section on the article bottom.
With version with sample data and clear version. Just click on the eating_diary.xslx.
Hello,
looks pretty awesome but every cell in the eating diary is #REF!
Can you help us to understand why ? What should be the formulas ?
thanks
This is the best excel app for tracking calories and proteins. I used it in my training plan and it worked prefect.
It is great with my fitness tracking app in Excel.
Thank you.
I need template with charts to see the progress of fat burning and calorie income. This calculator is great but some charts would it make perfect.
Thank you
I need to make a chart for my Bio Life Science class. Need a template for a food log with all calories, fat, protein….etc
I would like to keep track of my sugar intake, How can I add a column for sugar on each sheet?
You can try to expand all tables (add a column). Then copy the formula from last column. Be careful, the copied formula has to be exactly the same as original formula. Only difference has to be that the number 8 changes to 9. I hope it helps. I think this is one of the best app to track calories and eating diary.
Thank you. This is what I have been looking for. It is much better than my Android app calorie counter. I can set everything in my way. Superb
I don’t think so. Mobile apps are much better. Try it. The best android app for calorie counter and diet is the FatSecret Calorie Counter and is for free.
You can update this Excel template and make calculations and statistics by your own needs. This is not possible in the mobile app.
Hi Jan. Here’s an issue that needs a fix.
I autosave my file every 2 min, and save it after finishing some work.
When I reopen the file, I’m getting a repaired error notification, which requires the file to be saved to a new name.
Problem is, this happens every time I open the new file, too! The tables repaired are the same, however how do you identify them?
Not sure what to do. Please advise.
”
–
error028400_01.xml
Errors were detected in file ‘C:\Users\Public\Documents\_Food keto eating_diary 2.xlsx’
–
Repaired Records: Table from /xl/tables/table3.xml part (Table)
Repaired Records: Table from /xl/tables/table4.xml part (Table)
Repaired Records: Table from /xl/tables/table5.xml part (Table)
Repaired Records: Table from /xl/tables/table6.xml part (Table)
Repaired Records: Table from /xl/tables/table29.xml part (Table)
Repaired Records: Table from /xl/tables/table30.xml part (Table)
Repaired Records: Table from /xl/tables/table45.xml part (Table)
Repaired Records: Table from /xl/tables/table46.xml part (Table)
“
Great spreadsheet! I’ve not used pivot tables before, so editing has been fun.
I added food items, added a column to the food pages and the Eating Diary (and corrected the formula so it worked); changed the tab names; and update the TYPE tab. I checked all the new names in Eating Diary, and everything is working great there. The new column is pulling data from the pages.
Statistics is not though, even with refreshing. It wants to show me various levels for each main line. It is not showing a total column I added, even though it is check in the Pivot Table control panel. I’m not sure what to do. Please advise.
I figured out the StatisticsTab. The Row Labels had all kinds of extra sorts, when it should have been just “Date.” I’m guessing it went wacko after I updated Types.
BTW, when you change the Types and the Tabs, the respective tables are automatically renumbered. I didn’t have to change one of them.
On Eating Diary a new minor issue has cropped up: Columns Carb, Fiber, Net Carbs (new), Protein and Fats all have “inconsistent error” flags. After the save, the formula for those columns had to corrected. Besides turning them off for all those cells constantly, I’m not sure what to do.
The only other error is input. When I cross foot the calculations on the Statistics, I’m 100 calories off. A minor thing.
Okay, so my solution to the Eating Diary was to Move a Copy from the ‘eating diary clean’ Eating Diary tab to my file. I changed any columns I need (including adding and rearranging). I saved the document.
Then I copied and pasted columns A, B, C, D from the original Eating Diary to this new one Eating Diary (2) tab. Once I verified the numbers were correct (using sum function for columns F-H (or what ever you have added) a few rows below the table, I created a new pivot report. Instructions are in the Help menu.
On the new Statistics tab, I changed the color of the text for the Grand Totals so they nearly met the background. Grand totals don’t mean anything to me.
Next I renamed Eating Diary and Statics to ‘Eating Diary 1″ and “Statics 1” then hid them. I renamed my new Eating Diary Statistics tabs. Voila!
Because I make a lot the same recipes repeatedly, I use a fresh copy of Eating Diary and renamed it ‘Recipe Base.’
To use it, I make another copy of it, naming it for the recipe. Ignoring the date field, I put together the ingredients.
A few rows below the table I use sum to tally columns F-H (or what you have). I then divide those numbers by the number of servings. Next, I list the serving results on the appropriate page for the dish, so I can add it to the Eating Diary. I move this named recipe tab to the end of the tabs. It’s available to update and out of my way.
I found this template very helpful however, I have tried to add a new column (Total Sodium) and cannot get it to work. Is there something that I am missing? Again, thanks for developing this template – it is very helpful.
George, see my post.
I too am having trouble with the formulas on Eating Diary after adding a new column. My comments might help you get fix some of your errors. Until I figure out how to change the default formulas for Eating diary, I change them for the group on a daily basis.
The eating diary is fantastic however I have encountered an issue I don’t know how to fix. Running Excel 2010
When I get to line 27. Drop downs are no longer available and the Design tab goes away.
How can I keep this going after line 26?
This is fantastic but after two days I’m no longer able to enter data in the eating diary worksheet due to no drop downs
Very valid, pithy, suntccci, and on point. WD.
I experienced the same problem — go to the last good line – start at the date and tab to the end – it will go to a new line and all will be restored so that you can enter data again.
Excellent template…saved me an hour of work by just googling, and downloading!
I am on a low carb/ketogenic diet, and this works great for tracking my meal breakdown.
Thanks again!
I’d venrtue that this article has saved me more time than any other.
Hi there,
Just come across this and it is brilliant. How do i manage to change each category or add additional categories?
Thank you
Hi,
I created videos how to do that. I hope this help 🙂
Thanks so much for putting this invaluable tool together. I keep in my Dropbox so that wherever I am and whatever device I am using I can add as I go through my day. Using this has helped me keep under 60 grams of carbs a day and that along with increasing my exercise slightly has helped me lose 12.5 pounds from December 26 to Jan 30.
Thanks for inciodutrng a little rationality into this debate.
I think your blank document doesn’t have all the formulas. I just used the sample one and cleared the data. That might be why the poster from February couldn’t get it to work.
@Sam
yes i am having the same complications with the blank sheet, i would love to edit and add in my own tabs and fully customize everything as my own but this file is protected,
Hi,
I repaired formulas and I removed protection, so you can do any changes you want 🙂
Excellent .A+ .Unfortunately does not work in Excel 2013
Hi,
I have tested this only in Excel 2007 but I’m pretty sure it has to work in 2010 and 2013 versions too. The errors had to be in my formulas – I repaired it, so you or somebody reads this can test it again. Please leave a comment if it works in your Excel version. Many thanks.
Why does this have to be the ONLY rellbaie source? Oh well, gj!
tHANKS