Grab the file I used in the video from here 👉 pages.xelplus.com/remove-gaps-in-charts-file
@dlswa0310
2 жыл бұрын
WOW! I have been wracking my brain trying to get my chart to be dynamic. I could not have done it without your help. Look at you go this video is 5 years old and still helping people.
@johnborg6005
6 жыл бұрын
People like Mike, Bill Szysz, Bill Gelen you and all the online excel team help all of us learn more and more. A Feast of Excel.
@LeilaGharani
6 жыл бұрын
That's very kind! I'm very glad to be part of the online Excel community. I have learnt so much from Mike & Bill & other Excel gurus out there - it's a great online Excel community. I like that term: "a Feast of Excel" :)
@ssharrison5
2 жыл бұрын
Just what I wanted, You're my first stop Guru
@gob2004
4 жыл бұрын
Thanks a lot, I 'm not a native English speaker but can understand your clip because you speak clearly and slowly. Your idiom is easy to understand so I love your clip.
@LeilaGharani
7 жыл бұрын
More simpler functions specially if your category axis has dates as in video - or other numbers - just not text: For sorting of dates: =IFERROR(SMALL($A$36:$A$60,ROWS($E$36:E36)),"") For sorting of values: =IF(E36="","",LOOKUP(E36,$A$36:$B$60)) Thank you to Bill Szysz! And thank you to Mohamed: =IFERROR(AGGREGATE(15;6;($A$36:$A$60/$A$36:$A$60)*$A$36:$A$60;ROWS($A$36:A36));"") Appreciate the input from the Excel Online team for helping us all become better in Excel.
@raroboy715
Жыл бұрын
love the way you take time to explain the logic of the function and how you design the solution - you are my go to Excel expert ....
@Labirinth7
4 жыл бұрын
You are pure gold!! it has never happened that I was looking for something in excel and I haven't found a solution to it in one of your videos!
@LeilaGharani
4 жыл бұрын
Great to hear!
@darielanria9493
5 жыл бұрын
Leila you are not from this world. Legend!!
@Be_Gee
4 жыл бұрын
You are a saint. Thank you soooo much. Dealing with thousands of cells and manually filtering, and deleting would mess up the order my code, but this solves it all.
@asadrauf5902
2 жыл бұрын
Excellent!!!...you made my day. Thank you Leila. Keep on helping people. God bless you.
@anette789
2 жыл бұрын
Exactly what I needed. Your solutions have saved me a lot of time over the years and taught me many interesting tricks in Excel. Thank you!!!
@bocadillarica
3 жыл бұрын
You are absolutely amazing!!!! Your step by step instruction has brought back my sanity.
@toomuchsend9237
2 жыл бұрын
You literally saved my life
@kuuuyajim
2 жыл бұрын
For some strange reason, I couldn't get the FILTER function to work on dates. Good thing I found this video of yours. Thank you so much, Leila! 😃
@dilipkjena
3 жыл бұрын
Your channel is a blessing. It is helping me in my presentations in my PhD. Thank you, Leila.
@LeilaGharani
3 жыл бұрын
Wonderful!
@tonyc4669
3 жыл бұрын
This video needs a hashtag or other search handle for handling incongruous data. I think many people are looking for this topic. The dynamic charting is amazing and am surprised at what it can do
@excelisfun
7 жыл бұрын
Thanks for the gap-less charting : )
@LeilaGharani
7 жыл бұрын
Thanks Mike :)
@jesuseduardobittersuarez7517
3 жыл бұрын
I love you, my table had lots of blank rows but just at the end andwith the second part ot the video with the name ranges i was able to refresh my graph while ignoring all those blank rows a the end that I had to keep because my data changes
@rishabgaba
2 жыл бұрын
blew my mind. Exactly what I was looking for. Thank you for figuring this out.
@guidobusuttil4659
3 жыл бұрын
Used the formula to sort out dynamic cells for my dropdown list. It works amazingly well as my input cells are dynamic and so erratic. Full of spaces.
@astepintherightdirection2725
3 жыл бұрын
Forever grateful for your videos. Please keep up the good work.
@LeilaGharani
3 жыл бұрын
Thank you, I will
@parmarboyz
5 жыл бұрын
What a Beautiful Trick, Brilliant Formula and Super Awesome Video..... 1000 Likes for this Leila. Keep Up, Great Work.
@LeilaGharani
5 жыл бұрын
I'm glad you like it!
@ijnmpi
4 жыл бұрын
WOAH WOAH WOAH !!! I was struggling for so long to get something like this and had watched so many tutorials but wasn't working. This solved :) Thank you so so much!!
@LeilaGharani
4 жыл бұрын
Glad I could help!
@yavuzt1017
3 жыл бұрын
this video is so useful to me that i have solved my sorting issues. thanks a lot....
@LeilaGharani
3 жыл бұрын
Glad it helped.
@annammaheshbabu3580
3 жыл бұрын
Love you Leila, you are awesome.. it is very helpful....
@LeilaGharani
3 жыл бұрын
You're so welcome!
@user-lv3vo9ew2j
6 жыл бұрын
What a genius woman !!
@LeilaGharani
6 жыл бұрын
Glad you like the video. The genius ones are the ones that programmed Excel :) I'm just a user - many thanks though for the compliment :)
@s.sathiyamoorthi6634
3 жыл бұрын
What a genius woman having the brain of subtle understanding and having the heart to sharing it in simple Crystal clear communication.
@user-sn9mc7jt6n
Жыл бұрын
Very useful. Thanks
@danielbm24
4 жыл бұрын
Thanks Leila! You're the absolute best!
@orkinabdallatyf7205
4 жыл бұрын
indeed more than amazing mohandesa leila and truly useful
@Farhad4Layek
4 жыл бұрын
Thanks Leila! You're the best
@agoesrizatp1937
2 жыл бұрын
Superb! Thank you
@killermachine3143
2 жыл бұрын
Please provide a video on dynamic sorting of text without using sort() or sortby()
@atanugg9372
6 жыл бұрын
Thanks, simple intuitive video ,it help me to apply.
@LeilaGharani
6 жыл бұрын
You're very welcome.
@marz44
Жыл бұрын
Hi Leila, Thank you so much for your tutorials! I appreciate you so much. I have a question on this one though, are you able to do the same formula for a table that has labels on the left side but a 0 value on the right? Example: Cash $40 Debit Card $0 Credit Card $250 Savings $0 Please help, thank you so much!!
@Dimitronikos
3 жыл бұрын
Thank you very much for your assistance!
@dianavelasquez3354
4 жыл бұрын
Saved my job!👌🏼🤓
@dr.imrankhanyousufzai4710
3 жыл бұрын
Thanks a lot for the help
@eyuelgorfu9404
4 жыл бұрын
You did it again. Thank you!!!
@karencrouch5671
2 жыл бұрын
This works well but when the "Dates" column is not actually dates but some text that is something like 2022 Q1. My data range is including some blank cells to allow for growth then my graph ends up with a large blank area at the end of my X-axis. It did however help me remove the quarters and years that had no data so that was very helpful!
@NicholasFalter00
Жыл бұрын
Try changing the formulas to use COUNTA instead of COUNT. After you do this, it should return the number of rows that are not blank, which then when used in the same formula in the video, removes the blanks. Hope this helps!
@karencrouch5671
Жыл бұрын
@@NicholasFalter00 thanks, I'll give that a try
@davetuvolt7539
5 жыл бұрын
Thank you so much for doing this. Please continue and keep up the awesome work.
@christopherrilles3106
3 жыл бұрын
Thank you!!
@olivierkandel9409
4 жыл бұрын
First, super channel! I thought I knew excel but I discovered a lot of tricks. Question though: I'm 3 years late on that so I don't expect an answer...but I'll try. The NA trick doesn't seem to work on a Bar Chart with percentages. How can I make it work for a Bar Chart? Thank you!
@allanpark6046
4 жыл бұрын
Thank you thank you thank you. Amazing!
@edlewis1688
5 жыл бұрын
Thanks for this video. It was a great help.
@LeilaGharani
5 жыл бұрын
I'm glad to hear that Ed.
@emilycicerkofski8835
4 жыл бұрын
This was a very informative video! Thank you for sharing this with us. I am wondering if you can apply this method to a pivot table? I tried to do this but my graph is still showing the blank fields. Any help will be much appreciated :)
@alexandresantiago5499
3 жыл бұрын
TY very much
@abdanomer
7 жыл бұрын
Good index session, I like it a lot 👍🏻👍🏻👍🏼
@LeilaGharani
7 жыл бұрын
Thanks Abdelrahman.
@omara4673
4 жыл бұрын
Exactly what i needed! thanks
@bealovemomdad
3 жыл бұрын
Thank you
@njugunawanyoike146
4 жыл бұрын
awesome. Keep up the great work.
@NicoMellett
4 жыл бұрын
This is amazing, thank you!
@jong7513
5 жыл бұрын
Well done, Leila! Thank you, I really needed this info!
@Pa1KumarSistla
4 жыл бұрын
Thank you for the explanation, since there are new O365 functions, can this be done a simpler way, please
@junaidsidiq3234
6 жыл бұрын
You made my day. Thanks!
@soniccheese01
6 жыл бұрын
Hi Leila, love the video on closing the gaps. I was having a play and discovered that the formula could be shortened to this. =AGGREGATE(15,3,$A$36:$A$60,ROWS($A$36:A36)). When you test the return of the values in the array argument you get the following - {42736;42737;0;42739;42740;42741;42742;42743;0;42745;42746;42747;0;0;0;42751;42752;42753;42754;0;42756;0;0;0;0} So where the blanks are you get a zero. By using the k value increment with the ROWS($A$36:A36) , this seems to work. Pleas let me know if I have got this wrong, it seems to work okay!
@LeilaGharani
6 жыл бұрын
Hi Kevin - You're absolutely right. No need for index since we are dealing with dates here :) It only occurred to me afterwards. Thank you for sharing. It's always fun to play around - right? That's what I love with Excel - the fun doesn't end.
@gantulgaerdenechimeg3520
4 жыл бұрын
Leila, have you ever heard rose diagram (used in geology)? Please show us how to do it please. @t Thanks in advance Your big fan
@pooverngounden3049
5 жыл бұрын
Wow many thanks. I'm using this lecture for a presentation to my MD. Is it possible to create a dynamic graph that plots in the middle of the graph area? In other words I want to tell excel to plot data from a given column, however there is data in the middle of that column (column will update dynamically obviously) and I want it to plot that data only? Your response pointing me in the right direction would be deeply appreciated.
@settelaaymen8085
6 жыл бұрын
thanks for the video but we have to add 1 to the agregate function as follows: agregate(15;3;row(X:Y);1)
@kiki8535ify
6 жыл бұрын
Hi Leila Gharani, Thanks for your video. Can I ask you a more things? I'm working on a scatter chart. When the user select the categories, the the chart will show this categories's data. E.g The user selected 2 categories, but there are 10 categories in total. Then the chart is shown 10 legends and 8 legends are no need. May I know how to remove the legend label when the user doesn't select it? Many thanks.
@LeilaGharani
6 жыл бұрын
Excel by default shows the legend for the series that it's plotting, even if they are invisible. OFFSET might be an option. To basically create a data preparation table that holds the data the user selects and then to use OFFSET together with name manager to create dynamic ranges....
@TheBatey1
2 жыл бұрын
does this work with text, or formula to get form another worksheet?
@shayschopp
3 жыл бұрын
Thanks Leila. Now with the dynamic array on office 365, we could simply use the “filter” function for the data prep can’t we,? e.g., filter the date array when it is not blank.
@hosseinhosseinpoor4845
3 жыл бұрын
thanks thanks
@soniccheese01
6 жыл бұрын
Continuing the play with this just the following works too! =SMALL($A$36:$A$60,ROWS($G$36:G36)). It would appear that the SMALL function ignores the zeros produced by the gaps, whereas actual zero values would be picked up! Go figure! Hope that helps.
@LeilaGharani
6 жыл бұрын
Hi Kevin - yes very true. The fact that we have sorted dates makes this approach feasible. Something I forgot while I was filming. That's why I appreciate the comments so much :)
@milkar4135
Жыл бұрын
Hello, this is a little misleading graphics , if we are working with time series data. Fir example, if there is no production in the third month, the value should appear as zero. In this chart , if we skip the month, it may appear that there is continuous product every month
@sbnoraje5799
3 жыл бұрын
Great
@86Maryj
3 жыл бұрын
Hi instead of blanks if its text string mentioned how to exclude that string pls assist
@antoniobacic5674
Жыл бұрын
Can it be done with newest dates on top automatically?
@valeromero973
Жыл бұрын
How do you apply this if the formula I'm starting with is: If(e3=f3,c3,"") I want the above formula to skip blanks.
@LouayHomsi
Жыл бұрын
Is it possible to skip zeros too?
@dianasharon5647
4 жыл бұрын
I keep getting #VALUE! for the name manager at 17:12, and i'm following step by step. It has the result of =E(1st number):E(2nd number), then when i press enter it says #VALUE!. Help!
@kaiser_83
5 жыл бұрын
How do you manage these blanks when data is missing from a pivot table and you are creating a pivot chart? Sorry i'm just a beginner, if this is a very simple solution
@csabakucor9506
5 жыл бұрын
amaizing and simple...jejjjjj,thank you!
@LeilaGharani
5 жыл бұрын
You're very welcome Csaba!
@stiankiese6728
5 жыл бұрын
Great video! I am struggling with adding two different lines within the same chart.. Do you have any workarounds to share?
@LeilaGharani
5 жыл бұрын
Do you need to break the line like in this video? kzitem.info/news/bejne/lqScnqarnKppY3o
@stiankiese6728
5 жыл бұрын
Thanks for the quick reply. I want to be able to compare actual vs budget with a dynamic x-axis. I believe the panel charts above is a bit too complicated. Is there no way to add another series?
@stiankiese6728
5 жыл бұрын
I do have a quiet complicated list of projects with different start and end dates with monthly reporting. I want to compare actual vs budget - therefore the method above seems to suit my needs. The only issue is that I am not able to add another data series (the monthly reported budget) to the chart..
@kwfong3037
Жыл бұрын
多謝!
@LeilaGharani
Жыл бұрын
Thank you!
@apurvchaurasia
4 жыл бұрын
what if the my data are in text format then what can be done ?
@Christian-uz3qs7fe5g
5 жыл бұрын
How do you skip columns instead of rows
@antoinecamilleri7650
4 жыл бұрын
Does it have to be so complicated!!! All i need to do is ignore 0 when i query a table
@dr99
4 жыл бұрын
thank's.
@rockguitarist8907
7 жыл бұрын
What if you continue to enter cells beyond the range of the Index? This formula doesn't pick up cells exceeding the range. Is there a way, besides a table to continue expanding the Index and Count range?
@LeilaGharani
7 жыл бұрын
Not that can think of, besides dragging your formulas all the way down to the point where you think there might be numbers and then maybe include a few more cells on top.....
@IcePaq986
3 жыл бұрын
can this also apply to Bar chart?
@tormodsolemslupphaug1043
3 жыл бұрын
Yes
@tingpangchong437
6 жыл бұрын
Hi Leila, what if suppose i have 2017 actual and 2018 budget (2 category in the X axis, I wanted a line chart but the problem is, it causes a gap at dec 17 actual and jan18 budget. How do i joint them together?
@LeilaGharani
6 жыл бұрын
You have to overlap your data set for the month that's causing the gap. So for Actual series, for Jan 2018 - you need the same number as you have in your budget series for January..this causes the gap to close.
@MalinaC
7 жыл бұрын
Leila, what microphone do you use? You sound perfect :)
@LeilaGharani
7 жыл бұрын
Thank Malina. I use Edutige lavalier mic: amzn.to/2y3Yq5y.
@Wosso-Spiritas
3 жыл бұрын
Leila - if one date is repeated the result is no longer correct
@BillSzysz1
7 жыл бұрын
Thanks Leila :-)
@LeilaGharani
7 жыл бұрын
Thanks Bill - you're simply great! I've added your formulas to the comments and the workbook.
@scarfacesheki
6 жыл бұрын
I get an Num Error when using the Formula
@LeilaGharani
6 жыл бұрын
Try downloading the workbook (link is in the description of the video) and cross-checking with own formulas...
@kebincui
3 жыл бұрын
👍👍👍
@BernardoTavora
3 жыл бұрын
This solution is useless for line graphs with more than one series. Excel simply doesn’t recognise values associated with vectors set up in the Name Manager section, hence, not useful
Пікірлер: 126