Problems With Setting Up Graph with Auto Action & Design


New Member
Jul 27, 2007

This is my first post here, so I hope I've done everything correctly in accordance with the rules :) My downfall is my "descriptive subject line" because I ran out of room! In any case, here goes:

I'm using Excel 2007 Ultimate Edition under Windows Vista Home Premium. I'm not a "power user" by any stretch of the imagination - my uses for Excel are very limited. However, I'd really like to use it for a new application and I can't figure out how to get out of it a few of things, which I'm sure are there - I just can't figure out where to find them.

I've tried looking these up though Excel's own help files, checked out the Microsoft Knowledge Base and searched here as well. My main problem with each search place is the same as I mentioned above - finding a way to search for what I'm looking for by only using a few short words has been pretty much impossible. So, here I am.

I'll tell you up front why I'm doing this graph just so you don't think I'm trying to scam free information when I could be a business and pay for expensive courses to learn how to do all this stuff. I'm a disabled student who has recently been diagnosed with diabetes and want to create a simple way to keep track of my blood sugar levels every day. To go along with the actual spreadsheet (which was easy to create except for a couple of things) I'd like a graph that I could give to my doctor each visit rather than him thumbing through a notebook (which is what he expected me to provide him). Considering he knows how much of the day I spend on the computer (I study externally because of my disability) then he could well expect this more modern-day approach from me anyway :)

I went to the Microsoft site and got myself a basic Blood Glucose Chart that someone else kindly put together. I've changed it more than enough now to make it my own, so thank you to the wonderful person who created it. It had a chart included but it didn't do any of the things that I wanted it to do so that's why I am here.

The first thing is this - my Excel spreadsheet is going to be only three columns wide with date, time and then the varied test results in the last column. The number of entries per day will vary and I'm fine with entering the current time and date in the first two columns (I have at least that bit worked out!). The last column will change every time.

I've also managed to work out a colour coded system for the last column of data so that I can easily see if it's too high or too low or just right. I've never really done anything much with Excel before at all except for a budget and the like so this is all new stuff for me! I'm pleased that I got that much worked out!

The spreadsheet data entry area has been set up so that the most recent date is at the top and the oldest down the bottom. This is so I will always have the most recent entries on view at any one time and the older ones further down the page. What I want to be able to do is that once I enter the data on the top row, I want it to automatically create a new blank row above that and make all the current data go one row down. I'm sure I could create some kind of macro to do this, but as I've never created a macro in my life and, even after reading up on it in various places (Excel help section, etc as above), it's all still a bit beyond me I think. So does anyone have any suggestions for how this might work? What sort of macro might I need or is that what I need at all? Also, by creating the line new at the top every time will that include the formatting of the lower line (ie the colours for certain entries)? At the moment it does but will the automation take that away? By creating the new lines manually I find that I have to leave two blank lines at the top or otherwise when I create the new row I create the "header" row properties and not the normal row ones (the header being bold and coloured and informative not blank and noraml-). I'm not sure why it happens that way, it just does! This is the end of only the first question!

:: phew ::

Next question ... The line graphs (and it's a line graph that I'm almost certain I want) that I can create very easily using the Insert --> Line graph button are fantastic, but I'm curious as to why they can't be made permanent? I know they can be created as a template and then it's only one click to get to exactly as I want it to be (which I've done), but I'd like things to be done automatically (had you figured this out yet?). I'd really like a permanent graph (maybe on a separate sheet similar to the one that came with the one from Microsoft?) but that will also automatically update as new data is added. Also, I want to be able to target certain data at a time - say a particular week. At other times I want to see all the data that's in the sheet. Or year to date. You get the idea.

I've done a little work on the custom template graph that I created, but I'm gathering that the more permanent graph would be put together differently - well, I'm almost sure? There are, however, two particular things that I'd like to do with it. The number of results throughout a day will change - sometimes one or two results, sometimes five or six (depending on what my results are). When I create the graph, however, I want the width of a section for each day to be the same. So I guess what I need is for the axis at the bottom to be over hours throughout a day rather than just one point on the graph for only each and every test. The latter would result in a graph that would give a false sense of trends - if my blood sugar was high for one day that might be okay. But if I took five tests that day and they are all high compared to one test on a low sugar day then it's going to look like I had a lot of highs and only one low. By comparison in my perfect graph all the highs will still look like only one day and so the one test on one day that was low will show up as being comparable - I had one day with high tests and I had one day with a low test.

So I need to carefully set up that axis on both x and y (I need the "x" to reflect everything from, say, 0 to about 16 - these are reasonable limits of the glucose tests we use in Australia although it does go higher - which I know is different from the results in, say, the USA - and then the "y" date range across the bottom to be time over individual days - as above). This isn't the only thing that I need on my graph, but it's a good start anyway! And if I can learn the big stuff I might be able to work the little stuff out on my own. :)

Okay, two final questions and they're both frippery but frippery I'd still like ... As I said before, I've colour coded the results so that if I go above, below or between certain numbers then it will fill in the cell with various colours. I tried to mix this with bars (I've just had a look for them and can't find the exact name now, but the bars that vary in length over the width of a cell to reflect the result) so that the coloured bars I have for high, middle and low would be longer or shorter to give me a view of these results immediately but I couldn't find a way to do it. Any ideas on this one? Then I'd like to have the dots in the graph reflect the colour that the fill in of the cell is that it comes from. So high results would not only be high on the chart but red as well. Or perhaps that the fill behind the bars above a certain figure would all be red? So that it was easily seen that every time the chart went into that area it would be bad. And being blood sugar results I have red then yellow then green then yellow then red again because being low is as bad as being high and there are mediocre results as well (hence the yellow). Is any/all of that possible? It's nothing more than just another clear showing of where those results lay in the table but my Doctor needs this, trust me!

Thank you all in advance for reading through all of this and I hope someone or a few people out there are able to help me. I will be most grateful, although what I can offer is return is probably not very much. But I will be happy to offer this whole spreadsheet/workbook to any other diabetics (or those who know diabetics) out there who are interested in it and would be happy for someone to serve it on their sites for that purpose.

Much appreciation.

Kind regards


Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back