Excel Workbook for Tracking Progress

I really was not comfortable with waiting 3 months to see if my plan to combat diabetes was effective. I also needed some tool to keep me motivated on a daily basis. What I was looking for was a way to predict what my blood numbers were going to be in the future if I stuck with my plan. I also wanted to know ahead of time if I needed to adjust my plan based on an unfavorable prediction.

In my reading I ran across an article on the “5% Club” (http://www.tudiabetes.org/group/the5club). This is a forum for diabetics who have managed to get their HbA1C numbers down below 6%. That sounded exactly like my goal. While reading the 5% Club members forum I stumbled upon a link to an Excel spreadsheet (http://www.loanuniverse.com/Diabetes/) That could be used to predict/estimate  HbA1C numbers. The spreadsheet uses the Nathan formula to perform the HbA1C calculation.

In my reading, I discovered that there are a lot of HbA1C / Blood Glucose equations in use. The Nathan formula is one. It appears that most labs use the DCCT formula. There is also some literature that suggests that the new ADAG formula may give a better indication of average glucose levels based on HbA1C values. So, not knowing which methodology was best I decided to modify the spreadsheet to use them all. I also wanted to use only 1 Excel workbook to do all of my diabetes-related tracking.

The following is a link to my version of the workbook.

Carbs Glucose and A1c Tracking.xlsm

Here is a list of the modifications I made:

  • Added a daily calorie and carb log
  • Added a few more columns on the glucose log for more glucose data entry (calculation accuracy increases as you increase the number of data points)
  • Expanded the glucose log from 120 days to 360 ( HbA1C values are still base on 120 days of data. Each month (30-day period) the HbA1C calculation uses the previous 120 days of data.)
  • Added both the DCCT & ADAG formulae calculations in addition to the Nathan formula calculation
  • Added the ability to change the glucose concentration units (mmole/mL = mg.dL / 18.05)
  • Added the ability to select between Whole Blood Glucose or Plasma Glucose (PG = 1.11 * WBG)
  • Added the ability to set high alarm values for calories, carbs, glucose concentration and HbA1C values (cell background and font color change if the alarm value is exceeded)
  • Daily calorie and carb numbers are imported onto the Glucose / HbA1c sheet to facilitate side-by-side comparison of food intake vs glucose levels
  • Added a worksheet for recording calorie and carb values for food one might routinely eat.


It is not required that an data entry be made in every cell of the daily glucose log. If a cell is left empty it is not used in the HbA1c calculation. The calculation does not use a zero for a blank cell. Blank cells are ignored.This workbook has been updated as of 01/21/2017. The update was done to correct a couple of minor errors in the calculation using the Nathan and DTTC equations.

If you have any questions or comments about this workbook you can contact me at 


24 thoughts on “Excel Workbook for Tracking Progress

  1. Sweet someone after my own Excel ideals Looks good wondering did you use any excel pivot tables as they do marvelous calculations.

    Thought about this and started my excel sheet then thought the same thoughts. This is all new to me and it helps to see someone take the time to do this and really think about it outside the box, (tally sheets are ok) but i am a data enthusiast :>)
    Sweet thank you

  2. EXCELent spreadsheet, extremely comprehensive. I was impressed with your use formulas. I also created a spreadsheet about a year ago, maybe more, so I can appreciate the time LOTS of time, you put into it (Not to mention hair pulling) I did, I’m sorry to say “Borrow” three of your formulas The Nathan DTTC & ADAG. I did make mention in my program that I got them from you, hope you forgive me. I will send you a copy of the spreadsheet if you care to peek at it.

    Thanks for a terrific spreadsheet

    • All “blue” cells are for user input. Click on cell E8 on the glucose log and then click on the arrow icon to select your units from the drop-down list.

  3. Wonder if it is possible to use my fitness pal and export to excel. Would make the food logging easier.

    Please respond to my email

    • I am sure that the MFP data can be exported to Excel. However, the entire workbook would have to be rewritten to conform to how the information is imported from MFP. That would be no simple task. Since I do not use MFP, I am not going to invest that amount of effort. But, if you want to give it a shot, go for it. While many of the workbook cells are protected, there is no password on that protection.

  4. You have done a great job with your worksheet. I have been using it for over two years, my Doctors love the ease of tracking my progress. God Bless you for all your work.

    Steve Davis

  5. I love this spreadsheet because I have Type II diabetes and need this kind of tracking. Unfortunately, I’m not a spreadsheet geek. I tried to put the current date in the “Date” colum and got this #VALUE! for the rest of dates. Can anybody help me out? Thanks in advance.

  6. Great Work, really appreciate this work , have been using this for almost 4 years and it has been great .

    May God bless you abundantly.

  7. Thanks for this comprehensive spreadsheet! I’ve just discovered it tonight. I had been using the older one that you started from. My last A1c of 6.7 disappointed me so I have returned to read about low carb eating, I added a fourth 500 mg Metformin and will track carefully for the next three months. My primary care physician is very satisfied with numbers below 7.0 as I approach 72 with Type II diabetes. I’m looking for something below 6.0.

    • Congradulations on an A1c below 7. I am convinced that the only way to get below 6 is a high-fiber, low-carb diet with some exercise. Anything resembling white bread is a killer.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>