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.
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.
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
|Drinks & Juices|
|Meat Fish & Shellfish|
|Milk & Milk Products|
|Nuts & peanuts|
|Oils & Fats|
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.
This file is free for any use.
Version with sample data: eating_diary.xslx
Clear version: eating_diary_clear.xlsx
- 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