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

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.

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:

🚩Let’s connect on social:

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

[dlswa]

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.

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


Appreciate the input from the Excel Online team for helping us all become better in Excel.

[johnborg]

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.

[gob]

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.

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

[raroboy]

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

[Labirinth]

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!

[anette]

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

[ssharrison]

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

[asadrauf]

Excellent!!!...you made my day. Thank you Leila. Keep on helping people. God bless 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! 😃

[jesuseduardobittersuarez]

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

[darielanria]

Leila you are not from this world. Legend!!

[tonyc]

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

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

[guidobusuttil]

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.

[yavuzt]

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

[killermachine]

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