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.

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

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

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.

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.

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.

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.

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.