A YouTube video subscriber Mili, wanted to know more about creating a Dynamic Excel Column Chart using Offset function. So Mili sent me the data that was to be used in the charts, and I thought I would post this case study and video of creating a dynamic chart in Excel using the Offset Function.
I had previously posted a blog posting and a video that describes this technique.
You can check it out here:
How-to Make a Dynamic Chart Using Offset Formula
This posting was not meant to show you the workings of the Offset formula and some did not like that. It was only meant to show you the ins and outs of using an offset formula in a chart (with all the tips and tricks that I know about.)
In fact, that video really combined several topics on this blog that make up the Dynamic Offset Chart.
Namely, how to find the last row and also how to use the offset formula. You can check out these posts and videos here:
Find the Last Row or Last Column for Dynamic Excel Dashboards
This is the Bomb: or How I came to love the Offset function
However, Mili and a few other of the video viewers wanted to see how to use the Offset Function to create the dynamic graph for their Excel Dashboard in the same video.
Case Study – Create a Dynamic Chart in Excel using the Offset Function:
Mili asked for the following help:
“I’m creating a social media benchmark tool using Excel, and I need to create a pre set graph which will update itself as I add/remove/edit data. I have added some dummy data to create a graph but I’m not very familiar with the offset function itself. I had joined an excel forum and one of the members just did it for me but I have no idea how he did it so was struggling with it.”
So Mili sent me the data for the chart. Here is what it looks like:
After looking at the data, it didn’t seem too complicated. In fact, I would offer up an alternate and MUCH MUCH EASIER solution then using the Offset formula in Excel to create a Dynamic Chart. If you are using Excel 2007, Excel 2010 or the New Excel 2013 you can use this technique. It may not work as easily for Excel 2003, so you may want to stick with the Offset technique for that Excel Version.
This easier solutions involves using Excel Tables. You can read all about it on this post and it also has a video as well.
How-to Make Dynamic Excel Dashboard Charts Using Tables
(You can also see this technique demonstrated in the Video below)
Oh well, if we must use the offset function in our Excel graph, then lets get to it.
Step 1: Learn to love the offset function:
This is the Bomb: or How I came to love the Offset function
Step 2: Learn to create a formula that will find the last row of your data:
Find the Last Row or Last Column for Dynamic Excel Dashboards
Step 3: Create your Named Ranges with your Offset Functions:
How-to Make a Dynamic Chart Using Offset Formula
1) Define Named Ranges using the Offset Function that uses dynamic techniques for each data series and the X axis categories.
2) Create a chart adding the Named Ranges as the series and axis using the Excel Select Data dialog box.
Very Important – This step MUST be done to the chart series not the chart data range as excel will translate the Offset named range to an actual range that will not remain dynamic.
Just as Important – When adding the named range to the chart series, you MUST reference both the file name as well as the named range…i.e. FileName!NamedRange.
Video Tutorial:
You can check out the New Combined Offset Function and Dynamic Chart creation video here:
Please let me know if this post and the video has helped you to understand how to create your very own Dynamic Excel Dashboard Component.
Steve=True
Hello Steve=true,
I wanted a to write a question in an editable window, where I can edit my text to be more expressible, or where I can insert code from my excel macro, or paste a picture of my chart to be more helpful, but this is probably not a forum-like ask-support… :-/, never mind…
sorry that I’m writing this way, but I want this comment calmly public…
I have a question, but first: you have a super video on this side ,
but the question is: I tried to make my “XY” chart make dynamic by the offset formula and I encountered this problem: I came to the point, where I made the name ranges, and then when I highlight the data (including the x-axis(as you named it in your video) range) and make a XY chart, it makes no xy dependance but two dependances(“X” and “Y” as a function of the x-axis range). The second thing is, the edit button in ‘category’ is not highlighted, so I can’t edit the x-axis data. I don’t know if it helps, but the result= two dependances, is exactly the same, as the excel (I use the 2007 version) makes a default graph from two columns throught a “macro”. To be specific- when you record a xy-chart macro in excel 2007 this way: You highlight the two columns, press the insert button, choose XY scatter and choose one of those graphs, and then ends your recording. The result will be a proper xy- dependance, BUT if you RUN that macro, the result will be not that proper, but rather the way, I mentioned as the problem, I’m writing about the dynamic chart- so two dependances. In other word, to make the proper chart throught running a macro, you must record the macro this way: you highlight the two columns, select a cell at least one cell away from the two columns(that is important), and again you press the insert button and choose one graph, AND you get a blank field (where the graph should be) AND select from context-menu the source data and type them by yourselve. Then whenever you run the recorded macro, you get the proper version of the graph.
What I’m trying to say is, that there might be a resemblance in the default. And I should choose my source data by myself. I tried, but I cannot somehow figure it out to combine the two approaches (I mention, and you provide)…
Thank’s for your help.
OK, everthigs allright, I managed to make my XY-dynamic chart, I just had to completely leave the x-Axis (category) out and move ahead with some changes in the way you explained the “column dynamic chart” to apply in the “XY dynamic chart”.
Thank’s for your help.