Hi All,
A little over a year ago, I created a calorie tracker spreadsheet and posted it on the forums here (per BB rules, my post count is not high enough to post hyperlinks, so unfortunatly, you will need to copy/paste any links that I post): "forum.bodybuilding.com/showthread.php?t=173586151&highlight=tracker+sprea dsheet"
Since then, I decided I didn’t really like it that much, so I created a new one. It has the same functionality as the previous design, but the layout is a little more streamlined/convient in my opinion and I added a few new features to it. There are screenshots of the program attached.
Here is the link for the new Google spreadsheet. This is a view only link and in order for you to make edits, you will have to download it or make a copy of it to your personal drive.
"docs.google.com/spreadsheets/d/15tvoPYlg9orIYevgjISGDx734HeviadawJyGEp9bF9g/edit#gid=559479310"
I added a little instructions sheet the goes over the functionality of the spreadsheet, but I will also explain it here.
Body Stats
The first sheet is the Body Stats page. This sheet is simply where you input your body stats and allows you to keep a running log of your progress. By entering in your height, weight, waist, neck, and hips (hips for females only), it will calculate your body fat based on the Navy body fat calculator. It will then update the bar graph for your weight and body fat just as a little visual progress report. I have noticed that when the graph is updating, it takes a couple seconds longer to load than normal. I have also noticed that sometimes when the spreadsheet is opened, the graph will be positioned over the text. You will need to move it out of the way (the normal location is to the right).
I also set it up so that the fields that should have data entered in are highlighted blue. These will auto update as data is entered. Unfortunately for our metric friends, this is setup only for english units. It wouldn’t be hard to convert it to metric (remove all of the “*2.54” in the equation stored in cell G5), but added more complexity to the equation to add both measurement systems than I wanted to deal with. If the formula is changed, the units need to be measured in kg and cm.
One thing to note, is that when you are entering in your stats, you do not need to enter in all of the measurements every time. The formulas will use the last entered values for waist, neck, and hips. This is so that you can weigh yourself a couple times a week (or more or less), but then take your measurements every couple of weeks and still get a somewhat accurate value on your progress.
Food Goals
This sheet is where you put in the goals that you are shooting for, food wise. Like the previous sheet, the editable cells are highlighted blue.
At the top of the sheet, enter in your activity level and what your goal (bulk, cut, or maintain). Based on the your body stats from the previous page, this will tell you your TDEE using the Katch-McArdle equation. This number automatically gets updated to your last entry in the body stats log so you constantly get an accurate* number based on your current body (*as accurate as the equation can give you anyways).
At the top right, this is where you enter in your macro goal. Should be simple enough…
For the bulk of this sheet, you have an overview of how your food planning/consumption is going. Each day has its own little table as well as a global weekly table that sums up the 7 weekly tables. These table are also what lets you micromanage your daily planning, to a degree anyways. For those interested in macro cycling, calorie cycle, reverse dieting, etc. this is where you can make the changes. At each table (except for the weekly overview) you can change the macro percentages to match what your current goal is (this removes the little formula in the cells and will need to be reapplied if you want to go back to the original form). At the right side of each table, there is a small box labeled “calorie modifier’. This box is where you can put in a modifier value to your calories to adjust the daily calorie goal. This is useful for those who are doing a reverse diet or maybe someone who wants to be just under maintenance mode.
At the label for “Weekly Summary,” there is a dropdown that allows you to switch between the weekly summary and the current days summary. For instance, if today is Wednesday, the formula knows what day it is and will display summary for Wednesday (if you keep the spreadsheet open at the turn of the day, you may need to refresh the page for the formula to display the next days data). This was mainly a laziness feature as I was tired of scrolling to the bottom of the sheet…
Each table is set up so that you can rename each of the boxes to match your schedule. If your week starts on Sunday instead of Monday, you can re-label the boxes how you like. This is not automatic, so you will need to re-label each box individually. This also affects the tables on the next sheet, “Planner”.
Lastly for this sheet, each box has conditional formatting on the remainder cells that switch from green, yellow, and red to give a visual cue to how close you are to your goal.
Planner
This sheet is where you will probably be spending most of your time with this spreadsheet. This is where you put in the food that you are planning to consume/have consumed.
-The first column (B) is simply a label for each meal. Use it if it helps you, or ignore it if it doesn’t.
-The second column (C) is where you put in your food. You can use the dropdown box and scroll thru the list (not recommended as the list is very long), or you can start typing a keyword to find your selection (when typing, the punctuation is critical and so, until you learn how the database is laid out, you will only be able to use one keyword. Ex. “Cheese, Cheddar” works, “Cheese Cheddar” does not. Notice the comma in the first example). The drop down list contains both recipe items from the Recipe sheet as well as food items from the FoodList sheet.
-Column D and E is the serving size and the unit of measure for the food that you selected in column C. The units selected in column E needs to be of the same family (mass vs volume) of what is in the food database. As long as the units are in the same family as the food database, the equations will automatically convert from one unit to another (pounds to grams, Tbsp to tsp).
-Column F shows how the food item is measured in the food database. This just makes it easier to see how the food was initially measured so that you can measure it the same way. For the items that have a blank in this column, it can be assumed that the measurement is “each”.
-Columns G thru K are auto-populated and calculate the calories/macros of the food that you selected based on the quantity that you entered. Column K is for comments and are automatically pulled from the food database and recipe sheets. Useful for adding notes (measured dry vs wet) or adding a website link to a recipe, among others.
The rows that have “Remainder” in column B are linked to the Food Goals sheet and are automatically updated with any changes that happen on the previous sheet. These cells also have the same green, yellow, and red conditional formatting as the previous sheet.
If you need to add additional rows to a day, this needs to be done between the rows with the labels “Food” and “Totals”. If done correctly, the formulas used will auto-populate for the additional rows. Be aware that adding rows to the first table, currently labeled “Monday”, may affect the formatting of the “Recipe Suggestion” table that is located to the right. Adding additional rows below row 12 would be safe and not affect the adjacent table.
The last section on this sheet is the “Recipe Suggestion”, and in my opinion, the coolest feature of this workbook. If you are having trouble thinking of a recipe to make/plan for, this table will help. By selecting the day that you want to plan for, what type of food you want, and how you want the table to search for, it will generate a list of recipes (from your recipe database) that fit your criteria.
The day selection should be obvious. Just select the day of the week that you are planning for. -The next box, “meal search”, select what type of meal you want (breakfast, lunch, dinner, snack, etc.). This list is based on how you classified your recipe on the recipe sheet.
-The “filter by” dropdown is how you want your search to fit within the day that you selected. You can have the search fit within all of your macros remaining or just one of your macros or even custom macros based on the next couple of boxes.
-The rows labeled “Custom (less than)” and “Custom (greater than)” allow you to enter in custom values that you would like to search for. This is helpful when you want something, for example, that has a minimum of 10g of protein, but less than 200 calories. Any box that is empty in this area is seen as a zero, so keep that in mind when making changes.
-On the recipes sheet, there is a section to rate (0-5) your recipes. This table also utilizes that same rating and allows you to filter selections based on the rating you gave. This feature is active for all searches. Like the custom fields, a blank rating on the recipe tab will be seen as a zero and as such, any filtering of the rating greater than 1+ will not be seen if left blank on the recipe sheet.
-Lastly, the list that is generated can be sorted using the blue box directly above the table labeled “Sort by”. Simply select how you want the list to be sorted.
Continued in next post.
A little over a year ago, I created a calorie tracker spreadsheet and posted it on the forums here (per BB rules, my post count is not high enough to post hyperlinks, so unfortunatly, you will need to copy/paste any links that I post): "forum.bodybuilding.com/showthread.php?t=173586151&highlight=tracker+sprea dsheet"
Since then, I decided I didn’t really like it that much, so I created a new one. It has the same functionality as the previous design, but the layout is a little more streamlined/convient in my opinion and I added a few new features to it. There are screenshots of the program attached.
Here is the link for the new Google spreadsheet. This is a view only link and in order for you to make edits, you will have to download it or make a copy of it to your personal drive.
"docs.google.com/spreadsheets/d/15tvoPYlg9orIYevgjISGDx734HeviadawJyGEp9bF9g/edit#gid=559479310"
I added a little instructions sheet the goes over the functionality of the spreadsheet, but I will also explain it here.
Body Stats
The first sheet is the Body Stats page. This sheet is simply where you input your body stats and allows you to keep a running log of your progress. By entering in your height, weight, waist, neck, and hips (hips for females only), it will calculate your body fat based on the Navy body fat calculator. It will then update the bar graph for your weight and body fat just as a little visual progress report. I have noticed that when the graph is updating, it takes a couple seconds longer to load than normal. I have also noticed that sometimes when the spreadsheet is opened, the graph will be positioned over the text. You will need to move it out of the way (the normal location is to the right).
I also set it up so that the fields that should have data entered in are highlighted blue. These will auto update as data is entered. Unfortunately for our metric friends, this is setup only for english units. It wouldn’t be hard to convert it to metric (remove all of the “*2.54” in the equation stored in cell G5), but added more complexity to the equation to add both measurement systems than I wanted to deal with. If the formula is changed, the units need to be measured in kg and cm.
One thing to note, is that when you are entering in your stats, you do not need to enter in all of the measurements every time. The formulas will use the last entered values for waist, neck, and hips. This is so that you can weigh yourself a couple times a week (or more or less), but then take your measurements every couple of weeks and still get a somewhat accurate value on your progress.
Food Goals
This sheet is where you put in the goals that you are shooting for, food wise. Like the previous sheet, the editable cells are highlighted blue.
At the top of the sheet, enter in your activity level and what your goal (bulk, cut, or maintain). Based on the your body stats from the previous page, this will tell you your TDEE using the Katch-McArdle equation. This number automatically gets updated to your last entry in the body stats log so you constantly get an accurate* number based on your current body (*as accurate as the equation can give you anyways).
At the top right, this is where you enter in your macro goal. Should be simple enough…
For the bulk of this sheet, you have an overview of how your food planning/consumption is going. Each day has its own little table as well as a global weekly table that sums up the 7 weekly tables. These table are also what lets you micromanage your daily planning, to a degree anyways. For those interested in macro cycling, calorie cycle, reverse dieting, etc. this is where you can make the changes. At each table (except for the weekly overview) you can change the macro percentages to match what your current goal is (this removes the little formula in the cells and will need to be reapplied if you want to go back to the original form). At the right side of each table, there is a small box labeled “calorie modifier’. This box is where you can put in a modifier value to your calories to adjust the daily calorie goal. This is useful for those who are doing a reverse diet or maybe someone who wants to be just under maintenance mode.
At the label for “Weekly Summary,” there is a dropdown that allows you to switch between the weekly summary and the current days summary. For instance, if today is Wednesday, the formula knows what day it is and will display summary for Wednesday (if you keep the spreadsheet open at the turn of the day, you may need to refresh the page for the formula to display the next days data). This was mainly a laziness feature as I was tired of scrolling to the bottom of the sheet…
Each table is set up so that you can rename each of the boxes to match your schedule. If your week starts on Sunday instead of Monday, you can re-label the boxes how you like. This is not automatic, so you will need to re-label each box individually. This also affects the tables on the next sheet, “Planner”.
Lastly for this sheet, each box has conditional formatting on the remainder cells that switch from green, yellow, and red to give a visual cue to how close you are to your goal.
Planner
This sheet is where you will probably be spending most of your time with this spreadsheet. This is where you put in the food that you are planning to consume/have consumed.
-The first column (B) is simply a label for each meal. Use it if it helps you, or ignore it if it doesn’t.
-The second column (C) is where you put in your food. You can use the dropdown box and scroll thru the list (not recommended as the list is very long), or you can start typing a keyword to find your selection (when typing, the punctuation is critical and so, until you learn how the database is laid out, you will only be able to use one keyword. Ex. “Cheese, Cheddar” works, “Cheese Cheddar” does not. Notice the comma in the first example). The drop down list contains both recipe items from the Recipe sheet as well as food items from the FoodList sheet.
-Column D and E is the serving size and the unit of measure for the food that you selected in column C. The units selected in column E needs to be of the same family (mass vs volume) of what is in the food database. As long as the units are in the same family as the food database, the equations will automatically convert from one unit to another (pounds to grams, Tbsp to tsp).
-Column F shows how the food item is measured in the food database. This just makes it easier to see how the food was initially measured so that you can measure it the same way. For the items that have a blank in this column, it can be assumed that the measurement is “each”.
-Columns G thru K are auto-populated and calculate the calories/macros of the food that you selected based on the quantity that you entered. Column K is for comments and are automatically pulled from the food database and recipe sheets. Useful for adding notes (measured dry vs wet) or adding a website link to a recipe, among others.
The rows that have “Remainder” in column B are linked to the Food Goals sheet and are automatically updated with any changes that happen on the previous sheet. These cells also have the same green, yellow, and red conditional formatting as the previous sheet.
If you need to add additional rows to a day, this needs to be done between the rows with the labels “Food” and “Totals”. If done correctly, the formulas used will auto-populate for the additional rows. Be aware that adding rows to the first table, currently labeled “Monday”, may affect the formatting of the “Recipe Suggestion” table that is located to the right. Adding additional rows below row 12 would be safe and not affect the adjacent table.
The last section on this sheet is the “Recipe Suggestion”, and in my opinion, the coolest feature of this workbook. If you are having trouble thinking of a recipe to make/plan for, this table will help. By selecting the day that you want to plan for, what type of food you want, and how you want the table to search for, it will generate a list of recipes (from your recipe database) that fit your criteria.
The day selection should be obvious. Just select the day of the week that you are planning for. -The next box, “meal search”, select what type of meal you want (breakfast, lunch, dinner, snack, etc.). This list is based on how you classified your recipe on the recipe sheet.
-The “filter by” dropdown is how you want your search to fit within the day that you selected. You can have the search fit within all of your macros remaining or just one of your macros or even custom macros based on the next couple of boxes.
-The rows labeled “Custom (less than)” and “Custom (greater than)” allow you to enter in custom values that you would like to search for. This is helpful when you want something, for example, that has a minimum of 10g of protein, but less than 200 calories. Any box that is empty in this area is seen as a zero, so keep that in mind when making changes.
-On the recipes sheet, there is a section to rate (0-5) your recipes. This table also utilizes that same rating and allows you to filter selections based on the rating you gave. This feature is active for all searches. Like the custom fields, a blank rating on the recipe tab will be seen as a zero and as such, any filtering of the rating greater than 1+ will not be seen if left blank on the recipe sheet.
-Lastly, the list that is generated can be sorted using the blue box directly above the table labeled “Sort by”. Simply select how you want the list to be sorted.
Continued in next post.
from Bodybuilding.com Forums - Nutrition https://ift.tt/2O8TlSB
0 comments:
Post a Comment