Excel Dynamic Chart #10: OFFSET Function Dynamic Range

Download Excel Start File: people.highlin...
Download Excel Finished File: people.highlin...
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?

[@excelisfun]

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

[@brnjnsvld]

What if you want the number of series to be dynamic?

[@excelisfun]

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...

[@excelisfun]

@HearIsRahul , see this video: youtube [dot] com/excelisfun#p/c/3FBEE51974F03CCF/0/grInTVFnh8c at 14:15 minute mark

[@excelisfun]

Send clearly stated question and example workbbok to: excelisfun at gmail and I will try to take a look over the weekend.

[@excelisfun]

No time to answer. working 80+ hours a week. Try THE best Excel question site: mrexcel[dot]com

[@excelisfun]

@cutelilmeeh , I am sorry, but I do not know how to use a Mac.

[@excelisfun]

people.highline.edu/mgirvin/excelisfun.htm

[@excelisfun]

people.highline.edu/mgirvin/ExcelIsFun.htm

[@excelisfun]

At the bottom in the Other section

[@excelisfun]

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.

[@excelisfun]

You are both welcome!

[@excelisfun]

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 ...

[@excelisfun]

You are welcome!

[@excelisfun]

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

[@excelisfun]

title of video is: Office 2010 Class #20 What Is Excel?, Editing Ribbons & Quick Access Toolbar, File Extensions

[@excelisfun]

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

[@excelisfun]

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!

[@excelisfun]

@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 KZbin) 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

[@excelisfun]

See if this video helps: Excel Dynamic Chart #9: 4 Week Chart Dynamic Formula & Dynamic Data Validation Formula

[@excelisfun]

I am not sure what the problem is because when you use a Defined Name with the OFFSET Function it should work from any sheet. Make sure that when you enter the Defined name that there are not Double Quotes in the Defined name. Sometimes Excel enters those by mistake. Such as: ="OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A$2:$A$12))" instead of: =OFFSET(Sheet1!$A$2,,,COUNT(Sheet1!$A$2:$A$12))

[@rbsrifat]

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

[@excelisfun]

@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.

[@excelisfun]

I am not sure. Try this alternative site (not affiliated with excelisfun at KZbin) for posting Excel questions: mrexcel [dot] com/forum

[@excelisfun]

I will1 Thanks for thr 5 stars! I have about 5 more NEW Dynamic Chart videos coming out later this week!

[@excelisfun]

@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

[@excelisfun]

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!

[@excelisfun]

@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

[@excelisfun]

Yes, but this addiction is a skill that employers highly demand!!

[@excelisfun]

For back and forth dialog to get Excel solutions, try THE best Excel question site: mrexcel. com/forum

[@excelisfun]

@HearIsRahul , you are welcome!

[@excelisfun]

Maybe use a 2 in the width argument.

[@excelisfun]

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!!

[@excelisfun]

@Kapetanaki , I am glad that it helped!!

[@excelisfun]

Cool! I am glad that the videos help!!

[@excelisfun]

Post question to THE best Excel question site: mrexcel[dot]com/forum

[@excelisfun]

You are welcome!

[@excelisfun]

You are welcome!

[@excelisfun]

You are welcome!

[@excelisfun]

@stewpotIRL , I am glad that it helped!

[@excelisfun]

@MacGyver7640 , Cool! I am glad that you could implement the knowledge!

[@excelisfun]

Yes: people.highline.edu/mgirvin/excelisfun.htm

[@excelisfun]

post questions to mrexcel[dot]com/forum

[@excelisfun]

You are welcome!

[@excelisfun]

You are welcome!

[@excelisfun]

Always good to hear that the videos help! Thanks!

[@excelisfun]

You are welcome!

[@boresow]

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!

[@McSoappy]

Awesome!!! Thanks so much!!

[@excelisfun]

Post questions to: mrexcel [dot] com/forum

[@excelisfun]

Thanks!

[@nadermounir8228]

Great one. Thank u for this amazing Video 📹

[@excelisfun]

You are welcome for this oldie but goodie ; )

[@VEGETADTX]

Human kind...making things complicated for absolutely no rational reason. Can anyone give me any logical point, any PHYSICAL LAW that gets in a way of making something that will let you input DATE, input value, and simply make a dynamic chart automatically. WHAT IS THAT PARTICULAR THING THAT STOPS HUMANS FROM DOING SOMETHING SO DARN SIMPLE!?

