Automatically Skip Blanks in Excel Charts with Formulas (ignore gaps in Excel chart axis)
HTML-код
- Опубликовано: 7 июл 2024
- 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
Grab the file I used in the video from here 👉 pages.xelplus.com/remove-gaps-in-charts-file
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.
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.
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.
Leila you are not from this world. Legend!!
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.
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" :)
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!
Great to hear!
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.
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!!!
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 ....
You are absolutely amazing!!!! Your step by step instruction has brought back my sanity.
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.
Well done, Leila! Thank you, I really needed this info!
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! 😃
blew my mind. Exactly what I was looking for. Thank you for figuring this out.
Excellent!!!...you made my day. Thank you Leila. Keep on helping people. God bless you.
Just what I wanted, You're my first stop Guru
Thank you so much for doing this. Please continue and keep up the awesome work.
Thanks Leila! You're the absolute best!
This is amazing, thank you!
You made my day. Thanks!
Your channel is a blessing. It is helping me in my presentations in my PhD. Thank you, Leila.
Wonderful!
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
Exactly what i needed! thanks
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
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!!
Glad I could help!
Thanks Leila! You're the best
You did it again. Thank you!!!
Thank you very much for your assistance!
Very useful. Thanks
Superb! Thank you
indeed more than amazing mohandesa leila and truly useful
Thanks, simple intuitive video ,it help me to apply.
You're very welcome.
Forever grateful for your videos. Please keep up the good work.
Thank you, I will
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 :)
What a Beautiful Trick, Brilliant Formula and Super Awesome Video..... 1000 Likes for this Leila. Keep Up, Great Work.
I'm glad you like it!
Thanks for the gap-less charting : )
Thanks Mike :)
Saved my job!👌🏼🤓
awesome. Keep up the great work.
Thanks a lot for the help
Thank you for the explanation, since there are new O365 functions, can this be done a simpler way, please
Thank you thank you thank you. Amazing!
Thanks for this video. It was a great help.
I'm glad to hear that Ed.
Good index session, I like it a lot 👍🏻👍🏻👍🏼
Thanks Abdelrahman.
You literally saved my life
Thank you!!
Love you Leila, you are awesome.. it is very helpful....
You're so welcome!
Thank you
this video is so useful to me that i have solved my sorting issues. thanks a lot....
Glad it helped.
amaizing and simple...jejjjjj,thank you!
You're very welcome Csaba!
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.
TY very much
What a genius woman !!
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 :)
What a genius woman having the brain of subtle understanding and having the heart to sharing it in simple Crystal clear communication.
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.
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!
thanks for the video but we have to add 1 to the agregate function as follows: agregate(15;3;row(X:Y);1)
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!!
Please provide a video on dynamic sorting of text without using sort() or sortby()
Thanks Leila :-)
Thanks Bill - you're simply great! I've added your formulas to the comments and the workbook.
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
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!
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!
@@NicholasFalter00 thanks, I'll give that a try
Great
thank's.
Great video!
I am struggling with adding two different lines within the same chart.. Do you have any workarounds to share?
Do you need to break the line like in this video? ruclips.net/video/5nf8rucx80E/видео.html
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?
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..
thanks thanks
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!
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.
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....
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.
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 :)
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!
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.
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?
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.
does this work with text, or formula to get form another worksheet?
多謝！
Thank you!
How do you skip columns instead of rows
what if the my data are in text format then what can be done ?
Can it be done with newest dates on top automatically?
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?
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.....
Is it possible to skip zeros too?
Leila, what microphone do you use? You sound perfect :)
Thank Malina. I use Edutige lavalier mic: amzn.to/2y3Yq5y.
Hi instead of blanks if its text string mentioned how to exclude that string pls assist
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.
can this also apply to Bar chart?
Yes
Does it have to be so complicated!!! All i need to do is ignore 0 when i query a table
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
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
I get an Num Error when using the Formula
Try downloading the workbook (link is in the description of the video) and cross-checking with own formulas...
Leila - if one date is repeated the result is no longer correct
👍👍👍
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