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

  Рет қаралды 118,239

Leila Gharani

Leila Gharani

Күн бұрын

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
In this video I show you how to dynamically ignore blank dates and errors in charts. This trick doesn't just apply to dates, but to any data set that has gaps in it and you'd like Excel to completely ignore the gaps, even for cases where you have values in front of the gaps.
⬇️ Download the workbook here: pages.xelplus.com/remove-gaps...
Key Learning Points:
- Dynamic Formula Approach: Learn a dynamic method for ignoring blank cells and error values in both X and Y axes of Excel charts.
- Data Preparation Table: Understand how to create a dense data preparation table that filters out blanks and errors, providing clean data for charting.
- Innovative Use of Index Formula: Explore how the Index formula can efficiently return dates and numbers, filtering out unwanted cells.
- Advanced Excel Techniques: Delve into the use of advanced functions like Aggregate and Rows, and how they contribute to accurate data filtering.
- Creating Dynamic Charts: Step-by-step guide on creating dynamic charts that automatically update with your data.
- Utilizing Name Manager: Learn the crucial role of Name Manager in creating dynamic charts and how to set it up effectively.
- Practical Application and Testing: See the formulas in action with practical examples, ensuring your chart responds dynamically to changes in data.
This video shows you how to use the Aggregate functions, Index & Match and Name Manager to get the desired result automatically.
Links to related videos:
Part 1: Manual approach to removing gaps in charts: • Skip Dates in Excel Ch...
Index & Match basics: • How to use Excel Index...
Index & Match advanced: • Index Match Advanced: ...
Changing Chart Ranges with Index: • How to Create a Dynami...
★ My Online Excel Courses ► courses.xelplus.com/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

