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.

  1. Enter a date below the last line of the table.
  2. Select the Type. (Grains)
  3. Select the Item. (Nestle Fitness Corn flakes)
  4. Enter the Quantity in units that are in the column Units (150). Units can be changed but not in this table!
  5. Excel calculates everything else.
  6. 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:

  1. Right-click somewhere into the statistics table.
  2. 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)

  1. Find the right category. (Nuts & peanuts)
  2. Enter the name of food below the last line of the table.
  3. Enter the quantity.
  4. Enter the unit.
  5. Enter the rest of the values (Calories, Carbohydrates, Proteins, …).
  6. 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 :)

Add a comment »31 comments to this article

  1. This is the best excel app for tracking calories and proteins. I used it in my training plan and it worked prefect.

    Reply

  2. It is great with my fitness tracking app in Excel.

    Thank you.

    Reply

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

    Reply

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

    Reply

  5. I would like to keep track of my sugar intake, How can I add a column for sugar on each sheet?

    Reply

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

      Reply

      • 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

        Reply

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

          Reply

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

    Reply

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

    Reply

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

      Reply

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

      Reply

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

      Reply

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

    Reply

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

      Reply

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

    Reply

    • Very valid, pithy, suntccci, and on point. WD.

      Reply

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

      Reply

  10. 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!

    Reply

    • I’d venrtue that this article has saved me more time than any other.

      Reply

  11. Hi there,

    Just come across this and it is brilliant. How do i manage to change each category or add additional categories?

    Thank you

    Reply

    • Hi,

      I created videos how to do that. I hope this help :)

      Reply

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

    Reply

    • Thanks for inciodutrng a little rationality into this debate.

      Reply

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

    Reply

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

      Reply

      • Hi,
        I repaired formulas and I removed protection, so you can do any changes you want :)

        Reply

  14. Excellent .A+ .Unfortunately does not work in Excel 2013

    Reply

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

      Reply

    • Why does this have to be the ONLY rellbaie source? Oh well, gj!

      Reply

  15. tHANKS

    Reply

Leave a Reply to Mary Cancel reply



Copyright © All Rights Reserved · Green Hope Theme by Sivan & schiy · Proudly powered by WordPress