Here at Excel Skin we are always looking for ways to use Microsoft Excel on our Mac computers. With nutrition being such a complex but major part of our lives, tracking that nutrition is very important to maintaining health. In this post we will go through a few steps to create an easy to use spreadsheet that updates calorie count and food intake automatically.
Step 1: The first step is to create your nutrition tracker layout. In the example below, I have used a horizontal layout for the days, and labels for each meal + any snacks. The idea is we will write the types of food next to the meal, and the number of calories directly under it so we can calculate calorie totals.
I used the Select Row and Select Column shortcut to make the gray border between each week.
Step 2: Add a total calorie calculator cell under each day, and next to each week.
We have now designated row 16 to be our daily calorie counter, and next to Week 1 will be the weekly caloric total.
Step 3: Create the calorie calculator formula’s in the designated cells.
While in the total calorie calculator cell, insert the AutoSum formula. Then select the cells you want to sum. In the example above, we want B5, B8, B11, and B14 for Monday’s daily total.
Once you’ve selected the cells, press Enter.
Step 4: Drag this formula across all weeks. To do so, click and drag the square at the bottom right of the cell with the AutoSum formula in it.
This will duplicate your =SUM formula for each day accordingly.
Step 5: Create an AutoSum formula for your weekly total. To do so, execute the same shortcut you used to create the daily calculator next to Week 1:
This will add up all the days so you can see your weekly caloric total.
Step 6: Start tracking!
Once we add some data to the table we have created, calories will start being added automatically. Here is an example of my week 1 for Monday and Tuesday:
As you can see, the daily totals have been calculated, and my weekly total is updated automatically as well. This spreadsheet takes almost all the work out of the calculations, all you have to do is input the calories!
You can copy this Week 1 calorie counter to as many weeks as you want to stay healthy and keep tracking your performance!
As always, please let us know if you have any questions.
Comments will be approved before showing up.