How to keep a Food Diary in Microsoft Excel

If you are on a weight loss program (or a fat release program to be more accurate), you will probably be told that you should keep a food diary. When you write down everything you eat, and the calorie cost, you tend to find it harder to cheat yourself.

I tend to avoid being ‘should’ upon, but I must admit, a Food Diary is a good way to stay on track. You can certainly keep a food diary on a piece of paper or small notebook. I often start out that way, but if you are somewhat familiar with Microsoft Excel, you’ll find using a spreadsheet is easy and accurate.

Open a new spreadsheet in Microsoft Excel .

1. ROW 1: In the cells in the first row write your column headings:
Date (in cell A1)    Food (cell B1)    Calories (cell C1)
In cell D1 put your starting weight (be honest… the worse it is now, the better the loss)
We’ll return to this row later.

2. ROW 3 (skip row 2)
In Column A put today’s date
Tap Alt then I (insert) N (name) D (define) Startdt and tap OK. That way you’ll know your starting date.
In Column B put today’s weight (yes the same as in D1 today, but not tomorrow!)

3. ROW 4 (through 13 or so)
in Column B put the food you had for breakfast (yes, eat breakfast!) [today I wrote ‘oatmeal + spec k’]
in Column C put the calorie amount of that food [my oatmeal + cereal amount is 220]

Continue entering the food and the calorie amounts on each separate row.

Skip a row. (let’s call it row 14)

4. TOTAL
In the next row (let’s say row 15) after you’ve entered all the food you’ve eaten today, place your cursor in the cell in column C and click to be in that cell (C15). Use the keyboard combination Alt and = (did you know that’s the shortcut for totaling a row or column? you do now!) Press the Enter key to accept the suggested column of calories to be summed. That’s your day’s total (so far).

5. ADD LINES If you eat more and need more space insert a row or two above the skip line. To do that you can click into one of the cells in the skipped line (which was row 14). In sequence press Alt then I (that’s an eye not an el) then R, or use the snazzy toolbar buttons I taught in another article (http://www.bukisa.com/articles/44646_how-to-add-microsoft-excel-toolbar-buttons-insert-and-delete-rows-and-columns). You can then enter food and calories above the total and have them be counted.

6. TO GO? If you have a specific calorie count that you’re aiming for, whether it’s 1200 or 1500 or 2000, you can create a formula to tell you how many calories you have left for the day. In the cell next to the cell in which you made your totals (D15 if you haven’t added any new rows) type this:
=1500-C15 (or =2000-C15, or =1200-C15) assuming that C15 is the cell in which your totals are.

That’s today.

7. TOMORROW
Don’t make a new spreadsheet every day. Instead just add about 14 or 15 lines above today’s (now yesterday’s) date. To do that, click in cell A3 and start inserting lines in the way you like to do it best. That way your current date is always on top and easy to find, but you can refer to prior day’s meals and weight if you want to.
Enter the new date in A3 and your new weight in B3.

8. Weight loss calculator
You can create a couple of formulas to calculate how much you’ve lost, and how long it took you.
Click in cell D1 (where you put your starting weight). Press Alt, I (for insert) N (for name) D (for Define) and call this cell ‘startwt’ then press OK.
Click in cell E1 and type “Lost so far”
Click in cell F1 and type the formula  =startwt-B3 (which is where today’s weight will be) That’s your loss total.
click in cell G1 and type ‘pounds in ’
click in cell H1 and type =Today()-startdt
if that looks like a date, reformat it: Alt F (format) C (cells) N (number). Change it to zero decimal places. OK
In Cell I1 type ‘days’
Want to know how many weeks? the formula is =H1/7
Want to know how much weight you’ve lost per week? the formula is =F1/(H1/7) parens are important here.

You will have to change the formula in F1 each day after you’ve added rows and put in your new weight, because it will remember the last cell address and update it with new rows. So just change it back to B3 every day. (If anyone has a solution to this, please msg me or comment and I’ll be happy to fix it here and in my own food diary spreadsheet.)

This seems a lot harder than it really is. You don’t need the bells and whistles of step 8 if you’re not comfortable with Excel. Just keep the food diary and watch your success.

Tips
If you've got a lot to lose, you might want to start a new diary each month. If you use a new worksheet in the same workbook, you don’t have to rename startdt and startwt and you can continue to use those defined names in formulae.