Calorie Counting in Excel - Tutorial

[@9to5strength]

New Video!! Go here kzbin.info/www/bejne/moeZc2ico61lask to see how to add a drop-down menu, import macro information from another sheet and automatically calculate portion size.

[@colletteowen4979]

This is excellent and will be really helpful to me. Who needs fancy apps with annoying ads or paid subscriptions when you can make your own for free. Many thanks for the clear and very helpful tutorial.

[@9to5strength]

This was presented at a speed for someone who isn't too experienced with Excel, try 1.5x speed if you know what you're doing :-)

[@seanrobinsonjr4363]

If you are "experienced" with Excel, you should at least know the SUM function : o

[@rohitfitness1311]

Hi... I just want to know the formula for changing the serving size and automatically the nutrition and calories would change... For example we added data of 100g and we just want 50g

[@9to5strength]

I did an updated video with more fancy formulae including serving size (I think?), you can see it here kzbin.info/www/bejne/moeZc2ico61lask

[@Mariusake28]

why did you wrote on the second column lunch, what does that mean?

[@rik-keymusic160]

hi, is there a way i could like enter in a given food. For instance, i enter in Watermelon to which other cels immediately pull up the right amounts of protein, carbs and fat /100g for that given food from a food list on an other page... and when i enter in a given mount it automatically calculates it for that given value. So let say 250g it wil automatically adjust the macro's

[@9to5strength]

Hey, check out the more recent version of that video, I think I answered it there, if not let me know kzbin.info/www/bejne/moeZc2ico61lask

[@HannahRees]

Hi! Do you have a favourite place to find food macro info for when building your food database? And cooked Vs uncooked?

[@9to5strength]

Hey, there's not a lot of new foods in my diet but normally i'll just see what comes up in a search engine. If it hasn't got a label on it there's a good chance there's info online but it's quite frustrating that there are so many incorrect numbers circulating around and i'll often be left making a best estimate. I tried using the MyFitnessPal app but because it's all user entries there's loads of mistakes and some people just enter calories but no macro info. The cooked vs uncooked problem winds me up, I sympathise with anyone caught out by that as well as the "serving size" issue. The new guidelines say one serving of fruit juice is 150ml, so people drinking 250ml of Tropicana won't necessarily be told how much is in the whole bottle which is just silly. How about you? Guessing for body comp you're tracking everything down to fibre and also considering nutrient timing.

[@HannahRees]

9to5Strength I am not overly meticulous on tracking for myself at the moment but know roughly what I’m having by eye. I use myfitness pal-and agree it can be a bit of a mission working out what’s what. I tend to tell all clients cooked weight for most things meat/fish. Dry weight oats only really. Green Veg doesn’t really matter. Trying to think up a new way to build my plans effectively and quickly to give things a freshen up. I like the excel spreadsheet idea, so this video was really useful, thank you! Still haven’t quite sussed the best way to do it. The UK doesn’t have much education/courses on this. Do you know of anywhere?

[@9to5strength]

No I can't think of any course material on it. A lot of people rely on the traffic light systems we have on food labels that tell you if anything is high in saturates/salt but not many track calories or even know how many calories they should be getting in the first place. The excel spreadsheet really helped me out when I was first getting into nutrition because you're physically reading labels and start to build an idea of the macro breakdowns of the foods in your diet and can make more informed decisions in the supermarket. One approach for cooked weight is to relate it to the volume of food they make. If it's something like oats I measure raw and I can use e.g. a cup to measure and if I use the same cup each time I don't need to get the scales out, or say a sweet potato the size of my fist is about 300g etc. That way I don't need to use the scales and if someone else made the food I can just eyeball it on the plate as an estimate. I don't have clients but I help out a lot of friends and family and the biggest calorie traps I find are restaurants. If you want a resource for UK restaurants i'm building one on my site. 9to5strength.com/nutrition/uk/ I'll get them to name something they ate in the last week and then estimate the calories and they're normally off by about 50%.

[@danieljensen3781]

this was help me a lot thanks

[@ma.joycejaneazur6587]

how did you come up with 4 and 9 in the first formula?

[@9to5strength]

One gram of protein has 4 calories, one gram of carbohydrate has 4 calories, and one gram of fat has 9 calories, so that's an easy way to calculate total calories rather than read off the label. Sometimes the value on the label is a bit different because they can choose to ignore fibre sometimes. Sorry if that wasn't in the video!

[@tamaracachia5965]

IS there a way where you can change the portions of the foods & excel will calculate the NEW macros of a food? eg if you changed this to 3 pieces instead of 2?

[@9to5strength]

I'm sure there's a fancy way with VLOOKUPS but I do one of two things: 1) If it's something I have multiples of, e.g. 10g or 20g of dark chocolate, I'll either have both values worked out in my food database or I'll simply copy in 10 grams on two rows so it adds up to 20 grams. 2) If it's fractions, like having 80 grams of rice and then one day eating 100 grams, I'll do some maths in an empty row to work out the new values. So if I had 80 grams of rice and Protein, Carbs, Fat values were in cells C2, D2, E2, I'd find some empty rows and do =C2*100/80, =D2*100/80 and =E2*100/80. Then I'd type the result back into C2, D2 and E2, and delete my working. As long as you use =(cell)*new portion size/old portion size it should be fine. Quite hard to explain in text only :-)

[@tj9382]

All the quantities could be expressed per 100g serving. You’d enter your serving in grams and a cell would have a calculation something to the effect of your serving divided by 100 x the amount per serving.

[@leonardjr.f.dollete3619]

why the serving table is there if it serve no purpose on calculation?

[@9to5strength]

I think the idea was that the food database could have portion sizes as separate rows e.g. 1 piece of bread, 2 pieces of bread, then you copy and paste the amount you need. I introduced serving size to the calculation in the "sequel" video kzbin.info/www/bejne/moeZc2ico61lask

[@BARTEKS-ACADEMY]

Legend thanks mate made my life a lot easier

[@mayam.235]

How would you make a number present as a word? For example, apple=100.

[@9to5strength]

I'm not sure what you mean, like a unique ID for each food? I don't think you can type 100 and it converts that cell to say Apple, but you could get the adjacent cell to show it with a vlookup. e.g. if you stored your combinations of number, food in columns A & B and you put an ID in say, column Y, you could get a cell to fetch the ID in column Y and return the food associated with it using "=VLOOKUP(Y1, $A$1:$B$100, 2, FALSE)".

[@justinbursey3298]

THANKS A MILLION