How to Track Your Diet with Excel

How to make a diet tracking sheet in Excel

Let’s directly continue where we left off. So you’ve decided to get more fit and made yourself a diet plan. You know your daily target numbers for total calories plus protein.

But how to make sure you are actually hitting them?

How to track your diet

At least in the beginning, you’ll have to write down what you eat and make some calculations. No way around that, unless you’re a) the Terminator or b) some kind of a nutrition-savant. Eating healthy is easy – everybody knows how to do that at least on the conceptual level. Eating exactly 2000 calories a day with 150 grams of protein to a 50 kcal / 10 gram error margin  – that’s something completely different.

So a food diary it is. List the foods that you eat along with their amounts, and calculate the calories and macronutrients out of that data.

Luckily, we are not forced to rely on a pen and a notebook anymore. You could use one of the bazillion mobile apps, but I’d still recommend the good old Microsoft Excel. You’ll get to add “Good command of Excel” to your CV after all. The OpenOffice version is fine, too.

So let’s make a diet tracking sheet in Excel!

Setting up an Excel sheet for tracking

In fact, Excel can take care of most of the calculations necessary. After the set-up phase, the only thing remaining to you is listing the foods and portion sizes.

Let’s see how to do it. I’m going to use a diet tracking sheet I made for my colleague as an example.

Daily view

Below, you can see a screenshot of its main page, or let’s say tab.

That part of the tracking sheet is for making sample daily plans, to get some idea what 2000 calories with 130 grams of protein might look like. Hint: there are quite a many options that fulfil that requirement.

The green cells are for user input: for listing food items and their amounts. That’s where you come in. Simply type the name of the food item on column A, and the amount you ate on the same line on column F.

The grey cells are automatically calculated by Excel. For instance, cell B8 – the total daily protein – is calculated simply with

=SUM(A11:A50).

Calories are then calculated from protein, carbs, and fat with

=4*B8 + 4*C8 + 9*D8.

That’s easy.

But columns B to D are supposed to show the numbers of protein, fat, and carbs per each kind of food listed on column A, corresponding the amount on column F. Finally, column G is supposed to show the reference serving size for the type of food in question, i.e. if we are dealing with deciliters or grams.

Let’s see how we can pull that off.

List of ingredients

For that purpose, we have the Ingredients tab in the diet tracking sheet. Below, you’ll find a screenshot of that one.

As you can see, it has a list of food items and ingredients listed on column A. On column G, you see a typical serving size. And finally, columns B to D have the protein, carb and fat amounts per serving of the specific item.

Of course, filling the Ingredients tab can take a while. But, you’ll only need a few items to get started. After that, you can add new ones as you need them.

The greatest effort is probably figuring out the portion sizes. Luckily, the macronutrient composition is nowadays printed on almost anything you can buy from your local supermarket. Just use those, some measuring cups, and maybe a kitchen scale.

By the way: definitely, do this!

Making a 20-euro investment on the said scale also lets you use it for measuring the portions you are actually eating, i.e. the column F we described earlier. It makes following your diet plan so much easier, especially in the beginning. A huge amount of guesswork just disappears. You can always sell it later if it hurts your sensitivities.

Looking up ingredients

Let’s turn out attention back to the main Meal plan tab. Let’s say you have 2 deciliters of yoghurt for breakfast. You type “Yoghurt” in cell A11 and “2” in cell F11. Now, the sheet’s supposed to automatically calculate the macro contents of that serving.

How to do that?

Well, the following monster-piece of code will do just that (for protein):

=IF(ISNUMBER(MATCH($A11;Ingredients!$A$2:$A$3000;0));INDEX(Ingredients!$B$2:$D$3000;MATCH($A11;Ingredients!$A$2:$A$3000;0);1)*$F11;””)

To actually understand anything, let’s take it apart.

1. Finding the item

Let’s begin be finding out the position of “Yogurt” on the Ingredients tab.

For that, we’ll use the MATCH function, like this:

MATCH($A11;Ingredients!$A$2:$A$3000;0)

This line here takes the contents of cell A11 (meaning “Yogurt (unflavoured)”), and tries to find a match for it in the first column of the Ingredients tab, ie. Ingredients!$A$2:$A$3000. The final 0 argument to the MATCH function simply tells Excel that the match should be exact. If the match is found, the function returns its row position.

For brevity, let’s denote that by r (Note: of course you cannot really do this in Excel, at least very easily. When making your sheet, you should replace r with the actual piece of code.):

r = MATCH($A11;Ingredients!$A$2:$A$3000;0)

2. Getting macros per serving

Then, to get protein per serving of Yogurt we would use the INDEX function like this:

INDEX(Ingredients!$B$2:$D$3000; r ;1)

Here, Ingredients!$B$2:$D$3000 is the range of cells we are operating on. In this case, the cells actually containing the macros of each food item on our list. The second and third argument, r and 1, mean that we want the contents of the cell at row r and column 1. That would be the protein. For carbs, the third argument would be 2, and for fat 3.

3. Getting macros you actually ate

Of course, that is not enough yet. We want the actual numbers we are eating – not just the per-serving ones. For that purpose, we do a multiplication with the number of servings we are eating – in this case 2 (deciliters) on column F. For protein, that would be

INDEX(Ingredients!$B$2:$D$3000; r ;1)*$F11

Looking back to the Meal plan tab, you see that the reference serving size is also shown on column G. That is fetched exactly like the macronutrients are, i.e. with

INDEX(Ingredients!$G$2:$G$3000; r ;1)

Cleaning the output

Finally, don’t want to show all-zeros on rows that have no foods listed yet. For that, we use the IF function in the following fashion:

=IF(“a valid food is listed on column A”; “yes–>show the amount of e.g. protein”; “nope–>don’t show anything”).

For the first argument, to check if column A has a valid food item (or anything written at all), we use the ISNUMBER function like this:

ISNUMBER(MATCH($A11;Ingredients!$A$2:$A$3000;0)).

In other words, we simply check if the MATCH function described earlier returns anything sensible. If it does, we show the number of e.g. protein we calculated earlier. If not, we’ll simply show nothing with “”.

In other words, the nightmarish

=IF(ISNUMBER(MATCH($A11;Ingredients!$A$2:$A$3000;0));INDEX(Ingredients!$B$2:$D$3000;MATCH($A11;Ingredients!$A$2:$A$3000;0);1)*$F11;””).

Simple as that.

Expanding your sheet

Of course, the daily planning-style sheet shown earlier is not very useful for actually tracking your diet. But, it’s quite easy to suit the functionality described above for that purpose.

Below is an example of my diet tracking sheet in OpenOffice.

It’s in Finnish, but I think the idea is clear enough. The top line has the date, type of the day (lepo = rest = non-workout), and the numbers for protein, carbs, fat, and total calories.

Below, the foods I ate are listed in the second column, while the amounts are in column 7. Everything else is calculated by the software, exactly like in the earlier example.

Now get to it!

Excel sheets can be quite handy, but they ain’t gonna fill themselves.

-Antti

How to Track Your Diet with Excel