[@aamirmasood4655]

Hi Mike, plz advise if this is still worable in excel 2021 and LTSC veraion, I am facing problem with this offset formula and I have office 2021 LTSC, thanks in advance

[@cosmindespan4111]

Hello, Not sure if you can help me, just putting this here. Any idea if from the below table Jan-14 Feb-14 Mar-14 Apr-14 Costs Sales Delta Costs Sales Delta Costs Sales Delta Costs Sales Delta Item 1 100 200 100 200 300 100 300 400 100 400 500 100 Item 2 200 400 200 300 500 200 400 600 200 500 700 200 Item 3 300 600 300 400 700 300 500 800 300 600 900 300 Can I create a evolution chart for each one of the "Items"? Note that the month is actually 3 merged cells, so each month has Costs Sales Delta values? Thanks a lot!

[@es30000]

Does this work with a "stacked bar chart" I have about 35 different series being pulled from several different sheets into a single chart which I want to be dynamic? The problem I am having is that my chart is not acting "dynamic" and when I am selecting my data source, it says "the data range is too complex to be displayed. If a new range is selected, it will erase all of the series in the Series panel."

[@travs7]

Great video! I can replicate your dynamic chart but when I apply it to a spreadsheet I'm using, where the range starts at C200, it spits out a #value error. Strange as it'll work towards the beginning of the same spreadsheet when I test it. I can take the same offset formula and apply it to the graphs without any problem but it doesn't cap the range and plots everything instead of omitting the blank cells at the end of the range. Any thoughts?

[@jlessgolfer]

I noticed your charts are on the same sheet as your data. I tried creating a dynamic chart following your video, but ran into errors when I used a different sheet for my chart. The dynamic chart works just fine on the sheet with the data but sometimes leave off data when the chart is on a different sheet. Do you have a video outlining how to do this? Would it involve using a global name perhaps?

[@mendfred]

Maybe you can help me with one improvement to the code. I have a chart that shows objective, result in the y-axis and months in the x-axis. I want to fill objective and date field for the whole new year, but in the chart i want to show only last 12 months. How can i bind date and objective with the results field so that if result field is empty i doesn't include last 12 filled objective and date fields but last 12 obj. and date fields that are relevant with last 12 result field.

[@utk.k]

Hi thank you for this video, I had a few more q?'s. The data set that I am working with has random multiple entries datewise, I want to use the second graph that u showd in the VDO to display weekly data. In the video you chose to display the last 5 rows as it represented last 5 months, In my case I dont know how many rows would that be. Also as I am dealing with large number of entries could u show us how to make the charts not scroll and adjust lengthwise only to get more dates in the chart

[@EciekPeciek]

@ExcelIsFun Yes, mrexcell site is the best but not fully functional. I would say, using it is a pain. but... maybe you will have some time in the future and ... you will someday answer my burning question. To make your life easier I need to add that in this stacked chart, series are aligned horizontaly and I haven't found a method for using dynamic name ranges (offset function) for this. I've tried different layouts for dynamic name ranges and nothing works. Bye

[@GabiRav]

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:"......

[@jgostling]

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?

[@excelisfun]

Glad you like it!

[@MrHappycapitalist]

I have this problem, but I wanna do it on horizontal values. I.E. I want to add columns instead of rows. So I recon I should use width instead of height. I also wanna base this upon selection of a month instead of just counting cells with values. I can make the offset work everywhere outside of the chart function.

[@aalb1970]

Hi. This video was very helpful. Thank you very much. I have a question: Is it possible to create a dynamic range which selects the two latest periods plus the same period the year before? At work we have a client who prefers this layout in their reports and they are a b**** to edit manually. BR Anders.

[@EciekPeciek]

Hello ExcellsFun, I have another problem. I have dynamic stack chart with changing number of series. Everything is ok when number of series is maximum (i.e 10) but when dynamically number of series decreases (for example to 5) I'm getting zeros in chart legend (legend must be used). How to deal with that? Thanks in advance for your help.

[@stoicman31]

I just want to thank ExcellsFun. I have learned a lot from your videos.Been checking out your videos for ages.

[@44hopeful]

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!

[@linuxBlob]

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.

[@cutelilmeeh]

What does F3 Key do? I use a mac, and the key functions above are different, so maybe if I can figure out the command I can use the "Help Bar" to figure out the MAC version shortcut. Do I really need that step anyways? Thanks! I always enjoy your tutorials.

[@peachesandsunshine9]

Hello, is it possible to create a dynamic chart with more than one Y value? Let's say I have data for Sales A, Sales B, and Sales C for the y values and each have the same Date for the X values. Would I have to make individual formulas for the Y values?

[@cemcel7077]

how can we use the values ​​in two different columns in one graphic? Continuesly. Not separately. Thanks in advance.

[@adeelmush]

Hi It looks great, just need a little bit assistance for creating dynamic dashboard with date range. so when a date range changes the whole of dashboard changes. so can you help me out in this regards

[@chrisbroughton6]

I have done this exactly as you did and it works great. However when I save my workbook then re open it my charts dont work anymore and when I open the chart series to see what is going on it changes my sheet reference from this: ='Energy Dashboard.xlsx'!Summary_Gas_Range to this =[0]!Summary_Gas_Range. Why is it resetting my workbook name? I have made no changes in it :(

[@VanessaKlinger]

Hi I had a question about a bar of pie chart How do you keep the bar reference and delete the chart I have a reference if you aren't sure what I'm trying to say? Thanks

[@nhoj277]

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.

[@thomasavinash72]

It works for me "Yeah" Couldn't make a dynamic title though

[@deependra2008]

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...

[@excelisfun]

Cool tip!

[@HearIsRahul]

Sir you Have short cut too how add Add-in Botton on Ribon which we see Near the Developer Rebon Pleas help Me ... 1 More time

[@ashish8223]

its amazing........

[@nhoj277]

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.

[@HearIsRahul]

veary use full it Than'X you Help me toooooooooooooooooo Much ☺ Rahul Thakre: Pune Maharastra

[@banhbengconuong]

I have problem of drawing a dynamic radar chart. Could you please help me?

[@pramodchandelpkc]

Very Helpful Video

[@excelisfun]

Glad it helps!

[@صالحالحوسني-ي6ز]

Can you send me formula for the 5 frist months

[@rac2873]

I am about to lost my mind. I have tried this over and over and I am trying to create a dynamic range only showing the last 12 months. I check the range is highlighted and it works however when I go to add the range in the chart I get an error every single time. A formula in this worksheet contains one or more invalid references. How can I have an invalid reference when the formula checks out. I am going on 16 hours now and I get the same thing over and over. UGH!!

[@deninsrmic4165]

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.

[@antonioacharomo2986]

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.

[@mikedbman]

I created a simple line chart with three lines all referring to the primary Y axis. But the data is distorted in the chart. The first line, Gross Margin shows up higher than the Revenue line. If I change the order of the data a different line appears "correct" but I cannot get them to all three be correct. Suggestions?

[@kylejacobs1007]

I want to have a dynamic excel chart range used on an advanced filter. I am filtering by location to get customers at that location and have sales per month. After using the advanced filter my comes out like Customer Period Sales Customer 1 Jan 2016 50,000 Customer 2 Jan 2016 34,000 Customer 1 Feb 2016 22,000 Customer 2 Feb 2016 55,000 etc..... I would like each customer to be its own line over time with sales as the y axis Any idea how to easily plot this? When I try i get multiple data series for the same data series(customer 1 repeats everytime it shows up)

[@ReDsOxFaN4LiFe1]

What if, instead of the past 5 months, I wanted to shift the months. So for example if I want to have data from the 4th quarter of the first year and then use an active x button to "zoom in and out" (basically add more months) but NOT at the end of all the data.

[@krn14242]

Mike, oldie but a goodie. Just re-viewed this video for a refresher on dynamic charts with offset. Came in handy.

[@viewer844]

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.

[@oadmhernandez]

Clear, Clean and Detailed. Just what I was looking for... Thank you Sir.

[@Intelli-gent01]

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

[@HasseLarsen]

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

[@RobertFCM]

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.

[@ashaydwivedi420]

Name Manager Is Not Silly, You Forgot To Put The Equal Sign

[@rbsrifat]

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

[@richardkinzer]

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!