Dynamic Chart with Offset Function
In this post, I will show you how to create dynamic charts with Offset Function. You can create super handsome charts once you get a hang of this technique.
Offset function enables users to reference to cells with unknown location. As long as you have a starting cell reference and you know how many rows and/or columns you need to move, you can read any range in a worksheet using offset function. I am going to use this ability of offset function to dynamically change chart source data selection.
Some informative links about the tools used in this tutorial:
This is the data we are going to use as our charts source data:
I have inserted a column chart using items column as horizontal axis and first month column as values.
At this point, this is a standard static chart. I decided to use cell P15 as month selection cell. It is right beside the chart. And inserted a Spin Button (form control). You can see how to setup the spin button from the picture below.
Now that all is set, here is the tricky part:
Open Name Manager and define a named range. I named mine as Revenue and inputted an Offset Formula that will use cell P15 as an argument.
This formula selects 7 cells in a column that is as far as cell P15’s value from cell B4. Which means, as value of cell P15 changes, Revenue range will be referring to different cells. Now we have a dynamic range that is triggered by cell P15 and reads that months revenue values.
Now it is time to make our chart read this dynamic range. Open chart source data selection window and change data range as shown below:
I have named the file as short as possible to make it easier to write here. You need to type full name of your file along with its extension in singe bracelets. Than an attention mark and your dynamic range name. Just like the above picture.
When finished, click ok and return to your worksheet. Now you can click on your spin button and observe the chart dynamically showing different months revenues.
Please note that chart labels needs to be applied for each month’s values separately.
You can download this chart from here: