Automatically Skip Blanks in Excel Charts with Formulas (ignore gaps in Excel chart axis)

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/remove-gaps-in-charts-file

[@dlswa0310]

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]

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]

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

[@gob2004]

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.

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

[@Be_Gee]

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.

[@LeilaGharani]

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.

[@Al_hagu_hagbhar]

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]

Great to hear!

[@anette789]

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

[@kuuuyajim]

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

[@asadrauf5902]

Excellent!!!...you made my day. Thank you Leila. Keep on helping people. God bless you.

[@darielanria9493]

Leila you are not from this world. Legend!!

[@ssharrison5]

Just what I wanted, You're my first stop Guru

[@tonyc4669]

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

[@jesuseduardobittersuarez7517]

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

[@bocadillarica]

You are absolutely amazing!!!! Your step by step instruction has brought back my sanity.

[@dilipkjena]

Your channel is a blessing. It is helping me in my presentations in my PhD. Thank you, Leila.

[@LeilaGharani]

Wonderful!

[@guidobusuttil4659]

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.

[@rishabgaba]

blew my mind. Exactly what I was looking for. Thank you for figuring this out.

[@ijnmpi]

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]

Glad I could help!

[@toomuchsend9237]

You literally saved my life

[@excelisfun]

Thanks for the gap-less charting : )

[@LeilaGharani]

Thanks Mike :)

[@parmarboyz]

What a Beautiful Trick, Brilliant Formula and Super Awesome Video..... 1000 Likes for this Leila. Keep Up, Great Work.

[@LeilaGharani]

I'm glad you like it!

[@astepintherightdirection2725]

Forever grateful for your videos. Please keep up the good work.

[@LeilaGharani]

Thank you, I will

[@yavuzt1017]

this video is so useful to me that i have solved my sorting issues. thanks a lot....

[@LeilaGharani]

Glad it helped.

[@killermachine3143]

Please provide a video on dynamic sorting of text without using sort() or sortby()

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

[@emilycicerkofski8835]

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 :)

[@annammaheshbabu3580]

Love you Leila, you are awesome.. it is very helpful....

[@LeilaGharani]

You're so welcome!

[@olivierkandel9409]

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!

[@karencrouch5671]

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]

Thank you so much for doing this. Please continue and keep up the awesome work.

[@danielbm24]

Thanks Leila! You're the absolute best!

[@زيزوالانطاكي]

What a genius woman !!

[@LeilaGharani]

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]

What a genius woman having the brain of subtle understanding and having the heart to sharing it in simple Crystal clear communication.

[@СергійСапотюк]

Very useful. Thanks

[@Dimitronikos]

Thank you very much for your assistance!

[@orkinabdallatyf7205]

indeed more than amazing mohandesa leila and truly useful

[@atanugg9372]

Thanks, simple intuitive video ,it help me to apply.

[@LeilaGharani]

You're very welcome.

[@nishantjain2664]

when i put the range in name manager, it is not working, in your video it is only highlighting till the cell yu have values eben though in formula you have till E56, but for me it is going till 56 and including all the blank cells which is affecting my chart

[@agoesrizatp1937]

Superb! Thank you

[@Farhad4Layek]

Thanks Leila! You're the best

[@kiki8535ify]

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]

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

[@Pa1KumarSistla]

Thank you for the explanation, since there are new O365 functions, can this be done a simpler way, please

[@dr.imrankhanyousufzai4710]

Thanks a lot for the help

[@TheBatey1]

does this work with text, or formula to get form another worksheet?

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

[@eyuelgorfu9404]

You did it again. Thank you!!!

[@soniccheese01]

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]

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.

[@pooverngounden3049]

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.

[@dianasharon5647]

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!

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

[@omara4673]

Exactly what i needed! thanks

[@shayschopp]

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.

[@allanpark6046]

Thank you thank you thank you. Amazing!

[@dianavelasquez3354]

Saved my job!👌🏼🤓

[@gantulgaerdenechimeg3520]

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

[@edlewis1688]

Thanks for this video. It was a great help.

[@LeilaGharani]

I'm glad to hear that Ed.

[@NicoMellett]

This is amazing, thank you!

[@antoniobacic5674]

Can it be done with newest dates on top automatically?

[@jong7513]

Well done, Leila! Thank you, I really needed this info!

[@abdanomer]

Good index session, I like it a lot 👍🏻👍🏻👍🏼

[@LeilaGharani]

Thanks Abdelrahman.

[@settelaaymen8085]

thanks for the video but we have to add 1 to the agregate function as follows: agregate(15;3;row(X:Y);1)

[@86Maryj]

Hi instead of blanks if its text string mentioned how to exclude that string pls assist

[@LouayHomsi]

Is it possible to skip zeros too?

[@kaiser_83]

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

[@christopherrilles3106]

Thank you!!

[@njugunawanyoike146]

awesome. Keep up the great work.

[@soniccheese01]

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]

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 :)

[@alexandresantiago5499]

TY very much

[@junaidsidiq3234]

You made my day. Thanks!

[@bealovemomdad]

Thank you

[@antoinecamilleri7650]

Does it have to be so complicated!!! All i need to do is ignore 0 when i query a table

[@apurvchaurasia]

what if the my data are in text format then what can be done ?

[@rockguitarist8907]

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]

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

[@Wosso-Spiritas]

Leila - if one date is repeated the result is no longer correct

[@stiankiese6728]

Great video! I am struggling with adding two different lines within the same chart.. Do you have any workarounds to share?

[@LeilaGharani]

Do you need to break the line like in this video? kzbin.info/www/bejne/a5_JaaWrmN1rZqc

[@stiankiese6728]

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]

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

[@Christian-uz3qs7fe5g]

How do you skip columns instead of rows

[@IcePaq986]

can this also apply to Bar chart?

[@tormodsolemslupphaug1043]

Yes

[@tingpangchong437]

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]

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.

[@csabakucor9506]

amaizing and simple...jejjjjj,thank you!

[@LeilaGharani]

You're very welcome Csaba!

[@hosseinhosseinpoor4845]

thanks thanks

[@sbnoraje5799]

Great

[@MalinaC]

Leila, what microphone do you use? You sound perfect :)

[@LeilaGharani]

Thank Malina. I use Edutige lavalier mic: amzn.to/2y3Yq5y.

[@dr99]

thank's.

[@kwfong3037]

多謝！

[@LeilaGharani]

Thank you!

[@scarfacesheki]

I get an Num Error when using the Formula

[@LeilaGharani]

Try downloading the workbook (link is in the description of the video) and cross-checking with own formulas...

[@BillSzysz1]

Thanks Leila :-)

[@LeilaGharani]

Thanks Bill - you're simply great! I've added your formulas to the comments and the workbook.

[@BernardoTavora]

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

[@kebincui]

👍👍👍