My Diabetes Spreadsheet

If you’ve no interest in diabetes, please skip this post. I’m aiming it squarely at anybody who measures their blood sugar.

These last few weeks, I have been working on a Diabetes spreadsheet. It is aimed at storing the values, when you use a glucometer to prick your finger. Well, okay, I have had a Diabetes spreadsheet for years, but just using UK units. After all, that was all I needed. This last few weeks, I made it international. That only took about a day, the rest of the time I have just been testing it.

The resulting spreadsheet, I share with you today. It is a spreadsheet containins macros (tiny snippets of programming) so I had to save it as a .XLSM file (m = macro), instead of the usual .XLSX extension. Furthermore, WordPress will not allow me to upload a .XLSM file directly, so I have had to place it inside an archive .ZIP file. To run the macros in the spreadsheet, you will have to allow Excel itself to run macros – there are instructions for this on the web, or contact me privately, and I will try to help.

What’s in the box?

When you open the spreadsheet, you will see two coloured backgrounds, white and grey. White are the cells in which you are expected to enter data, grey are the cells which are calculated for you. The cells you are expected to enter are just the date, time and the value of your reading. I’ve added ten dummy rows to give you an example to start with.

The very first cell is a dropdown to allow you to specify what units you want to use. There are two units used worldwide (mmol/l and mg/dl), and I support them both, but only one at a time. Whichever unit you choose, this is referred to as your primary unit. The other unit, the spreadsheet refers to as the secondary unit. I realise that the secondary unit might not be of interest to you, but I have written it in so that the spreadsheet can use either unit.

So, the values you enter. Column A is the date, column B is the time. I have formatted these columns to how I like them, but you can change this formatting if you wish.

Column C is used by the spreadsheet to store the datetime, which it will calculate and will use in its own calculations. Again, you can change the format if you wish.

Column D is the value of the measurement, in whatever unit you have chosen. Again, change the format if you wish.

At this point, please note that the spreadsheet expects the values to be added sequentially. In other words, if you took a reading on 1st January, 2021, it goes before the reading you took on 1st February, 2021, which in turn goes before the 1oth February, 2021 reading. And so on. The reason for this will become clear in a moment.

Now, here is why the spreadsheet is useful, beyond just recording the readings. What I find is that the number in Column D can be all over the place. Literally, it can be very low in the morning and very high in the evening, if I’m not careful. Because, they are spot values. So, on their own, the numbers don’t really give a clear idea of how well I’m controlling my sugar. Here’s an example:

See what I mean? It can be difficult to see what is going on. I find that it helps to apply statistics to the numbers, to talk about my average sugar rather than individual values. And the spreadsheet calculates these average values for you, that’s why it has macros in it. So, the chart above becomes:

which I think is clearer. In fact, the spreadsheet calculates your average sugar over the last fifty days. It also calculates a value called the standard deviation, which gives an idea how much your sugar varies each time. Note that the standard deviation calculation needs at least two readings in order to be calculated, as you will see if you look in the very first row of example data.

So when you ask it to calculate what these statistics were on a certain date, the thing the spreadsheet does is to find all the readings which are within 50 days, so as to include them in its calculation. That’s why the data has to be entered sequentially.

Therefore, Column E is the calculation, if I am going back up to 50 days, what is the earliest cell I count? Similarly, Column F tells you the total number of rows to be counted. In fact, as long as they are within 50 days, it doesn’t matter whether there are 1 or 1000 results. Columns E and F will be calculated for you.

Columns F and H contain the two interesting, statistical values, the Average and Standard Deviation, in your chosen unit. Again, I have chosen a format which suits me, you can change it if you wish. I also added a couple of graphs to my spreadsheet (5 minutes) but I shall leave that to you to do, if you wish. The graphs are pretty, but the numbers are more important.

Now, you remember we talked about the primary and secondary units? We have all the values in your primary unit, but Columns I, J and K show them in the secondary unit. Again, these fields are calculated and again, you may alter the format, or even hide, these columns, if you wish.

