Reply
Results 1 to 4 of 4
  1. #1
    Registered User Dfaszer's Avatar
    Join Date: Feb 2013
    Age: 36
    Posts: 9
    Rep Power: 0
    Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10)
    Dfaszer is offline

    Post Calorie Tracker Spreadsheet

    Hi All,

    I created a spreadsheet to help me track my food intake and thought I would share it here.

    The Short Version

    It is simply a calorie tracker that tracks your food intake one week at a time. There is a tab for a weekly overview to see how you did during the week at a glance, tabs for Monday thru Sunday to enter in your food, a Food tab to create a database of the foods you eat, and finally, a Recipes tab to create recipes that you make.

    It is a Google Docs Spreadsheet, so you will need a Google account to use it, but anyone can view it, regardless if you have an account.

    Here is the link:
    (Per the forum rules, my post count is not high enough to post URLs as a hyperlink, so the following URL will need to be copied and pasted in the URL bar)
    docs.google.com/spreadsheets/d/1FOXCdCPXPT0_ljyuz6TOhT-zlwnDdTyMIrI1x1RrqEE/edit?usp=sharing

    I have disable editing and it is set for viewing only. You will need to create/download a copy of it in order to edit it in order to see its full functionality.




    Faszer
    Last edited by Dfaszer; 02-25-2017 at 09:09 PM.
    Reply With Quote

  2. #2
    Registered User Dfaszer's Avatar
    Join Date: Feb 2013
    Age: 36
    Posts: 9
    Rep Power: 0
    Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10)
    Dfaszer is offline

    Post The Long Version

    The Longer Version

    First off, what are the benefits of using this spreadsheet instead of the more common, commercial calorie trackers (myFitness Pal, Calorie King)?

    1) I personally found MyFitness Pal to be somewhat cumbersome (I haven't tried Calorie King). If I searched for a new food, there was no less than 20 different choices for me to guess which one to use. If I wanted to use a food that I have used in the past, it was usually on page 3 or 4 of my history. So not only would I have to go to page 3 or 4, but I would have to read each line item from page 1 to 4 just to make sure the food item wasn't on the previous page. Granted, many of these annoyances may have been fixed since I used it last; its been a while since I have.

    2) You are able to put in exactly the food you want with the nutritional value associated with that food. It requires a little more work on your part initially, but you will only have to do it once per food.

    3) If you need to change a food item or a recipe that you have created, it is very simple and allows for you to be more accurate with your tracking. For instance, you create a recipe for a simple sandwich. Bread, mayo, cheese, meat, and lettuce. Normally, you use cheddar cheese, but today you used, jack. Simply switch out the cheese without having to go thru complicated menus or creating a new recipe just for a small variation in the recipe.

    4) 100% customizable. Add or remove features that you want/don't want with basic spreadsheet knowledge. You could add a tab to track your workout, which is then linked to how much time is spent/calories burned on exercise a week, which could then be linked to your total daily energy expenditure (TDEE), which would then be linked to accurately tell you how many calories to consume based on your current goal.

    5) Lastly, this can be used offline for those that are worried about using too much data.


    In this next section, I will go over each tab in more depth.

    Weekly Summary
    (Also per the forum rules, my post count is not high enough to add images to the body of the post (or maybe I just can't figure out how), so I have attached images of each of the tabs)

    This tab is where you enter in all of your basic information and gives you a summary of the week at a glance.

    To start, enter in your basic bodily measurements, your activity level, and your goal (cut, maintain, bulk). Based on this info, using the Navy body fat calculator, it calculates your body fat, your lean body mass (LBM), basal metabolic rate (BMR), and your TDEE. It will then calculate your target calories for the day. Below the target calories is a calorie modifier which lets you add or remove calories to your target goal if you need it (Useful for when you are reverse dieting after a cut or going up to a bulk).

    Lastly, there is a place to enter your goal for your daily water intake as well as determining your macro breakdown. All of the tables at the top (target calories, water, and macro breakdown) are linked to the small tables associated with each day in the lower section of this tab. In the instance of calorie cycling (correct term?), these values can be changed for each day as desired.

    Please note that in the section that says "Calorie Breakdown Consumed", the sum of the percentages may not equal 100%. I'm assuming that this is attributed between the rounding error of the macros the nutrition label reports and the calories that it reports. A food may report 3g of protein and have 13 cals. I am guessing that while the nutrition reports 3g of protein, it is really closer to 3.25g of protein.


    Daily Tracker

    This section encompasses the tabs for Monday thru Sunday.

    At a quick glance, at the top of the tab is your macro, calorie, and water goals for the day. It lists how much you have consumed, your target, and how much you have remaining. The target values are linked to the values on the weekly overview tab and will update if the values are changed on the overview tab. There is also conditional formatting on the remainder line to give you a visual warning when you are getting close or have exceeded your goal.

    The bulk of these tabs are where you enter in your food that you have eaten. It has 9 columns where data is stored with 5 of those column being auto-populated.

    Column A - Meal) This is where you can number/label what meal you are having. I thought this was a good idea at first, but it is a lot of work to maintain and I generally don't use it. You can if you want...

    Column B - Food) This is where you will enter in the food that you are eating/plan to eat. There are two ways to do this. You can use the drop-down list that is generated by you favorites list, or by manually entering in the food. If you start manually typing in the food, the drop down list will open up and give suggestions on what you are looking for based on what you typed in. If the food you are eating is not in your favorites list, there will be an error on the cell (small red triangle on the top right of the cell. See attached picture). You can ignore this error as the cell functions will still work.

    Column C - Qty) This is where you enter in the quantity of food that you are eating. It needs to be in the same family (volume or mass) of units that is in your food database tab.

    Column D - Units) This is where you select the unit of measure that you are using for your food. It needs to be in the same family (volume or mass) of units that is in your food database tab.

    Column E - H - Macros/Calories) These are the column that store the nutritional information of the food that you have selected. In general, you do not want to edit these cell, but in the event you do, there is a simple way to restore the cell formula (discussed farther down). The few times where you would want to manually override the cell formula is when you want to enter in a one time food that you rarely eat and that you don't want to enter into your food database (the one time 1000 calorie burger on your cheat meal from you local burger joint).

    Column I - Comments) This section is reserved for any comments that you added to the food database. These values also auto-populate.


    Food

    This tab is where you put all of your food nutritional information. It should be pretty self explanatory, so I wont go over this tab in too much detail. A few things to note tho are the favorites column, the units, the comments, and sorting.

    Favorites column - Simply place an x (and only an x) in this column next to any food item that you want to add to your favorites list. Because of the ability of the favorites drop down (on the food tab) to suggest an item based on what you start typing, I have the majority of food items added to my favorites database on my personal copy of this spreadsheet.

    Units - This is where you enter in how you typically measure stuff. Be wary of measuring by volume or by mass. This section is my greatest annoyance, due to the fact that sometimes there are multiple ways to measure something. A quarter cup of shredded cheese on one day and an ounce (mass) of cheese the next day. The program only allows for one or the other. So if anyone has a suggestion on how to improve it, I would love to hear about it. This program will convert between different sizes (teaspoon vs cup) and English and Metric (pound vs gram).

    Comments - This is just an area to add a little note if you need it. Whether or not you are supposed to measure the rice dry or cooked, frozen veggies vs fresh, etc...

    Sorting - To the right of each column label is a stack of 3 lines, long line on the top with a smaller line on the bottom. Clicking this button allows you to sort the whole tab based on the column selected. In most cases, you should sort the food column alphabetically.

    This tab is also the biggest drawback of this whole spreadsheet. The database requires work from you (the user) to enter in your own database as opposed to the commercial programs that already have a very large database. However, once the database starts to get larger, you will find yourself entering in less items.


    Recipes

    This tab is very similar to a cross between the daily tabs and the food tab with a couple small differences.

    The title of the recipe needs to go into column B. From there, the ingredients of the recipe go into the next line below. After all of the ingredients have been entered, go back to the row that has the label for the recipe. Enter in the how many servings the recipe makes and the units of the recipe. In most cases, you will probably use "Each" for the units, but you may end up using a different measurement.

    In the macros/calories column, you need to do a little more work for the recipe. On the same row as the recipe, overwrite the formula in the cell with the "=sum()" formula (Google it if you don't know how to use this) to add up all of the ingredients used in the recipe. These values are of course for the entirety of the recipe and when the recipe is added to the daily tracker, it will account for how much you have eaten (the recipe has four servings and you only had one, then it will divide the recipe macro amounts by 4).


    Reset Functions

    This is a custom toolbar menu that I created using script to aid in some of the function of this spreadsheet and it lives to the right of the Help menu. The menu can clear all/some of the days back to a blank page, copy one page to another, and to also reset all of the cell formulas for the daily tabs and the recipes tab (may end up adding the weekly overview tab, but I haven't decided yet).

    I have no way to test this, but I think this menu will need to be set up at an individual level and something that I can not do for you. I have provided the code, but you will have to make it turn on. I have instructions in the spreadsheet on how to make it work. If someone decides to take a look at the code and can suggest a better way to make it, I would love to hear about it.

    Instructions/Revisions
    Last tabs to go over. Whew....

    These tabs are straight forward and there is nothing for you to do other than read it. I have written out instruction for the spreadsheet in case you need it. Once you are familiar with the spreadsheet, I would hide/delete these tabs as you should no longer need them. The revisions tab is only to see what changes I have made and when. Also delete this tab once your have made a copy of the spreadsheet for yourself.


    Finishing Thoughts
    Feedback would be greatly appreciated. Also, if you have any suggestions on how to improve it or if you have a request for later revisions, please let me know.

    As far as future plans for this spreadsheet, I was thinking of moving the instructions tab to a custom menu like the Reset Functions menu. Not sure how or if it would affect the performance of the program once the food and recipes database started to get large. Also, depending on the feedback that I get about this spreadsheet, I was thinking of making a separate spreadsheet that was shared and editable by all to act as a food database. It would not be linked to this calorie tracker, but would be a resource where people could submit and also lookup foods to then put into their own database. Let me know!

    One thing to note is that the Google spreadsheet service does have a cap on how large a spreadsheet can be (don't remember the number), so try to be conscious of that. Also, the larger the spreadsheet, the slower it will eventually operate (hence why I suggest deleting the Instructions and Revisions tab).

    Don't forget to check out the attached images. They are definitely worth more than the couple thousand words posted here.




    Thanks,
    Faszer
    Attached Images
    Reply With Quote

  3. #3
    Registered User Dfaszer's Avatar
    Join Date: Feb 2013
    Age: 36
    Posts: 9
    Rep Power: 0
    Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10)
    Dfaszer is offline
    Bump
    Reply With Quote

  4. #4
    Registered User Dfaszer's Avatar
    Join Date: Feb 2013
    Age: 36
    Posts: 9
    Rep Power: 0
    Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10) Dfaszer has a little shameless behaviour in the past. (-10)
    Dfaszer is offline
    I created a new spreadsheet that I think is better than this one. You can find it here: forum.bodybuilding.com/showthread.php?t=176007761
    Reply With Quote

Similar Threads

  1. Replies: 5
    Last Post: 02-02-2013, 12:43 AM
  2. What do you want in a calorie tracker!
    By mackd in forum Nutrition
    Replies: 10
    Last Post: 06-08-2012, 07:20 AM
  3. free online calorie counter spreadsheet
    By redbutton in forum Nutrition
    Replies: 0
    Last Post: 07-25-2010, 12:18 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts