Blog Archives

Excel – Dynamic Range 12 Month Rolling Horizontal Chart

Horizontal 12 Month Rolling Chart

After publishing my blog on dynamic ranges to create a 12 month rolling chart (http://wp.me/p2EAVc-ah), I have received a number of enquiries about how to apply this to a horizontal table of data.

Personally, I prefer to have my data tables set vertically as it gives me far more options to slice and dice my data, apply filters, pivots etc. Based on the number of questions I have had there seem to be quite a number of businesses out there using a horizontal data layout.

So here is how it’s done;

The formula you will need will be as follows (bearing in mind you may need to adapt this to your own data table);

=OFFSET(Sheet1!$B$2,0,COUNT(Sheet1!$2:$2)-1,1,-12)

Our anchor point is $B$2, and always starting in the same row (0). The COUNT function counts the number of cells in row 2 and takes away 1 to give the correct end cell i.e. the last cell in the row to contain a numerical value. The height of the range is 1 row but the width is 12 columns back from the last cell that was identified by the COUNT function.

As we are applying this to a chart, we also need to create a dynamic range to pick out the correct labels;

=OFFSET(DynHorizData,-1,0)

As explained in the earlier blog, this dynamic range is based on the dynamic data range we created above. “DynHorizData2 is just the name I assigned to the range.

And there you have it…a 12 month rolling chart based on a horizontal data table.

To see an example using the formulas above follow the link below to open a workbook (Excel 2013) called “Horizontal Dynamic Chart.xlsx”. The workbook is read only, so click on FILE and Save As to download the file and then you can edit the data and see what happens.

https://onedrive.live.com/redir?resid=2C036E200F2C8BCF!242&authkey=!AKh4mjBK2AWbTnw&ithint=file%2cxlsx

Advertisements

Excel – Creating Dynamic Named Ranges

If you use named ranges in Excel, you’ll know how useful they can be. If you are not familiar with them check my blog ( http://wp.me/p2EAVc-99 ) on setting up named ranges and how they can be used.

If you are currently using them you will also know that it can be a bit of pain having to update the range all the time as new records are added or removed from your data. If your data does change regularly, wouldn’t it be nice to have a named range that automatically adjusts to the correct number of rows or columns? Well…you can, by creating dynamic ranges.

At the heart of a dynamic range is the OFFSET function. Before we embark on creating ranges, let’s look at the syntax first as it is not that straightforward.

The official syntax you get from Microsoft when you enter the function is;

=OFFSET(reference, rows, columns, [height], [width])

…which doesn’t really tell you much if you have never used this function before.

Reference: every range has a starting point, even dynamic ones. Reference is like an anchor point from which the rest of the range is referenced.

Rows: is the number of rows away from the reference or anchor point. Positive numbers represent rows down, and negative numbers rows up.

Columns: is the number of columns away from the reference or anchor point. Positive numbers represent columns to the right, and negative numbers columns to the left.

=OFFSET($A$1,0,0 would mean the range starts from A1

=OFFSET($A$1,1,0 would mean the range starts from A2 (1 row below)

=OFFSET$A$1,0,1 would mean the range starts from B1 (1 column to the right)

…and so on and so forth.

Height: this is optional, but represents the number of rows you want to include in your range. So if we had =OFFSET($A$1,0,0,5 then the range would be 5 rows starting from A1.

Width: another optional argument. This sets the number of columns that make up your range. Continuing the formula from above, if we have =OFFSET($A$1,0,0,5,3) then we would set our range to 5 rows high and 3 columns wide starting at A1.

Using OFFSET to create a defined range

Using OFFSET to create a defined range

This of course gives us a fixed range, as we are specifically defining the number of rows and columns we want in the named range.

So how do we get it to be dynamic?

We need to build in functions that calculate the number of rows and/or columns within the OFFSET function. Generally speaking we do this within the height and width parts of the function, but you may need to alter the other arguments such as when you work with dynamic charts.

So let’s build a dynamic range;

First of all, you can’t create a dynamic named range in the same way as a normal named range i.e. select a bunch of cells and give them a name. We need to go to the NAME MANAGER and click on NEW, or go to DEFINE NAME.

In either case, give your range a name.

Decide whether the range is specific to the worksheet or can be referenced from anywhere in the workbook.

Add a comment…purely optional as a note to yourself or anyone else vaguely interested in the named range.

And then the important bit – REFERS TO: Rather than entering a basic range, this is where we have to use the OFFSET function.

Starting with a basic table, I want to create a dynamic range that automatically works out the number of rows in a fixed width table of 5 columns.

Working out what we have and what we need to create our dynamic range

Working out what we have and what we need to create our dynamic range

So our formula will look like this;

=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),5)

In plain English…starting 1 row below A1, count the number of cells that contain numbers in column A (to work out number of rows) and set the width to 5 columns.

In this example I am excluding the heading row, but you may well need to include it, if for example, you are creating a dynamic range to use in a Pivot table.

Below, we can see the correctly selected data.

Correctly selected range based on rows found

Correctly selected range based on rows found

If I add a few rows of data, the range is automatically recalculated to include them.

Recalculated range with extra rows

Recalculated range with extra rows

If I want the range to check the number of columns too then I need to modify the formula as follows;

=OFFSET(Sheet1!$A$1,1,0,COUNT(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

Recalculated range with extra columns and rows

Recalculated range with extra columns and rows

Now, not only does the formula count the number of cells containing numbers in column A, it counts the number cells containing text (hence COUNTA)across the top of my data table to give me the number of columns that make up my table.

So now you can create either a one-way or two-way dynamic range depending on your personal requirements, and no need to manually update the REFERS TO cell references.

In another blog, I will show you how you can use the OFFSET function to create dynamic charts.