Create Dynamic Rolling Chart to Show Last 6 Months
HTML-код
- Опубликовано: 30 июл 2016
- This video shows you how to create a dynamic rolling chart to show the last 6 months of data. Whenever new rows are added, the chart automatically updates to consistently display the last 6 months.
This technique can easily be updated for any other rolling time frame such as the last 12 months, 10 days or 6 weeks.
Two dynamic named ranges are created for the dynamic source data. One for the chart data and another for the chart labels.
These named ranges are set up using the OFFSET function. The chart is then edited to use these named ranges for its source.
Find more great free tutorials at;
www.computergaga.com
** Online Excel Courses **
The Ultimate Excel Course - Learn Everything ► bit.ly/UltimateExcel
Excel VBA for Beginners ► bit.ly/37XSKfZ
Advanced Excel Tricks ► bit.ly/3CGCm3M
Excel Formulas Made Easy ► bit.ly/2ujtOAN
Creating Sports League Tables and Tournaments in Excel ► bit.ly/2Siivkm
Connect with us!
LinkedIn ► / 18737946
Instagram ► / computergaga1
Twitter ► / computergaga1 
Хобби
Hi Alan.. great tip. In the past, I've used a more convoluted method to solve this problem on my worksheets. I'll definitely use your superior method going forward. Thanks and Thumbs up!
Thank you Wayne.
Excellent explanation, Cimputer Gaga, what an amazing trick, it's wonderful! Thank you very much for sharing this valuable tip that will be very useful
This is probably the clearest and easy to follow guide to automate your graphs in excel. 11 graphs every month needed to be updated one by one, data set by data set. This has literally saved me a few hours every month.
Only thing I would say to you to help you out in future.... Add the word EXCEL to your video title.... it was very hard to find this video ;)
Thank you, Jamie. For your comments and advice 👍
Perfectly explained. I'm working on a chart that includes the past 90d from my county's covid-19 data - and this dynamic method should be perfect. I've never used the OFFSET function before, so seeing the video was very helpful.
That is brilliant to hear Terry.
Great explanation, building multiple charts for a project that will require this dynamic rolling date function. Took a while to find an explanation online, so pleased my search brought up your explanation. Excellent work, thank you!
Great to hear. Thank you, Phillip.
this is EXACTLY what I needed, to display the latest 11 values of air quality data I'm constantly organizing in a graph that compares it to the daily limit, now I can get it to update automatically instead of dealing with the mess of modifying all ranges automatically
Fantastic! Happy to help Carlos.
Genuinely Awesome!! What a lad, thanks!
You're welcome Johan. Thank you!
Thx for explaining this sort of tricky part of charts in an easy way. Very hulpful.
No problem Robert, thanks.
Very informative, great explanation. Cheers.
You're welcome Thomas.
Definitely much easier to follow than the other non-video source I tried
Great to hear! Thanks, David.
Really helpful and straightforward to follow - thanks
Great to hear. Thank you, Neil.
Thank you I it's really helpful.
Your welcome Joseph.
Very good explained! Thanks a lot!!
Thank you 👍
Hi, This is brilliant, so thank you :-). I have a question. How can I produce a dynamic chart with 3 columns (i.e.) date, value, and percentage change which automatically update as I enter monthly data example
Jan 23.45 0.4%
Feb 23.50 ? Thanks a lot.
I've been watching your videos and learning a lot. A question I have is if you can use offset with a table to achieve the same result in a chart? Each time I add data to my table, I have to go and unclick some dates in the chart and I'd like to avoid doing that with a rolling chart.
Super helpful! Thank you!
Glad it was helpful! 😊
Brilliant!
Thanks so much Alin.
THANKS! :D
You're welcome Ethan.
Excellent, useful video. Thank you for the effort to create. I have a question though. When you continue to add data, eventually this will force the chart to scroll upwards, and require manual readjustment to continue to see it. Conversely, I have created a similar sheet where I add new rows at the top for most recent data (blood sugar tracking), and my chart will scroll down off the page if not continuously dragged back into place. Is there a way to anchor a chart into place regardless of modifications to the data on the same page? Thanks for any help on this.
Dude, you're a genius.
Thanks Jeff.
Wonderful video it increase my learning. Question. How to create a rolling chart to workout the last 6 months as a total average number or percentage number automatically capturing the data series on excel web data, into work book spread sheet?
Thanks in advance.
Cheers,
Very professional
Thank you Nándor.
This is great. How do I add a lookup into this formula? E.g. what is the last 6 months sales for Product A?
Incredibly helpful tutorial, many thanks :-)
My pleasure Rachel.
Yes sir.. This s really very useful.
And the same thing can i get dynamic chart in userform.
Actually I just want if i select a date from the combobox i want last 10 records. Is it possible. Pl support. Thanx in advance sirm
great video
Thank you very much Tony.
How do you add multiple sales ranges. In your example you only have 1 sales total but I have 3 different variants. How do I add this to the graph ?
Hello Sir, thank you very much for this clear tutorial. I would have the following question in order to complete my learning: In first instance I tried to use the data from a Pivot Table which didn't work. Then I created a simple table based on the Pivot Table's data and it worked perfectly. Does it mean that every time data from a Pivot Table has to be turned into a simple Excel table prior to apply all your instructions? Thank you. Kind regards, Etienne (Brussels)
Excellent, how about a stacked bar graph using this method?
How do you get the Chart labels to work when you have multiple series? I have 3 offset formulas set up to reflect my 3 columns of data (series) but I am having trouble with using an offset formula for the axis label (last 6 dates). Does the column reference in the offset formula have to change?
Fantastic!! I bumped into a problem when creating mine though. for some reason I was not able to find at this point it does not let me use the offset formula when creating the dinamic ranged names, I only allows me to use it if I delete the = at the beggining
I'm not sure what is going on with having to delete the =. I hope it is working for you now.
This is a great formula, but what I have noticed is that if some of my values are "0", (as we have some months without sales) then the offset wont account for these numbers and doesn't start at the bottom of the table. Any ideas on how to get around this?
I know this is an old video but I have an issue. My rolling chart has many label/data that are blank and therefore trying to use the offset function in the chart give me an error. Because my data is on a batch to batch basis where some get terminated and some data just isn't available I want to show these as blanks. Is there a way around this?
Is there a method to perform the same function but when the table is in days with multiple entries from one date? Want to get a chart for 90 days and 120 days of data, but some dates will have 3-5 entries.
This is very Helpful! However, I am having an issues with Multiple columns for the Chart Data Ranges which I dont ever hear explained and cannot find any videos in regards to multiple columns. Any Advice would be helpful.
Each column would need its own defined name I believe.
I wonder if you could add a video for a 3 months rolling candlestick chart for stocks. I have tried adding multiple series like open-day high-day low-close....but keep getting errors. Thx. Your video is great by the way.
Thanks Steve. I will see what I can do.
I have finally been able to create this as a 12 week rolling chart for a candlestick chart by creating more offset formulas for the other 3 columns.....however the issue I seem to be having is that the chart always reads the blank row directly below my data so therefor only showing 11 candlestick on my chart. this is the formula I have been doing for 12 weeks rolling chart.....=offset('Pivot tables'!$B$8,COUNT('Pivot tables'!$B:$B),0,-12,1)...cna't seem to figure out why it reads the one blank row
How can one do this on Google Sheets? I can't find the Formula tab etc...
My COUNT keeps referencing the last row as a Blank - so i'm really only getting the last 5 rows of data instead of 6. I have tried multiple times - is there something i'm missing?
Thanks...Can you help me here...how to take last 5 non empty cell values in graph (if that cell contain 0 value then graph should not show that entire row) pls pls pls help me
Introduce a column to reflect serial numbers of the data (1-10 or whatever). Put formula =LOOKUP(2,1/(G:G""),G:G) to return serial number for the last value. G is the column where you have the serial number.
From the lookup formula, deduct 5 and put it in a new cell (say, I30).
In a new column, put =IF(AND(G32>$I$30,E32>=1),E32,#N/A). G is where your serial number is, and E is where your data is.
#N/A will not be recognised by your line graph and will be ignored.
Hi,
Really appreciate this!
How can I do the same in Google Spreadsheet?
Thanks
My pleasure. Sorry but I do not use Google Sheets.
@@Computergaga no worries. Thanks again though
Bacon hath been saveth. Thank you!
Excellent! 😂
I have a scenario where there will always be "0" in the data after today's date instead of it being blank. Would it be possible to use some sort of an IF statement to only read data if there are 1 or more?
You may not need this anymore but someone else can find it. I have about a month’s rows of future dates preentered that I don’t want included in the range so instead of using COUNTA I use COUNTIF to count only if the date is less than or equal to TODAY(). You could apply this the same way or to whatever other numerical progression you have.
What change would I need to make to the offset formula if you had multiple columns represented on your chart? I have a column for weekly numbers and a column for the goal. Both of these are on my chart.
Also, thanks for the video it's helping me make the weekly adjustments easier.
If they are next to each other you should be able to just change the Width argument (last one of the match function) from 1 to 2 for the ChartData named range.
Your welcome, thanks.
Hi Alan.. is that possible on google sheet directly or via google script?
I'm not as familiar with Google Sheets so couldn't say Ilrak.
=offset(sheet1!$B$1,COUNT(sheet1!$B:$B)-5,0,6,1) put this beside the cell to make the list of the last 6 month data, then use it as the range for the chart
What if the dates went across the columns and the sales were rows A2:A10?
If I have 7 Series I have to do Offset data for each of the series? Or can I somehow make it work for the whole? I gave it a try via Chart data range: ='Trend ICO COE'!$L$72:$S$72;'Trend ICO COE'!$L$87:$S$108 (first part is series' names and second part I replaced with defined name with offset) and even though offset for data was pointing out to proper cells in Name Manager, my chart exploded...
It's so weird, because of built-in excel automation for chart creation. you simply mark whole table and it detects series, legend and data itself.
If I have to do it for each series and for at least 4 tables it's quite a lot of time
I think you need to do it for each series. But once it is done, it is done. So if this is a regular report. Spend 5 minutes now for automation in the future.
@@Computergaga Yeah I did just that and worked perfectly. Many thanks :)
Can you send me a formula for rolling chart for first 15 days
I have a problem. I have a worksheet with over 4000 rows of data. I followed the offset input as you showed us, however the report I am getting back is not for the last year, but for the yera 5/7/14 - 5/6/15.
Here is the formulae that I have:
DataJetA -- =OFFSET('DAILY VALUES'!$S$1,COUNT('DAILY VALUES'!$S:$S),0,-12,1)
LabelJetA -- =OFFSET(DataJetA,0,-18)
The column offsets are correct counts.
The data in the column is numbers. Can you tell me why it will not return the last 366 days starting from 5-18-17?
Joe
Hi Joseph, its hard to understand the formulas well without seeing the spreadsheet.
Would you like for me to send you a copy of it?
Sure, you can send it on Facebook at facebook.com/computergaga or email at admin@computergaga.com
sent to email
Super. I have replied Joseph.
Bear with me on this question. Here is the scenario... On one tab in excel I want have a table that reflects the last 8 entries from a data set on another tab. Lets say we are using cells A1 as a row header, A2 would have the data from the other sheet 8 weeks ago, A3 would have 7 weeks ago, A4 6 weeks ago etc..... I want these to change as current week information is added to the other sheet, so what was in A3 (week 7) would move to A2, what was in A4 would move to A3 and so on. The offset formula that I tried is the following =OFFSET(Tallmadge!H1,COUNT(Tallmadge!H:H),0,-8,1) Tallmadge! is my other tab. What this returns to me is the last entry verses the entry that was made 8 weeks ago. So apparently, I have the wrong formula. I just want this to count up 8 cells from the last entry and put it in the corresponding cell in my table.So if I haven't confused you too much.... HELP!!
Hi, if I understand you correctly the following formula should work. This is for the first row of the 8 weeks. The others would be the same except for -8 and then -7 etc.
=OFFSET(tallmadge!H$2,COUNTA(tallmadge!H:H)-9,0,1,1)
How to do the same in Google Spreadsheet?
=offset(sheet1!$B$1,COUNT(sheet1!$B:$B)-5,0,6,1) put this beside the cell to make the list of the last 6 month data, then use it as the range for the chart
ChartData
=offset(Sheet1!$B$1,COUNT(Sheet1!$B:$B),0,-6,1)
ChartLabels
=offset(ChartData,0,-1)
Is there such a thing as a scrolling rolling chart? or is it even possible?
I have a tutorial on a scrollable chart here - ruclips.net/video/rBj2PBQwO_o/видео.html
I don't see why you cannot combine both skills for a scrolling rolling :)