My last column, Column L, is just a free-text field I use to store the name of my monitor. This is just because, over the years, I have had several. Some will last years, others days. Build quality is usually not very impressive. Storing the monitor against its values might give me a clue if my values were consistently high or low, for example. That has never happened yet, but if it does… This column is not used in any calculations, you can delete it if you wish.

How to make a calculation

When you enter a date and a time, the datetime (Column C) is calculated automatically. Thereafter, though, the spreadsheet only performs a calculation when you hit the Calculate Statistics button at the top of the spreassheet. This is entirely deliberate, to allow you to type in values in peace, without the screen updating all around you.

When you hit Calculate Statistics, the first thing you see is a small dialog box which asks you where you want to start, and where you want to finish. This fits in with how I work. Every week or so, I walk the monitor over to the computer and type in each number. At the end, I only want to calculate the numbers for the last week, not every number. So I only need to calculate a dozen or so rows statistics each time. And, as long as you are just adding numbers, none of the previous numbers will change.

If you’re inserting numbers into the middle of the list, though, I recommend recalculating everything again. But the spreadsheet can do this, just so long as those dates and times are in the correct order.

When the macro has finished, it will pop up a little dialog box just to say as much. I have found on my machine that a calculation for a thousand rows might take 20s or so, but it will tell you when it is finished. Your computer might be faster or slower.

Lastly, the observant people among you will have notices that the spreadsheet has two tabs. Everything I talked about today refers to the Data tab. I’ll briefly talk about the Calculations tab in a day or two [update: this post is now written and may be found here]. Feedback, of course, is welcome.

Author: Mister Bump UK

Designed/developed large IT systems, interrupted by a stroke in 2016, aged 48. Now mix development of health-related software with voluntary work and writing. Married, with an estranged daughter.

11 thoughts on “My Diabetes Spreadsheet”

  1. I find this type of spreadsheet extremely easy to use and to track my sugars. it could be easily modified to track other health concerns as well..such as heart rates, BP, weights etc. I did download and discovered that when opened it is a “read only” file on my end. this is more than likely because I do not have excell as such, but an “open office” program which allows me to open excell but not really use them. I am asking permission to copy this into my open office new file and convert for my own use. Thanks for doing all the hard work!

    Liked by 1 person

  2. I have never made a spread sheet of my diabetes. It was first diagnosed when I was about 40 years old (diabetes type 2) but I just took tablets and am still taking them today, the same tablets, and I am now 73 years old. My No. 2 son also has diabetes type 2, and was injecting at the beginning, he was then about 40 years old, but when he got it under control moved to tablets, which he is still taking. To be quite honest, I do not even bother to check my blood sugar daily. I just trust my routine and feelings. I am also injecting (the big mix injection) every second day for my MS, so I am not really sure if I still have room on my body for more injections. Any how I read your information with interest and am keeping an eye on developments.

    Liked by 1 person

    1. I hardly used to measure, but am anal about it now, just because my only known problems were diabetes and a high BP. I kept myself ultra-fit but still went on to have a stroke, so I figure one of them probably caused it. So I have recorded my sugar since I was well enough to do so.

      Liked by 1 person

  3. This is fabulous! Since the virus thing became so intense, I’ve given up checking my sugar. It’s inevitably high because stress, for me, drives the numbers through the roof. I haven’t changed my overall regime (much) except maybe to eat less. I will use this wonderful tool you’ve shared though, once I feel less stressed out and can rely on a more accurate representation of where I am. Thank you so very much for sharing this with everyone! 😀

    Liked by 1 person

    1. when you say “stress”, do you think you just eat worse when you’re stresed? I’ve learned to avoid certain foods, like bread and potatoes, which are very bad for my numbers. I’m quite anal about it now because I reckon diabetes was a prime candidate for causing the stroke. I know it has messed with my vision.


    1. It is important just really for the reason of understanding how our bodies react to different foods. I think that link is key. People are more likely to avoid/limit bad food if they see what it does, rather than just being told what it does. And, “bad” foods are not necessarily what we might think!

      Liked by 1 person

      1. Exactly! That is so true. Besides diabetic, we’re all bombarded with the industry telling us all kind of stuff. We need to ‘measure’ it in our own bodies because every-body is a bit different.

        Liked by 1 person

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: