Excel Dynamic Chart #10: OFFSET Function Dynamic Range

[@jonbanks1987]

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!

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

[@stoicman31]

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

[@excelisfun]

You are both welcome!

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

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

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

[@nadermounir8228]

Great one. Thank u for this amazing Video 📹

[@excelisfun]

You are welcome for this oldie but goodie ; )

[@pramodchandelpkc]

Very Helpful Video

[@excelisfun]

Glad it helps!

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

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

[@Kapetanaki]

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

[@excelisfun]

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

[@krn14242]

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

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

[@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]

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

[@dmr450]

This is an excellent series.. Much more entertaining and stimulating than TV re-runs. Please keep making the videos... 5 Star all the way!

[@excelisfun]

Always good to hear that the videos help! Thanks!

[@oadmhernandez]

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

[@kumarraj2012]

i have been learning a LOT watching Excel VBA help videos and Mike's Videos!! You guys are awesome.. God Bless!!

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

[@excelisfun]

You are welcome!

[@vt1875]

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)

[@44hopeful]

I really appreciate your help. Have a wonderful day!

many tutorials on excel. i always end up learning with your channel. subscribed and will recommend to everyone!

[@excelisfun]

I am glad that the videos help!

[@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]

@HearIsRahul , you are welcome!

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

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

[@excelisfun]

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

[@excelisfun]

@Kapetanaki , I am glad that it helped!!

[@nhoj277]

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

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

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

[@excelisfun]

Cool! I am glad that the videos help!!

[@ismailismaili0071]

I think i started to understand offset function thank u Mr. Mike u are the best

[@excelisfun]

At the bottom in the Other section

[@excelisfun]

Glad you like it!

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

[@rahulsolanki1050]

Everytime I see your video i get awestruck.

[@excelisfun]

I am glad that the videos help you, Rahul : ) Thanks for your support!!

[@rahulsolanki1050]

@@excelisfun Pleasure is mine Sir

[@excelisfun]

Maybe use a 2 in the width argument.

[@McSoappy]

Awesome!!! Thanks so much!!

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

[@itsdaj]

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?

[@test123ok]

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

[@excelisfun]

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

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

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

[@excelisfun]

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

[@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?

[@ashish8223]

its amazing........

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

[@mikeycbd]

amazing! I've wanted to do this for ages! Thank you so much.

[@banhbengconuong]

thank you very much. this video helps me a lots in producing my report. Can I download the excel file too?

[@excelisfun]

@stewpotIRL , I am glad that it helped!

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

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

[@boazeps]

Hi Mike. How do I create a dynamic range that has 2 rows in it? You are the man Mike :)

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

[@excelisfun]

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

[@joepike3861]

Excellent tutorial!! I have a spreadsheet of daily stock price changes for a select number of stocks. I update the changes and have a small chart on the worksheet where I only need to see the most recent 38 days of price changes, so before your explanation, I would have to manually change the data range on the chart. What a pain. I've been searching the internet for a couple days trying to find the Excel formula or steps needed to automatically update this chart when I add new price change data to the spreadsheet. Your tutorial was exactly what I was looking for. Thank you very much.

[@excelisfun]

You are welcome!

[@joepike3861]

ExcelIsFun Similar to my last post, I am updating data every day and use this to find the number of days where the stock is above zero and the number below: =ABS(COUNTIF($J$131:$J$3000,">0")). I have to manually change the rows so Is there a way to use the OFFSET function or some other function to consider only the last X number days? Something like this: =OFFSET($J$3,COUNT($J$3:$J$2998),,-10,1) but with the above function built in? I sure would appreciate your help or direction to one of your tutorials that would apply to this. Thanks a bunch! Dave

[@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?

[@JosephVAnthony]

This is like geek cocain. I'm addicted. Thanks for all the awesome info!

[@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?

[@joseulloa6588]

Hi Mike, Thanks for this wonderful posting! Quick question, what if I have text instead of series or number in column date? Say I have text January, February, etc. In this case Count function wouldn't work, right? what function would I need to use in this case?

[@excelisfun]

Thanks!

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

[@LotfyKozman]

Great efforts

[@hafizurrahman9081]

Nice.very helpful bro.

[@xpxp7272]

I love you, man. I really do love you. With all my heart. It´s working all right. Thanks.

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

[@cemcel7077]

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

[@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?

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

[@andonirb1985]

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!

[@songin72]

It is a newly discovered function .. Thank you.

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

[@piyushasthana5223]

Great Tutorial

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

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

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

[@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?

[@HearIsRahul]

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

[@44hopeful]

Thank you for your quick reply!

[@khristine16]

great tutorials! thanks very much! :)

[@ouarirou1884]

As always very impressive

[@SalseraRossa]

Excellent video! Thank you for this - exactly what i was looking for!! :-))

[@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 :(

[@funniq]

Very usefull ... thank you so much

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

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

[@boazeps]

But of course. That works. You're the man Mike.

[@ilzezarina7126]

Thanks :) Great video!

[@StewartHand]

Brilliant love this one!

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

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

[@mdabbasi335]

AMAZING!

[@felipeferreira5206]

sweet video good job bud