The chart range does not dynamically change in the downloaded file the way it looks like it does in your video. What's up with that!? This is what I am really looking for. Please help!
I know I’m not might not gonna use this one, or some other tutorials, but each and every time Leila posts a new video, I HAVE TO watch it. She makes everything so intelligible and so pleasant to watch.
As someone who's done a bunch of analysis work over time, you never know what of this training will benefit you in the future. As such, my approach is similar to yours, I watch all these videos and absorb as much as possible with the understanding you can never know too much.
Quick tip: @7:39 you can use a little trick to shows "1" for "TRUE" and "0" for "FALSE." Its done by adding two consecutive minus signs outside the parenthesis: =FILTER(TSales, --(TSales[Date]>=E2) * --(TSales[Date]=E2) and press F9, it will show 1's and 0's instead of True and False.
@@largpack Imagine that you have hundreds of cells to scan through, TRUE/FALSE take up 4 characters each, whereas 0/1 are single characters. The only benefit is ease of viewing.
Bro, how do I create that drop-down list with departmental names the way she has done it. Each department selected has its own sets of data. Kindly help.
of course that would be a man trying to "better explain" or to add something...you guys are so tiring. When it's a man saying shit you are there to give them compliments for nothing. Afffff
Thanks Leila! You have an incredible talent for explaining things, it only really shows how good you are at it that you can an explain it in such a simple way
This doesn't yet seem to be available on the Current Channel for Microsoft 365 (Version 2212). Hope people on the Preview and Beta Channels are getting to experience it!
Great video Leila! 👏With the interactive chart, I am running in to this problem: the chart area is only grabbing the number of rows and columns for the first department I had selected when I inserted the chart. Example, if finance was active with 4 rows when I insert the chart, when I select another department say marketing with 7 rows, I am only getting 4 rows still for the chart area. How can I fix this without having to manually adjust the chart area each time? Thanks
me too! I have a dynamic array, and I have 365, but it isn't auto adjusting the array size like it is in her video. just keeps whatever the size was when I first made the chart from the array
Hey Leila! Your trick kinda worked for me but there is an issue in this trick, my data has around 5000 cells in which half of them are non-empty & half cells are needed to be filled over time, so half of the cells in 5000 are still empty. Is there any way to auto adjust the chart length along x-axis? Please help me Leila.
Hi, thanks for the lovely tutorial. However, while using the same in my excel I observed that the chart data source is not dynamic and is static. How can we make it dynamic?
Hello Leila. Been watching your tutorials for years and have built many dashboards from these lessons. Using Office365 but when attempting to recreate the same dynamic graph the chart range does not adjust when entering new date ranges. The filter command works just find but the chart stays stagnant. Any ideas what could be causing this issues? Thank you.
Thank Leila, nice to see the new FILTER function in action. But do not forget the old slicers in a table: They have the same effect and are a lot easier. To harvast the selected value of the slicer and put this in a dynamic title is however another story, that is not so easy...
Hi Bart. True. Table slicers are great too - specially if we can directly use the table results in the chart and don't need to do any extra calculations.
Your videos are always astonished helpful, and your standards for understanding and explanation it's the most impressive I saw in my thousands of times on UA-cam, I follow many creator contents in many areas of tech, but your videos it's always highly efficient and boosts my productivity a lot, thanx for all your help.
How have I missed F9 for debugging formulas? That's a juicy tidbit I'll put right to work. As for the filter function, I like to use named ranges to name criteria like DateFrom and DateTo. This makes the formulas a bit more readable.
Leila, I greatly appreciate all that you do! I need your help though please. I have a table that has Failure ID type down the left in column A, and years (2016-2023) across the top (column B:I) for counting the number of Failures by year. I think this is the perfect example for the Dashboard Trick, however I cannot figure out how to get this to work as shown. I need to be able to filter by multiple Failure types in column A and a range of years selected by drop downs and have it update in a dynamic table. Please help!
Awesome tip! So simple. I found out that this also works very well with Google Sheets. The modified formula I used was =filter(B2:C, A2:A=F1). F1 is the cell with the dropdown.
Ok, I have duplicated your example verbatim and still show empty areas in my chart when moving from multiple selection to few. This is so very frustrating. I've done absolutely everything you presented here.
I've finally figured it out. Table is necessary or this simply does not work. I don't think if I recall much emphasis was placed on that one area which absolutely makes the entire exercise pointless without. Perhaps I wasn't paying close enough attention. Either way it now works perfectly
Thanks, Leila.. These are fantastic features and will be put to use in our workflows. The only disadvantage is that users need to be mindful when sharing files with Non MS365 users. Great video...
Thanks! Initially I was wondering, how did you get the drop down from 'Department = Finance' in E2 and F2? I manage to find it out. Using 'Data Validation' List option. ua-cam.com/video/boQ5p8r63yg/v-deo.html
So helpful!!!! I could watch (and I do) Leila's videos for hours on end. I tried this with column chart that has multiple series, ie. still a single spill area but more than 2 columns, but it does not behave dynamically as it does with only a single series. Please show us how to do this with multiple series! I also need microsoft to get on board with error bars in excel - imagine if we could apply this concept to error bars as well, my life would be complete (specifically for instances with multiple series)
Useful ..simple to follow video...thanks so much😊 just 1 help - somehow my graphs are not getting updated when I am adding more data in the table..plz help understand
Hey Leila, can you show us how to combine both filters you have created in this tutorial? For example to filter on deparment and the date at the same time in the dynamic charts?
Hi Leila, i'm just creating chart based on filter like what you did in the video but mine are not working. I also using my office 365 work account. Can you please tell me what is wrong with mine?
Hi Leila, my name is Duncan and I really like the high quality of your tutorials. I am planning to start my UA-cam Chanel soon, can you please tell me the software you use for your tutorials.
Appreciate the video! I'm glad to know I can make a chart off dynamic ranges without the fixed range to worry about. One thing I kept hearing you say that I'm not sure is true is that you have to make you array source a table.. you can filter out blanks easily with your FILTER function by just declaring a column that should never be null is not = "" For some reason I am such an anti-table person. I feel like it restricts me and I cannot stand the cell references when making formulas. It makes me feel I'm not in control or can validate what I'm doing. Although, I cant make a great defense against NOT using tables, maybe I'm just spiteful that excel still cannot match Sheet's dynamic capabilities. This is a good step forward... especially now I'm forced to use MS environment at my new job.
This video is really helpful! I was trying something similar but a little bit expanded version. I have the date column and some more columns for different tasks to repot their volumes. Ex: task 1, task 2, task 3,,,. I want to create dynamic charts for each task type with month in the x axis , volumes in y axis and years as legends. Could you please advise how to do that?
Hi, need help from community, why won't this work? =sumif(subtotal(9,H22:H73),">0") Trying to find values greater than 0 in filtered range H22:H73. are there other options?
Hello Leila, amazing video but I don’t have FILTER formula is it because I’m running on “Microsoft Office Professional Plus 2019” 1:57 . Would like to use this formula for my dashboard. Do let me know if there’s a workaround
Unfortunately, it does not work in my case because I created my dataset from different tables. So, although I named a range, if I go to ws2!A1 = ws1!myRange, Excel says it will convert the spilled formula to values. After that, the table becomes static. Actually, I think tables cannot be dynamic, can they? I was so hopeful when I saw the title of the video 😭! Very good video though!
I don't know but I've recently found out when you add up certain number series 2 different ways (ex- negative ones fist & then positive ones... Positive ones 1st and then negative ones) you get 2 different answers... One answer is zeros other one is not zero (0.00000000000344...) can't wrap my head around it.. what's up with that? Is that a bug?
What about if you are using formulas to insert the data? I have 100 rows of data currently. all with formula in them. Lets say only 50 are currently displaying anything and the rest are all blank. How do I create a graph that will only show the 50 populated values? Then if the data updates and I end up with 60 rows, I want the table to obviously show those additional 10 rows. and the same if the number of rows displaying values goes down. If it helps, in my example there will be no random blank rows within my data set, the first row will always have data and all data will be presented from row 1 down. All blank rows are at the end of my dataset.
This update solves precisely the problem I am experiencing. Unfortunately, my employer does not have Microsoft 365. Could you give more information on how to accomplish this same result in excel 2016?
Your videos are absolutely amazing. Straight and to the point, but detailed enough not to leave less knowledgeable people behind. I'm addicted to all these new (to me) capabilities in excel. Thank you!!!
@leilaGharani: The actual solution is entirely opposite to what you are describing above. If dept has different # of entries, then white space do appear even in Excel 365. what is the need to show a wrong solution?
I've spent the last 2 days banging my head against a keyboard. All the advise online says to save that filter formula output as a Named Range and put the name in the Chart. BUT the chart would then save the hardcoded cells and break if my series changed. All i had to do was click on the cell with the filter formula. THANK YOU.
So I'm a bit frustrated that this is the first time that I have tried to replicate something you've done and it's not working. I am referencing a spilled formula, but it's transposed, and being displayed across a row rather than a column. Also, the Spill formula references a range of cells NOT in a table, but it's set up to still add additional values over time. I have the spill range selected when creating a chart, and it works, but when I test the dynamic adding/removing of values on the chart's access, it doesn't update even though the spill formula shows the changed range. Any ideas why? I am using a chart that combines vertical bars & lines. Any clue what it is that I am doing that's causing the issue? Is it the transposed range reference? Is it the lack of a table? Is it the Chart combo type? FYI, I have the Office 365 Extended license.
Do you have a sample that has more variables to consider? I did use that for a 10-column table it doesn't work, or is there another way that you might suggest? Also, I'm linking my dynamic table from a different worksheet.
Thank you Leila for this amazing video but unfortunately despite my workplace has Microsoft 365 and that I built the same dynamic range as you suggested, the chart created based on the data does not change like a pivot chart 😕 Any idea please? Thanks
Hi Leila, I followed exactly what you did step by step, but my chart range didn’t update automatically like yours. Not sure what I did wrong. Please help!
Hi. Yet another amazing video, thanks so much. Frustratingly I can't get this to work. I have 365 Monthly Enterprise Channel version 2302 - is this the reason? Or is there some setting that I need to change?
Remember: Nothing is stopping you from using a filter function as a named range -- You can also use external connections within a named range! Mix in the FILTER(FILTER,{1,0,1,0,1,0-esque array} trick and now you can pick and choose data columns from external sources; have fun mixing external data sources and tons of criteria filters that help create super dynamic charts 🥳
Hello I follow with interest your many videos that I find very well done, however concerning this video "Effortlessly Create Dynamic" I have an example in which although having followed to the letter the procedure described in your video the update of the dynamic graph is not done correctly. I think I have tried everything but I do not understand the reason. If you are interested I can send you the file. If so, how can I do this? This is a file where we use the type of stock market data available in Excel with update every 15 min. it can therefore interest many users.
I find only certain charts to work dynamically on the x-axis. However i am not filtering from a table. I had to use formulas to generate a list and I cannot create a table due to ‘overflow’. Any tips?
Followed your advice and everything works as it should, the Filter function returns the correct values, however, my chart doesn’t update regardless of what I do. Thanks for all your advice, the video tutorials are easy to follow.
Hi Leila, my chart is not dynamic. I followed your instructions but it does not work. I am confused. I recreated it again and it still isn't dynamic. I recreated your sample too, but my chart isn't also dynamic.
Hi Leila! Whenever I change my date range like increasing it, my chart doesn’t update its range to include the new data. Do you know. What could be causing this?
Wow that's incredible. And anther somewhat complex function taught so even I can understand it. I have an option trading spreadsheet I've been tinkering with and there are a few graphs and they take up so much space being separate. This can help declutter my workbook. Can someone help me out because I don't even know if this would be grouping or subtotals or something different. Each transaction I'm tracking can be made up from either 1 to 4 detail rows. This makes creating a chart a little difficult but using pivot tables has made it a bit easier. But still I have 1 to 4 detail rows I want to create on transaction row and then chart from there. If someone knows what that might be called that would be a help.
Am I the only one who literally is writing excel formulas in his sleep? I've been dreaming of formula writing the past few weeks. It's really weird but stimulating in a very odd way. Just curious if I'm alone in this weird new thing?
Excellent video! You always do a wonderful job. I have learned so much from you and have always been impressed with your knowledge but also your effective style of teaching. Thank you again!
Thanks Leila, how would it work if value was a sum meaning it does not exist in the table so it would not return with the spill. For example you used a unique and filter function to produce the list and the value is calculated if a value is returned. In this scenario would do you create a dynamic chart range. Thank you
Question: What if your table that you plan to filter has 10 columns and you want to filter out Column's 2 and 5. Can you still effectively use this filter function and get results?
This isn’t working for me- I follow along up to 3:00 mark but after I insert a column chart and then change the source table via a different selection, the x axis is just exhibiting the same behavior as at the beginning of the video 🤔
how do you have a bar chart showing 2 colors on one bar (combined costs from 2 sources) beside another single color bar (existing budget),,,, we ended up having 2 different color bars and just saying combined costs for the smaller bar and budget for the 2nd higher bar side x side on the graph.
The TRUE/FALSE logic behind the * in the FILTER function seems to be consistent with the SUMPRODUCT formula logic. Anyway, this is a nice improvement on creating dynamic name ranges using OFFSET. Thanks, Leila!
There "Youth Policy" is to leverage their pedigree as a European Superpower and scoop up young talented players nurtured by other teams youth academy's. They then put them into thire academy or loan them out for other teams to complete the nurturing process. I can't remember the last time that a Real Madrid player came through the academy and plays full time in the main team. So yeah great "Youth Policy" 🤷
for some reason this does not work for me. Using Excel 365. Get the spilled range and when I insert a chart, the rows and columns the spilled range first spilled into gets hard coded into the data series. So my selected data will always be the 1st spilled range and leave a white space or doesn't include a larger spill.
Hi there, if I have a waterfall chart and a dynamic array, is it possible to make the graph recognize the totals automatically? Because evertrime the range changes, it messes up which cells the graph thinks is the total
When I click to add the chart it doesn't automatically adjust to all of the spill data from my filter function, it is only showing the data within the cells that populated based on my first drop down selection. So for example if my first drop down selection had 3 rows of data and then I select a different drop down option that contains 6 rows of data, the chart will only populate 3 rows. Any suggestions as to what the issue is?
I love this feature but I’ve noticed when I create a chart with the dynamic info and link my chart into a PowerPoint presentation, the PowerPoint presentation still shows the empty space, and not the clean look as seen in excel. I’m trying to create a file with multiple reports needed for a presentation and our team will use the same presentation, we will only make the changes in the excel file by selecting from our drop downs, but it’s not working.
Hi Leila. Your videos are an inspiration to me. Thanks for this free and amazing content you're producing! I would be thrilled to cooperate in the future!
Can't wait for this feature to arrive. I tried it just now and while the graph "sees" the full range due to the #, it doesn't update the range when the #-range gets smaller or bigger.
What if you have additional columns you have used on the Filtered data (you have added a few extra columns next to the filtered data) and you want to plot a combination of these values?
¡Excelente Explicación!...I will use it in a project expenses table. I gotta filter "supplier" -"sponsor" - "materials" - "equipment" and "workforce".... ¡Gracias por tu explicación!. Me gusta como lo haces simplificando recursos y bien explicado.
IF THIS IS NOT WORKING FOR YOU HERE IS WHAT I HAVE FOUND. If the spill formula at any point generates an error (#CALC) it seems to throw off the dynamic chart. You can use an "IFERROR" and force at least one row of data. Also, if the selection of the X axis is modified as the result of even a simple VBA code running it will cause the chart to not autosize 'down' on the x axis until the number of x axis values matches or exceeds the currently displayed chart. I copied the example from the video and added a macro with this single line of code: Range("F2").Value = "Procurement" and that messed up the dynamic chart working until I selected Finance again. A 'fix' is if you turn 'calculations options' to Manual, either with the ribbon or in the VBA code and then execute the previous VBA command and then using the ribbon or f9 key do a manual calculation it seems to 'work'. I tried VBA code that set calculation back to automatic as well as the 'calculate' function but neither worked. Using the ribbon to change back to 'Automatic' also didn't work.
I resolved it by splitting each column into its own series filter, and then naming the cell with the filter in and making the reference spill with #. Then edited the graph to refer to each of these spilled references, and now it works flawlessly. It's basically in this video of Leila's here: ua-cam.com/video/1t4NVRlH_d4/v-deo.html. Will always work then regardless of CALC errors or not.
Grab the file I used in the video from here 👉 pages.xelplus.com/chart-dynamic-file
The chart range does not dynamically change in the downloaded file the way it looks like it does in your video. What's up with that!? This is what I am really looking for. Please help!
I know I’m not might not gonna use this one, or some other tutorials, but each and every time Leila posts a new video, I HAVE TO watch it. She makes everything so intelligible and so pleasant to watch.
As someone who's done a bunch of analysis work over time, you never know what of this training will benefit you in the future. As such, my approach is similar to yours, I watch all these videos and absorb as much as possible with the understanding you can never know too much.
Amen!!!!!!! I completely agree!!!
Quick tip: @7:39 you can use a little trick to shows "1" for "TRUE" and "0" for "FALSE." Its done by adding two consecutive minus signs outside the parenthesis:
=FILTER(TSales, --(TSales[Date]>=E2) * --(TSales[Date]=E2) and press F9, it will show 1's and 0's instead of True and False.
what's the benefit?
@@largpack Imagine that you have hundreds of cells to scan through, TRUE/FALSE take up 4 characters each, whereas 0/1 are single characters. The only benefit is ease of viewing.
Thank you for your new trick
Bro, how do I create that drop-down list with departmental names the way she has done it. Each department selected has its own sets of data.
Kindly help.
of course that would be a man trying to "better explain" or to add something...you guys are so tiring. When it's a man saying shit you are there to give them compliments for nothing. Afffff
Thanks Leila! You have an incredible talent for explaining things, it only really shows how good you are at it that you can an explain it in such a simple way
Thank you so much!
This doesn't yet seem to be available on the Current Channel for Microsoft 365 (Version 2212). Hope people on the Preview and Beta Channels are getting to experience it!
Did you try manually checking for updates?
Great video Leila! 👏With the interactive chart, I am running in to this problem: the chart area is only grabbing the number of rows and columns for the first department I had selected when I inserted the chart. Example, if finance was active with 4 rows when I insert the chart, when I select another department say marketing with 7 rows, I am only getting 4 rows still for the chart area. How can I fix this without having to manually adjust the chart area each time? Thanks
me too! I have a dynamic array, and I have 365, but it isn't auto adjusting the array size like it is in her video. just keeps whatever the size was when I first made the chart from the array
I have the same issue here as well
Hey Leila! Your trick kinda worked for me but there is an issue in this trick, my data has around 5000 cells in which half of them are non-empty & half cells are needed to be filled over time, so half of the cells in 5000 are still empty. Is there any way to auto adjust the chart length along x-axis? Please help me Leila.
Hi, thanks for the lovely tutorial. However, while using the same in my excel I observed that the chart data source is not dynamic and is static. How can we make it dynamic?
Hello Leila. Been watching your tutorials for years and have built many dashboards from these lessons. Using Office365 but when attempting to recreate the same dynamic graph the chart range does not adjust when entering new date ranges. The filter command works just find but the chart stays stagnant. Any ideas what could be causing this issues? Thank you.
It is a great video as always. It would be great if you could include the same result with the name manager trick for non 365 users.
Thank Leila, nice to see the new FILTER function in action. But do not forget the old slicers in a table: They have the same effect and are a lot easier. To harvast the selected value of the slicer and put this in a dynamic title is however another story, that is not so easy...
Hi Bart. True. Table slicers are great too - specially if we can directly use the table results in the chart and don't need to do any extra calculations.
Your videos are always astonished helpful, and your standards for understanding and explanation it's the most impressive I saw in my thousands of times on UA-cam, I follow many creator contents in many areas of tech, but your videos it's always highly efficient and boosts my productivity a lot, thanx for all your help.
Wow, thank you! Very happy to hear that Thiago.
How have I missed F9 for debugging formulas? That's a juicy tidbit I'll put right to work. As for the filter function, I like to use named ranges to name criteria like DateFrom and DateTo. This makes the formulas a bit more readable.
Leila I tried all that you explained in the video but there is space in the charts when changing the drop down options, any reason why?
Leila, I greatly appreciate all that you do! I need your help though please. I have a table that has Failure ID type down the left in column A, and years (2016-2023) across the top (column B:I) for counting the number of Failures by year. I think this is the perfect example for the Dashboard Trick, however I cannot figure out how to get this to work as shown. I need to be able to filter by multiple Failure types in column A and a range of years selected by drop downs and have it update in a dynamic table. Please help!
Awesome tip! So simple.
I found out that this also works very well with Google Sheets. The modified formula I used was =filter(B2:C, A2:A=F1). F1 is the cell with the dropdown.
Good stuff!
Ok, I have duplicated your example verbatim and still show empty areas in my chart when moving from multiple selection to few. This is so very frustrating. I've done absolutely everything you presented here.
I've finally figured it out. Table is necessary or this simply does not work. I don't think if I recall much emphasis was placed on that one area which absolutely makes the entire exercise pointless without.
Perhaps I wasn't paying close enough attention. Either way it now works perfectly
Please make video, how to make such videos! :)
Thanks, Leila.. These are fantastic features and will be put to use in our workflows. The only disadvantage is that users need to be mindful when sharing files with Non MS365 users. Great video...
Glad it was helpful!
Thanks! Initially I was wondering, how did you get the drop down from 'Department = Finance' in E2 and F2? I manage to find it out. Using 'Data Validation' List option. ua-cam.com/video/boQ5p8r63yg/v-deo.html
I swear you're getting me promotions at work and helping me get my projects so much faster!
So helpful!!!! I could watch (and I do) Leila's videos for hours on end. I tried this with column chart that has multiple series, ie. still a single spill area but more than 2 columns, but it does not behave dynamically as it does with only a single series. Please show us how to do this with multiple series!
I also need microsoft to get on board with error bars in excel - imagine if we could apply this concept to error bars as well, my life would be complete (specifically for instances with multiple series)
Sending ❤️ to Leila. Thanks for your amazing tutorials. Wishing you a lot of more subscribers for 2023. Happy New Year 🎉🎉
Useful ..simple to follow video...thanks so much😊 just 1 help - somehow my graphs are not getting updated when I am adding more data in the table..plz help understand
Hey Leila, can you show us how to combine both filters you have created in this tutorial? For example to filter on deparment and the date at the same time in the dynamic charts?
You would use the format of the second example and multiply the department condition in the parentheses.
Hi Leila, i'm just creating chart based on filter like what you did in the video but mine are not working. I also using my office 365 work account. Can you please tell me what is wrong with mine?
It is great! However, I need to ask if I can use AND /OR/NOT in FILTER function in Excel?
Hi Leila, my name is Duncan and I really like the high quality of your tutorials. I am planning to start my UA-cam Chanel soon, can you please tell me the software you use for your tutorials.
You can check out my gear here: www.xelplus.com/resources/#gear
Thank you so much Leila
Appreciate the video! I'm glad to know I can make a chart off dynamic ranges without the fixed range to worry about. One thing I kept hearing you say that I'm not sure is true is that you have to make you array source a table.. you can filter out blanks easily with your FILTER function by just declaring a column that should never be null is not = ""
For some reason I am such an anti-table person. I feel like it restricts me and I cannot stand the cell references when making formulas. It makes me feel I'm not in control or can validate what I'm doing. Although, I cant make a great defense against NOT using tables, maybe I'm just spiteful that excel still cannot match Sheet's dynamic capabilities. This is a good step forward... especially now I'm forced to use MS environment at my new job.
This video is really helpful! I was trying something similar but a little bit expanded version. I have the date column and some more columns for different tasks to repot their volumes. Ex: task 1, task 2, task 3,,,. I want to create dynamic charts for each task type with month in the x axis , volumes in y axis and years as legends. Could you please advise how to do that?
Hi, need help from community, why won't this work? =sumif(subtotal(9,H22:H73),">0") Trying to find values greater than 0 in filtered range H22:H73. are there other options?
Hello Leila, amazing video but I don’t have FILTER formula is it because I’m running on “Microsoft Office Professional Plus 2019” 1:57 . Would like to use this formula for my dashboard. Do let me know if there’s a workaround
Unfortunately, it does not work in my case because I created my dataset from different tables. So, although I named a range, if I go to ws2!A1 = ws1!myRange, Excel says it will convert the spilled formula to values. After that, the table becomes static. Actually, I think tables cannot be dynamic, can they?
I was so hopeful when I saw the title of the video 😭! Very good video though!
Thank you sooo much Leila, this is what I was searching for the whole month.😀
I don't know but I've recently found out when you add up certain number series 2 different ways (ex- negative ones fist & then positive ones... Positive ones 1st and then negative ones) you get 2 different answers... One answer is zeros other one is not zero (0.00000000000344...) can't wrap my head around it.. what's up with that? Is that a bug?
What about if you are using formulas to insert the data? I have 100 rows of data currently. all with formula in them. Lets say only 50 are currently displaying anything and the rest are all blank. How do I create a graph that will only show the 50 populated values? Then if the data updates and I end up with 60 rows, I want the table to obviously show those additional 10 rows. and the same if the number of rows displaying values goes down.
If it helps, in my example there will be no random blank rows within my data set, the first row will always have data and all data will be presented from row 1 down. All blank rows are at the end of my dataset.
good to know, very helpful will make amazing dashboards, good presentation
This update solves precisely the problem I am experiencing. Unfortunately, my employer does not have Microsoft 365. Could you give more information on how to accomplish this same result in excel 2016?
Your videos are absolutely amazing. Straight and to the point, but detailed enough not to leave less knowledgeable people behind. I'm addicted to all these new (to me) capabilities in excel. Thank you!!!
Use the TEXT function for formatting? Drives me crazy the AND function can’t be used within some formulas.
My chart range doesn’t update with the table range. Can anyone advise why that elements not working? I am on Version 2405.
@leilaGharani: The actual solution is entirely opposite to what you are describing above. If dept has different # of entries, then white space do appear even in Excel 365. what is the need to show a wrong solution?
I've spent the last 2 days banging my head against a keyboard. All the advise online says to save that filter formula output as a Named Range and put the name in the Chart. BUT the chart would then save the hardcoded cells and break if my series changed. All i had to do was click on the cell with the filter formula. THANK YOU.
So I'm a bit frustrated that this is the first time that I have tried to replicate something you've done and it's not working. I am referencing a spilled formula, but it's transposed, and being displayed across a row rather than a column. Also, the Spill formula references a range of cells NOT in a table, but it's set up to still add additional values over time. I have the spill range selected when creating a chart, and it works, but when I test the dynamic adding/removing of values on the chart's access, it doesn't update even though the spill formula shows the changed range. Any ideas why? I am using a chart that combines vertical bars & lines. Any clue what it is that I am doing that's causing the issue? Is it the transposed range reference? Is it the lack of a table? Is it the Chart combo type? FYI, I have the Office 365 Extended license.
Do you have a sample that has more variables to consider? I did use that for a 10-column table it doesn't work, or is there another way that you might suggest?
Also, I'm linking my dynamic table from a different worksheet.
So you use * instead of AND.....but did you know that you can use + instead of OR?.....and then with parentheses you can combine these.
Thank you Leila for this amazing video but unfortunately despite my workplace has Microsoft 365 and that I built the same dynamic range as you suggested, the chart created based on the data does not change like a pivot chart 😕 Any idea please? Thanks
In my excel it doesn’t work for this function 😢but it only has FILTERXML
Hi Leila, I followed exactly what you did step by step, but my chart range didn’t update automatically like yours. Not sure what I did wrong. Please help!
Hi. Yet another amazing video, thanks so much. Frustratingly I can't get this to work. I have 365 Monthly Enterprise Channel version 2302 - is this the reason? Or is there some setting that I need to change?
As always, brilliant 👏, very intuitive video
Remember: Nothing is stopping you from using a filter function as a named range -- You can also use external connections within a named range!
Mix in the FILTER(FILTER,{1,0,1,0,1,0-esque array} trick and now you can pick and choose data columns from external sources; have fun mixing external data sources and tons of criteria filters that help create super dynamic charts 🥳
The CHOOSECOLS function is a better alternative.
Hello
I follow with interest your many videos that I find very well done, however concerning this video "Effortlessly Create Dynamic" I have an example in which although having followed to the letter the procedure described in your video the update of the dynamic graph is not done correctly. I think I have tried everything but I do not understand the reason. If you are interested I can send you the file.
If so, how can I do this?
This is a file where we use the type of stock market data available in Excel with update every 15 min. it can therefore interest many users.
Salaam,
How could I stop the extremally annoying windows updates, windows 10 and 11? Thank you
I find only certain charts to work dynamically on the x-axis. However i am not filtering from a table. I had to use formulas to generate a list and I cannot create a table due to ‘overflow’. Any tips?
Followed your advice and everything works as it should, the Filter function returns the correct values, however, my chart doesn’t update regardless of what I do. Thanks for all your advice, the video tutorials are easy to follow.
Hi everyone. I am new to these Dynamic Excel functions. I would like to know whether the table in this video is considered a pivot table per se?
Hi Leila, my chart is not dynamic.
I followed your instructions but it does not work. I am confused. I recreated it again and it still isn't dynamic. I recreated your sample too, but my chart isn't also dynamic.
Hi Leila!
Whenever I change my date range like increasing it, my chart doesn’t update its range to include the new data. Do you know. What could be causing this?
Wow that's incredible. And anther somewhat complex function taught so even I can understand it. I have an option trading spreadsheet I've been tinkering with and there are a few graphs and they take up so much space being separate. This can help declutter my workbook.
Can someone help me out because I don't even know if this would be grouping or subtotals or something different. Each transaction I'm tracking can be made up from either 1 to 4 detail rows. This makes creating a chart a little difficult but using pivot tables has made it a bit easier. But still I have 1 to 4 detail rows I want to create on transaction row and then chart from there. If someone knows what that might be called that would be a help.
Am I the only one who literally is writing excel formulas in his sleep? I've been dreaming of formula writing the past few weeks. It's really weird but stimulating in a very odd way.
Just curious if I'm alone in this weird new thing?
Excellent video! You always do a wonderful job. I have learned so much from you and have always been impressed with your knowledge but also your effective style of teaching. Thank you again!
This isn't working for me, I still get dead space when switching departments.
Thanks Leila, how would it work if value was a sum meaning it does not exist in the table so it would not return with the spill. For example you used a unique and filter function to produce the list and the value is calculated if a value is returned. In this scenario would do you create a dynamic chart range. Thank you
That is a beautiful thing! I just love the new filter function. Thank you Leila.
Glad you like it!
Question: What if your table that you plan to filter has 10 columns and you want to filter out Column's 2 and 5. Can you still effectively use this filter function and get results?
Hi dear,
Is it possible to print only I page in landscape and other portrait page via excel?? Please assist.. Thank you - Tejas Joshi - India
This isn’t working for me- I follow along up to 3:00 mark but after I insert a column chart and then change the source table via a different selection, the x axis is just exhibiting the same behavior as at the beginning of the video 🤔
how do you have a bar chart showing 2 colors on one bar (combined costs from 2 sources) beside another single color bar (existing budget),,,, we ended up having 2 different color bars and just saying combined costs for the smaller bar and budget for the 2nd higher bar side x side on the graph.
Thanks Leila. But when I create a chart from the spill cell, it doesn’t seems to be dynamic. How can I solve this?
The TRUE/FALSE logic behind the * in the FILTER function seems to be consistent with the SUMPRODUCT formula logic.
Anyway, this is a nice improvement on creating dynamic name ranges using OFFSET. Thanks, Leila!
Hi Steve. Yes. It's similar to SUMPRODUCT.
Hi Leila. Is this released? I have tried several time but still not working, very strange if it's already in O365.
Please help me how to set a classification in excel using VBA. If any help me with the code it's very thankful to you 🙂
There "Youth Policy" is to leverage their pedigree as a European Superpower and scoop up young talented players nurtured by other teams youth academy's. They then put them into thire academy or loan them out for other teams to complete the nurturing process.
I can't remember the last time that a Real Madrid player came through the academy and plays full time in the main team.
So yeah great "Youth Policy" 🤷
Can this be done in Excel 2021 for desktop without 365?
for some reason this does not work for me. Using Excel 365. Get the spilled range and when I insert a chart, the rows and columns the spilled range first spilled into gets hard coded into the data series. So my selected data will always be the 1st spilled range and leave a white space or doesn't include a larger spill.
From which version of M365 does this work? I have 2212 but it is not there.
Hi there, if I have a waterfall chart and a dynamic array, is it possible to make the graph recognize the totals automatically? Because evertrime the range changes, it messes up which cells the graph thinks is the total
Hi leila, thanks for the video. This is not working on multiple conditions using filter function. Any suggestions please?
When I click to add the chart it doesn't automatically adjust to all of the spill data from my filter function, it is only showing the data within the cells that populated based on my first drop down selection. So for example if my first drop down selection had 3 rows of data and then I select a different drop down option that contains 6 rows of data, the chart will only populate 3 rows. Any suggestions as to what the issue is?
When I add new data (such as a new product) to the table, it shows up in my summary table, but my chart is not automatically updated.
can you do this if you have 12 monthly figures in 12 columns rather than 1 yearly column?
I love this feature but I’ve noticed when I create a chart with the dynamic info and link my chart into a PowerPoint presentation, the PowerPoint presentation still shows the empty space, and not the clean look as seen in excel. I’m trying to create a file with multiple reports needed for a presentation and our team will use the same presentation, we will only make the changes in the excel file by selecting from our drop downs, but it’s not working.
Hi, I am using Office 2021 and I am not getting dynamic range for the chart. Please help me
For the second way of filtering, I have a multi-column table but I need only a few in my chart. how do I do it?
Hi Leila. Your videos are an inspiration to me. Thanks for this free and amazing content you're producing! I would be thrilled to cooperate in the future!
Hi, nice feature, though I've just tried to apply it and it fails if there is more than 1 column to plot.
Hi Leila ,
Kindly do markup & margin on excel sheet video.
Hi. Can you please make a similar video where there are multiple salary values?? Let's say year 1, year 2, and year 3...
Any possibility to make this work for pie/donut charts?
Thanks Leila, does this work for charts with multiple Series?
Can't wait for this feature to arrive. I tried it just now and while the graph "sees" the full range due to the #, it doesn't update the range when the #-range gets smaller or bigger.
Hey Leila when you are going to start power BI ... waiting for your course on Udemy .
What if you have additional columns you have used on the Filtered data (you have added a few extra columns next to the filtered data) and you want to plot a combination of these values?
Hello. May I ask, do I need to use microsoft 365 to use this functions and tools??
Hi everyone , how do I create that departmental drop down list, Each department containing different data?
After spending a whole day messing around with the offset function you show me this easy solution...
Is the reason not to use the AND function? It would seem more intuitive for debugging.
¡Excelente Explicación!...I will use it in a project expenses table. I gotta filter "supplier" -"sponsor" - "materials" - "equipment" and "workforce".... ¡Gracias por tu explicación!. Me gusta como lo haces simplificando recursos y bien explicado.
IF THIS IS NOT WORKING FOR YOU HERE IS WHAT I HAVE FOUND. If the spill formula at any point generates an error (#CALC) it seems to throw off the dynamic chart. You can use an "IFERROR" and force at least one row of data. Also, if the selection of the X axis is modified as the result of even a simple VBA code running it will cause the chart to not autosize 'down' on the x axis until the number of x axis values matches or exceeds the currently displayed chart. I copied the example from the video and added a macro with this single line of code: Range("F2").Value = "Procurement" and that messed up the dynamic chart working until I selected Finance again. A 'fix' is if you turn 'calculations options' to Manual, either with the ribbon or in the VBA code and then execute the previous VBA command and then using the ribbon or f9 key do a manual calculation it seems to 'work'. I tried VBA code that set calculation back to automatic as well as the 'calculate' function but neither worked. Using the ribbon to change back to 'Automatic' also didn't work.
I resolved it by splitting each column into its own series filter, and then naming the cell with the filter in and making the reference spill with #. Then edited the graph to refer to each of these spilled references, and now it works flawlessly. It's basically in this video of Leila's here: ua-cam.com/video/1t4NVRlH_d4/v-deo.html. Will always work then regardless of CALC errors or not.