Пікірлер: 126
@LeilaGharani
@LeilaGharani 5 ай бұрын
Grab the file I used in the video from here 👉 pages.xelplus.com/remove-gaps-in-charts-file
@dlswa0310
@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
@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
@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" :)
@LeilaGharani
@LeilaGharani 6 жыл бұрын
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.
@ssharrison5
@ssharrison5 2 жыл бұрын
Just what I wanted, You're my first stop Guru
@anette789
@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!!!
@Be_Gee
@Be_Gee 3 жыл бұрын
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.
@jong7513
@jong7513 5 жыл бұрын
Well done, Leila! Thank you, I really needed this info!
@bocadillarica
@bocadillarica 3 жыл бұрын
You are absolutely amazing!!!! Your step by step instruction has brought back my sanity.
@davetuvolt7539
@davetuvolt7539 5 жыл бұрын
Thank you so much for doing this. Please continue and keep up the awesome work.
@asadrauf5902
@asadrauf5902 2 жыл бұрын
Excellent!!!...you made my day. Thank you Leila. Keep on helping people. God bless you.
@gob2004
@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.
@raroboy715
@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 ....
@darielanria9493
@darielanria9493 5 жыл бұрын
Leila you are not from this world. Legend!!
@rishabgaba
@rishabgaba 2 жыл бұрын
blew my mind. Exactly what I was looking for. Thank you for figuring this out.
@danielbm24
@danielbm24 4 жыл бұрын
Thanks Leila! You're the absolute best!
@Labirinth7
@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
@LeilaGharani 4 жыл бұрын
Great to hear!
@dilipkjena
@dilipkjena 3 жыл бұрын
Your channel is a blessing. It is helping me in my presentations in my PhD. Thank you, Leila.
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Wonderful!
@NicoMellett
@NicoMellett 4 жыл бұрын
This is amazing, thank you!
@junaidsidiq3234
@junaidsidiq3234 5 жыл бұрын
You made my day. Thanks!
@Farhad4Layek
@Farhad4Layek 4 жыл бұрын
Thanks Leila! You're the best
@astepintherightdirection2725
@astepintherightdirection2725 3 жыл бұрын
Forever grateful for your videos. Please keep up the good work.
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Thank you, I will
@kuuuyajim
@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! 😃
@parmarboyz
@parmarboyz 5 жыл бұрын
What a Beautiful Trick, Brilliant Formula and Super Awesome Video..... 1000 Likes for this Leila. Keep Up, Great Work.
@LeilaGharani
@LeilaGharani 5 жыл бұрын
I'm glad you like it!
@eyuelgorfu9404
@eyuelgorfu9404 4 жыл бұрын
You did it again. Thank you!!!
@omara4673
@omara4673 4 жыл бұрын
Exactly what i needed! thanks
@jesuseduardobittersuarez7517
@jesuseduardobittersuarez7517 2 жыл бұрын
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
@toomuchsend9237
@toomuchsend9237 2 жыл бұрын
You literally saved my life
@atanugg9372
@atanugg9372 6 жыл бұрын
Thanks, simple intuitive video ,it help me to apply.
@LeilaGharani
@LeilaGharani 6 жыл бұрын
You're very welcome.
@njugunawanyoike146
@njugunawanyoike146 4 жыл бұрын
awesome. Keep up the great work.
@orkinabdallatyf7205
@orkinabdallatyf7205 4 жыл бұрын
indeed more than amazing mohandesa leila and truly useful
@guidobusuttil4659
@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.
@ijnmpi
@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
@LeilaGharani 4 жыл бұрын
Glad I could help!
@Dimitronikos
@Dimitronikos 3 жыл бұрын
Thank you very much for your assistance!
@edlewis1688
@edlewis1688 5 жыл бұрын
Thanks for this video. It was a great help.
@LeilaGharani
@LeilaGharani 5 жыл бұрын
I'm glad to hear that Ed.
@excelisfun
@excelisfun 6 жыл бұрын
Thanks for the gap-less charting : )
@LeilaGharani
@LeilaGharani 6 жыл бұрын
Thanks Mike :)
@tonyc4669
@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
@emilycicerkofski8835
@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 :)
@dianavelasquez3354
@dianavelasquez3354 4 жыл бұрын
Saved my job!👌🏼🤓
@user-sn9mc7jt6n
@user-sn9mc7jt6n Жыл бұрын
Very useful. Thanks
@agoesrizatp1937
@agoesrizatp1937 2 жыл бұрын
Superb! Thank you
@abdanomer
@abdanomer 6 жыл бұрын
Good index session, I like it a lot 👍🏻👍🏻👍🏼
@LeilaGharani
@LeilaGharani 6 жыл бұрын
Thanks Abdelrahman.
@allanpark6046
@allanpark6046 3 жыл бұрын
Thank you thank you thank you. Amazing!
@annammaheshbabu3580
@annammaheshbabu3580 3 жыл бұрын
Love you Leila, you are awesome.. it is very helpful....
@LeilaGharani
@LeilaGharani 3 жыл бұрын
You're so welcome!
@dr.imrankhanyousufzai4710
@dr.imrankhanyousufzai4710 3 жыл бұрын
Thanks a lot for the help
@christopherrilles3106
@christopherrilles3106 3 жыл бұрын
Thank you!!
@csabakucor9506
@csabakucor9506 5 жыл бұрын
amaizing and simple...jejjjjj,thank you!
@LeilaGharani
@LeilaGharani 5 жыл бұрын
You're very welcome Csaba!
@Pa1KumarSistla
@Pa1KumarSistla 4 жыл бұрын
Thank you for the explanation, since there are new O365 functions, can this be done a simpler way, please
@yavuzt1017
@yavuzt1017 3 жыл бұрын
this video is so useful to me that i have solved my sorting issues. thanks a lot....
@LeilaGharani
@LeilaGharani 3 жыл бұрын
Glad it helped.
@user-lv3vo9ew2j
@user-lv3vo9ew2j 6 жыл бұрын
What a genius woman !!
@LeilaGharani
@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
@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.
@pooverngounden3049
@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.
@alexandresantiago5499
@alexandresantiago5499 3 жыл бұрын
TY very much
@bealovemomdad
@bealovemomdad 3 жыл бұрын
Thank you
@olivierkandel9409
@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!
@shayschopp
@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.
@BillSzysz1
@BillSzysz1 6 жыл бұрын
Thanks Leila :-)
@LeilaGharani
@LeilaGharani 6 жыл бұрын
Thanks Bill - you're simply great! I've added your formulas to the comments and the workbook.
@sbnoraje5799
@sbnoraje5799 2 жыл бұрын
Great
@dr99
@dr99 4 жыл бұрын
thank's.
@killermachine3143
@killermachine3143 2 жыл бұрын
Please provide a video on dynamic sorting of text without using sort() or sortby()
@marz44
@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!!
@hosseinhosseinpoor4845
@hosseinhosseinpoor4845 3 жыл бұрын
thanks thanks
@settelaaymen8085
@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
@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
@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....
@stiankiese6728
@stiankiese6728 5 жыл бұрын
Great video! I am struggling with adding two different lines within the same chart.. Do you have any workarounds to share?
@LeilaGharani
@LeilaGharani 5 жыл бұрын
Do you need to break the line like in this video? kzbin.info/www/bejne/a5_JaaWrmN1rZqc
@stiankiese6728
@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
@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..
@karencrouch5671
@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
@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
@karencrouch5671 Жыл бұрын
@@NicholasFalter00 thanks, I'll give that a try
@gantulgaerdenechimeg3520
@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
@dianasharon5647
@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!
@kwfong3037
@kwfong3037 Жыл бұрын
多謝!
@LeilaGharani
@LeilaGharani Жыл бұрын
Thank you!
@soniccheese01
@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
@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.
@tingpangchong437
@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
@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.
@soniccheese01
@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
@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 :)
@TheBatey1
@TheBatey1 2 жыл бұрын
does this work with text, or formula to get form another worksheet?
@MalinaC
@MalinaC 6 жыл бұрын
Leila, what microphone do you use? You sound perfect :)
@LeilaGharani
@LeilaGharani 6 жыл бұрын
Thank Malina. I use Edutige lavalier mic: amzn.to/2y3Yq5y.
@rockguitarist8907
@rockguitarist8907 6 жыл бұрын
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
@LeilaGharani 6 жыл бұрын
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.....
@antoniobacic5674
@antoniobacic5674 Жыл бұрын
Can it be done with newest dates on top automatically?
@apurvchaurasia
@apurvchaurasia 4 жыл бұрын
what if the my data are in text format then what can be done ?
@86Maryj
@86Maryj 3 жыл бұрын
Hi instead of blanks if its text string mentioned how to exclude that string pls assist
@valeromero973
@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.
@Christian-uz3qs7fe5g
@Christian-uz3qs7fe5g 5 жыл бұрын
How do you skip columns instead of rows
@LouayHomsi
@LouayHomsi Жыл бұрын
Is it possible to skip zeros too?
@kebincui
@kebincui 3 жыл бұрын
👍👍👍
@antoinecamilleri7650
@antoinecamilleri7650 4 жыл бұрын
Does it have to be so complicated!!! All i need to do is ignore 0 when i query a table
@milkar4135
@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
@IcePaq986
@IcePaq986 3 жыл бұрын
can this also apply to Bar chart?
@tormodsolemslupphaug1043
@tormodsolemslupphaug1043 3 жыл бұрын
Yes
@kaiser_83
@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
@Wosso-Spiritas
@Wosso-Spiritas 2 жыл бұрын
Leila - if one date is repeated the result is no longer correct
@scarfacesheki
@scarfacesheki 5 жыл бұрын
I get an Num Error when using the Formula
@LeilaGharani
@LeilaGharani 5 жыл бұрын
Try downloading the workbook (link is in the description of the video) and cross-checking with own formulas...
@BernardoTavora
@BernardoTavora 2 жыл бұрын
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
3 REALLY Useful Excel Chart Tips You May Have Missed
5:53
Leila Gharani
Рет қаралды 87 М.
Looks realistic #tiktok
00:22
Анастасия Тарасова
Рет қаралды 97 МЛН
КАРМАНЧИК 2 СЕЗОН 7 СЕРИЯ ФИНАЛ
21:37
Inter Production
Рет қаралды 538 М.
Ignore Blanks in Data Validation Lists in Excel
10:39
TeachExcel
Рет қаралды 51 М.
5 Excel Secrets You'll Be Embarrassed You Didn't Know
17:32
Excel Campus - Jon
Рет қаралды 100 М.
Dynamic array charts Integration -Excel(XLOOKUP Bonus)
11:08
ExcelMoments
Рет қаралды 1 М.
When Should You Use the Hash Sign in Excel Formulas?
10:53
Leila Gharani
Рет қаралды 1 МЛН
10 Excel Formulas That Will Set You Apart (Cheat Sheet)
18:04
MyOnlineTrainingHub
Рет қаралды 214 М.
How to Remove Blanks in Excel Drop Down Lists
14:32
Up4Excel
Рет қаралды 8 М.
Excel Charts and Graphs Tutorial
24:31
Kevin Stratvert
Рет қаралды 479 М.
How to PREVENT a dynamic LINE CHART from DROPPING TO ZERO in Excel
5:31
Looks realistic #tiktok
00:22
Анастасия Тарасова
Рет қаралды 97 МЛН