Excel Dynamic Chart #10: OFFSET Function Dynamic Range
HTML-код
- Опубликовано: 10 дек 2009
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel Finished File: people.highline.edu/mgirvin/Y...
See how to use the OFFSET function for two different types of dynamic ranges for a chart:
1)Dynamic Range that adds latest records
2)Dynamic Range that shows only last 4 Months of data
Learn about named formulas that use OFFSET function to create dynamic ranges and see how to insert named ranges into a chart. Learn about the OFFSETs 5 arguments:
1)Starting point
2)How many rows (up or down) do you want to move the starting point?
3)How many columns (left or right) do you want to move the starting point?
4)How tall is the range?
5)How wide is the range? 
Наука
![Megan Thee Stallion - Mamushi (feat. Yuki Chiba) [2024 London Performance]](http://i.ytimg.com/vi/pxTCJUuorKc/mqdefault.jpg)
You are both welcome!
You are the best. Every time I try to figure something out in Excel, I search all over the internet for the answer and never find the answer until I get to one of your videos. Now I can plot the last 20 data points in a very long data set that grows every day. Thanks!
Also: The reason that you want to watch theses vids and the reason that I make them is because:
We all have so much fun with Excel because Excel is usually more fun than video games or TV or movies or many other things that we may do...
And even if we love watching TV or play video games, if you really learn how to use Excel is blows the alternatives away!
I just want to thank ExcellsFun. I have learned a lot from your videos.Been checking out your videos for ages.
Send clearly stated question and example workbbok to: excelisfun at gmail and I will try to take a look over the weekend.
holy cow, Mike. I have fought and fought with a personal excel challenge ALL weekend and finally this video has gotten me over the hump. Not sure what I was doing wrong earlier, but I know how to do this NOW. I've incorporated a slider into my OFFSET to allow my user to easily control the contents of my graph. Life is good again. I know you hear it all the time, but thanks!
You can skip the volatile function al together by using INDEX function instead of OFFSET. See this video:
Excel Dynamic Chart #12: INDEX function as Alternative to OFFSET function for Dynamic Ranges
people.highline.edu/mgirvin/excelisfun.htm
people.highline.edu/mgirvin/ExcelIsFun.htm
You just saved me a ton of work. Used a few hours on this yesterday and didn’t think I would get it working today, but here we go. Thank you
Thanks for that hot tip! Saving COUNTA in a cell is a great idea!
But as I say in the video, it is better not to use the whole A:A column because (in Excel 2007) - you cannot store that much data in Excel a million rows of data is not possible. Better to use, for example, A1:A1000 if you expect 500 entries, or A1:A2000 if you expect 100 entries.
Combine the two ideas and you have a winner. See next note for another winner
This is exactly what I needed! Thank you so much. The tutorial was great. Things are in a slightly different place now in 2016. But this was so helpful and useful!
having all the highline gear to work along with and pdf.docs to explain is too much, you are just the most thorough guy, and really generous, thanks it saves me hours of slog and pain.
Post questions to:
mrexcel [dot] com/forum
You are welcome!
Clear, Clean and Detailed. Just what I was looking for... Thank you Sir.
Excellent!! This would have saved me HOURS upon HOURS of aggravation at work had I found your videos a year ago. Great work and delivery...
OK I will! Making the videos is fun! But only because in my own working life, I have continually been amazed about how much my knowledge of Excel helps in all the jobs that I have had. And remember: rate, rate, rate, so that the video rise to the top and many others can also have fun with Excel!
Mike, oldie but a goodie. Just re-viewed this video for a refresher on dynamic charts with offset. Came in handy.
Very interesting function OFFSET() combined with dynamic range and chart makes it amazing to use. in addition to that, it is great to refresh chart skills. Great video. Thanks Mike.
@test123ok , I am not the person to ask about VBA. There is an alternative site (totally different than this obe) where you can ask VBA questions:
mrexcel[dot]com/forum
I will1 Thanks for thr 5 stars!
I have about 5 more NEW Dynamic Chart videos coming out later this week!
Post question to THE best Excel question site:
mrexcel[dot]com/forum
This is an excellent series.. Much more entertaining and stimulating than TV re-runs.
Please keep making the videos... 5 Star all the way!
@cutelilmeeh , I am sorry, but I do not know how to use a Mac.
Always good to hear that the videos help! Thanks!
I love it! Now that's a hot tip! I just tried the checking the check mark after editing the formula and it works like a charm. Thanks!!
For back and forth dialog to get Excel solutions, try THE best Excel question site:
mrexcel. com/forum
@test123ok , I am not the person to ask about VBA. Try this alternative site (not affiliated with this site) for asking VBA questions:
mrexcel [dot] com/forum
i have been learning a LOT watching Excel VBA help videos and Mike's Videos!!
You guys are awesome.. God Bless!!
I think i started to understand offset function thank u Mr. Mike u are the best
amazing! I've wanted to do this for ages! Thank you so much.
I don't have a video for that. But I bet you could add an extra column for names and use an IF function or something like that. Have person enter name, IF then only shows that name, all the rest are blank. Add that column of blanks and names as the labels in the Select Data dialog box...
@HearIsRahul , you are welcome!
I love you, man. I really do love you. With all my heart. It´s working all right. Thanks.
I really appreciate your help. Have a wonderful day!
Glad you like it!
Excelente, fácil de entender, me ayudo a crear los gráficos dinámicos para las métricas de mi trabajo, me ahorro mucho tiempo.
Thank you for your quick reply!
@MacGyver7640 , Thanks!!! (They usually are not intentional, but the "trying to fix the mistake" is intentional. Since we humans by definition make a 100 or so mistakes every day, it is usually good to teach how to deal with mistakes.
This is like geek cocain. I'm addicted. Thanks for all the awesome info!
It is a newly discovered function .. Thank you.
Awesome!!! Thanks so much!!
@HearIsRahul , see this video:
youtube [dot] com/excelisfun#p/c/3FBEE51974F03CCF/0/grInTVFnh8c
at 14:15 minute mark
Cool! I am glad that the videos help!!
At the bottom in the Other section
Brilliant love this one!
See if this video helps:
Excel Dynamic Chart #9: 4 Week Chart Dynamic Formula & Dynamic Data Validation Formula
Excellent video! Thank you for this - exactly what i was looking for!! :-))
Thanks :) Great video!
Great one. Thank u for this amazing Video 📹
You are welcome for this oldie but goodie ; )
such an awesome video!!
@Kapetanaki , I am glad that it helped!!
THANK YOU SO MUCH!
@MacGyver7640 , Cool! I am glad that you could implement the knowledge!
great tutorials! thanks very much! :)
As always very impressive
I am not sure. Try this alternative site (not affiliated with excelisfun at RUclips) for posting Excel questions:
mrexcel [dot] com/forum
Amazing, many thanks
Everytime I see your video i get awestruck.
I am glad that the videos help you, Rahul : ) Thanks for your support!!
@@excelisfun Pleasure is mine Sir
Thanks! I used Table function in 2007 and made a dynamic chart adding columns to my table :)
Btw, in the Name manager I think if you click on the tick, it applies the new formula you typed (no need to close and reopen the name manager)
If they are individual columns, repeat procces for each. If it is one table, try this video title:
Excel Magic Trick 584: Dynamic Range for Periodic Data Dumps into ...
I use the last trick for my stock charts I am a statistician and naturally scoff at technical nonsense, so know the data is mostly random, but i Use a adaptive response rate single exponential smoothing model with a tracking index, to tell when the data is persistent in a direction( sort of a trend), and with this trick it seems to only graph well going back 200 days, but I can select 90, 30, 50 ,100 and they all scale and are really quite information full as opposed to tea leaf reading. They indicate persistance and movement around the mean well.
sweet video
good job bud
No time to answer. working 80+ hours a week. Try THE best Excel question site:
mrexcel[dot]com
Rather than closing and opening the name manager... we can click the small checkmark and it will take in the edited formula... Mike Thanx a ton for ur vids.. u my star...
Cool tip!
excellent!
For large array, microsoft says:
"=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
When you use the dynamic range name in a formula, it automatically expands to include new entries.
There is a performance hit because OFFSET is a volatile function ... COUNTA function inside the OFFSET has to examine a large number of rows. You can minimize this performance hit by storing the COUNTA part of the formula in a separate cell, and then referring to the cell in the dynamic range:"......
Thanks!
Great Tutorial
That is a great question. I do not have a video on that topic. I will make one but it will be at least a month because i am so back logged. We really need back and forth dialog to get you your custom solution becasue it is not a stright forward solution. You can try posting question at this alternative site:
mrexcel [dot] com
I good place to start is using array functions. So if you have a range of values that you want to sum and make errors blank try, =sum(if(iserror(A1:A10),"",A1:A10))
Remember to use Array Functions you have to press CTRL+SHIFT+ENTER after typing the formula.
Thanks Mike. It is always eye opening to watch your videos
I need to do something similar to this. In your video you said you usually use functions, do you have a video using functions that will take the last 5 or 6 months of data dynamically?
You are Just superb.
@stewpotIRL , I am glad that it helped!
AMAZING!
@ColdArmyKid ,
Try this alternative site (not the same as excelisfun) that is set up specifically to ask Excel questions and have dialog:
mrexcel [dot] com/forum
(mrexcel [dot] com is not affiliate's with excelisfun at RUclips)
This site has many people who can help you get the solutions you want.
If you ask a carefully constructed question, you will get answers quickly
post questions to mrexcel[dot]com/forum
Thank you so much
its awesome!
Very usefull ... thank you so much
Thanks, I'll try that.
Cool tip. Is it possible to do something similar with an XY (scatter) plot that allowed me to extend both the rows and columns that the chart encompasses?
Nice.very helpful bro.
Very Helpful Video
Glad it helps!
I love the dynamic named ranges for charts! I also love to use Excel Tables, which automatically copies formulas down to the next row when a row is added. But I don't seem to be able to use them together. Is it possible; am I doing something wrong? At first it seems to work, but as soon as I add a row to the table, the references in the series revert to raw cell references.
its amazing........
I must admit I might be an econemetrician and used spreadsheets since lotus 123 in 1987, and when quattro pro, brought the first solver to spreadsheets and when excel finally threw of the shackles of multiplan and became the destroyer in office 95, but it took a bit of practice with this one , tricky tricky!! You and mrexcel just kill me man!!
Great efforts
You can delete the whole axis label range and click = then the tab you want inserted to create the dynamic range. I have found it much easier than trying to delete everything to the ! mark
But of course.
That works.
You're the man Mike.
title of video is:
Office 2010 Class #20 What Is Excel?, Editing Ribbons & Quick Access Toolbar, File Extensions
Thanks for all the wonderful tutorials.
Coming from a programming background, I have written much of excel automation in VBA until I saw these videos.
My question : I was wondering if it was possible to have a dynamic reference to a source data file. What I would like to do is, to set up a template with all these in built formulae and when the template is opened, it asks for the name of the input file to run against.
So each week I can simply open the template and give it the current week's
Hello, first thanks for the tutorial, really helpfull. My question is quite simple, is OFFSET able to call for data in other spreadsheet? If my data is in Sheet1, but the OFFSET function is used in a cell in sheet2, this should work anyway? regards.
Thank you!
You're welcome!
More than great ;)
Glad it helps, Hazem!!
Maybe use a 2 in the width argument.
I would use formula like this for a date and sales dynamic range:
DateRangeAfterDate
=INDEX($A$2:$A$101,MATCH($D$2,$A$2:$A$101,0)):INDEX($A$2:$A$101,MATCH(9.99E+307,$A$2:$A$101))
SalesRangeAfterDate
=INDEX($B$2:$B$101,MATCH($D$2,$A$2:$A$101,0)):INDEX($B$2:$B$101,MATCH(9.99E+307,$A$2:$A$101))
Date column was sorted.
Where D2 had first date for dynamic range (like 1/1/2013).
B column had numbers.
A column had dates.
No records would be enetred after row 101.
Hello!
Thanks for the amazing video, I have checked many webpages to achieve the goal, but I didn't till I have seen this one!! But I have now another problem...
I plot a X Y (Scatter) chart, and I use a cell to tell it from which row on should be plotted. It works perfect, but the axes values don't get properly updated. there is a way to set them dynamically? because leaving the Auto option doesn't seem to work properly...
Thank you for the help!
Hello, I am totally in awe of what you have shared. I have one question, I need to have the data collected from the beginning of the dates and not collect backwards. How can I change this formula to show the results from January down? Thank you!
Hi thanks a lot for your video. can anyone tell me what should i do if i want to use text instead the month or date? like instead of january i may use Mike=50k