It was quite some time since I wrote a How-to-post. Here’s an Excel-thing that I managed to solve today that’s been bothering me for a long time.
Here’s the scenario:
- We have plenty of data point, one per day, counting something (really the number of patients per day but it can be anything). This is displayed in a diagram like the one above.
- After 2 months this starts to get out of hand looking at and really we’re only interested in the last 30 days
- Sometimes though it could be fun to see more data in one view
Basically we want the diagram to dynamically show the last 30 days (or any other number of days we fancy). Like a 30-day window backwards.
This post describes how to do that.
DISCLAIMER I have, for some stupid reason, a Swedish Excel installed. I have translated the formulas but some other names (like menus etc) might be off. Please bear with me.
First create a new workbook with a sheet named
NoPatients in it. In my real-life case we have quite a lot of data points, but to keep it simple I narrow it down to two.
My Excel sheet can be be downloaded here.
Here’s some example data for you if you want to build it yourself:
|Date||Patients per day|
Also, imagine that this now contains the data for the last 2 years and you’ll see the use of this entire exercise better.
Define the “window”
To make this really dynamic let’s be fancy from the outset and define the number of days our window should be in another cell, in cell
F2 for example:
|Latest days to show|
This will be cool to play with in a little while, I promise.
In order to get this working will not use the data above directly but rather create a named range that will off set the data for us.
A Name, or Named range is just a variable referring to a cell or a formula that calculates a value. It’s global for the entire workbook, which will become important later.
Defining a named range is pretty easy, depending on your version of Excel:
- Click “Insert…”” and then “Names”
- Now click “Define…” and a dialog that looks something like the picture above will open.
- For name write
RecentDatesand then add this formula (I’ll explain it soon) in the
- Click Add
- While we’re at it let’s define
RecentValueswith this formula:
- Click Add and OK to close the Name-dialog box. We don’t have to go back there.
Explaining the formulas
The main function we’re using in the formula is the
OFFSET function. [Quite simply the function](:
returns range of cells that is a specified number of rows and columns from an initial specified range. The user can specify the size of the returned cell range.
OFFSET function takes up to 5 parameters:
- The initial cell to start the offset from. The start point, if you like. In our case this is
'NoPatients'!$A$1, the first cell in that sheet.
- The number of rows from the start (upper left) of the supplied reference (i.e. the first parameter, to the the start of the returned range. In our case this is:
COUNTAcounts the number of values, so this will return the number of rows down from the top our window should start. Using the value currently in
$F$2this returns 17 for our data. Start 17 rows down, quite simply
- The third parameter indicates which column in the range to get the data from. This is
0in our case since we’re using the first (and only) column
- The fourth parameter is the height of the selection. The number of rows to select quite simple. In our example this is the value in
- There’s a fifth parameter which is the width of the selection. We don’t use that meaning that it will be same width as the first parameter, one cell in our case.
Phew! That was the hardest part. I promise.
If you’re followed along with this you have now two named ranges. These doesn’t show up anywhere, expect if you open the “Define Name” dialog box again. But we will put them into good use … Now.
Creating the diagram
Let’s create a line diagram. It is a little bit strange but for some reason you have to start with some data in there first. So let’s select all the data in the A and B column and create a “Line with markers” diagram. It should look something like the diagram to the right.
Great - that produced a nice digram. But all the data. We want a rolling window of 15 days (as it says in the
F2 cell). Let fix that.
Remember that our data will not come from the columns on the sheet but be served as a “window” that our offsetted named ranges will return.
Right click the diagram and select “Select Data…”. We’re now going to add the series manually using the two named range from before:
- First delete the series that is there now, created by Excel. We will not use it, since it shows all the data we selected.
- Click Add to add a new series
- In the “Name” box give the series a sensible name. I went with
No. Patients per day
- Y-values is the
RecentValuesnamed ranged. You have to give that using the workbook name for some reason (tell you soon). Write it like this:
- Label for category (X-axis) is the Recent Dates. In our cases this is
- Click OK and you should have a diagram that looks something like the one below.
When you refer to the named range we defined before you have to use the workbook (file) name. I presume this is because the name that we define is global. This also means that should you have many worksheets where you want to do this kind of dynamic diagram you need to choose the names of the named range careful. For example you might have a
CustomerSatisfaction_RecentDates which we do.
This is looks ok, right? Let’s play around with it and make sure that it works too:
- First change the
F2cell to 2. See how the diagram is updated
F2to 31 and you’ll get the entire range.
- Add a line below and see that the first data point is “rolled out of our window”
- Remove the row you just added and see that the first data point now is shown again.
- Add a trend line (right click the line and then “Add trendline”), notice how that trend is affected by the data that is shown, as you change the
F2value or add / remove data. Pretty sweet.
Using this technique can be very hand for a long list of data. Imagine that we track the number of patients for 18 months. We can still show a rolling window of the last 30 days. Or simply change the value and get an overview.
I know that I will come back to this post many times. I hope you found it useful too.