How to Not Show Zero Values in Excel Line Chart (prevent drop to zero & dynamic legend positioning)

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Enhance your Excel charting skills with this insightful tutorial, perfect for anyone who needs to create dynamic and informative line charts in their reports. This video walks you through an essential trick to cut off line series based on a specific month selection, ensuring your charts display only relevant data.
⬇️ Download the workbook here: pages.xelplus.com/line-chart-...
Here's what you'll learn:
Problem with Extended Line Series: Understand the common issue in Excel line charts where future months are displayed even when not needed.
Creating a Dynamic Dropdown List: Follow the steps to insert a dropdown list for month selection using data validation, which dictates the data shown in the chart.
Preparing Data for Charting: Discover how to prepare a data table that the chart will reference, ensuring it only shows data up to the selected month.
Implementing the MATCH Function: Learn to use the MATCH function to locate a specific month within your data, a crucial step in customizing your line chart.
Using IF and COUNTA for Data Comparison: Find out how to compare months and set up conditions to display data only for selected months and earlier.
Crafting the Line Chart: See the process of inserting and optimizing a line chart, including how to avoid line crashes to zero by using the NA function.
Adding Dynamic Data Labels and Titles: Get tips on creating moving labels for your line series that update based on the selected month, making your charts more interactive and informative.
Hiding Errors in Your Report: A bonus tip on how to hide NA errors in your report using conditional formatting, keeping your charts clean and professional.
In this Excel Line Chart tutorial, you learn four tricks:
1. How to prevent your line series to drop to zero
2. How to create dynamic charts based on a Point of View (in this case month selection)
3. How to integrate your legend inside the chart - i.e. at the end of the line series for improved readability and...
4. How to hide or make error values in cells, invisible with conditional formatting.
Links to related videos:
Basics of Excel Charts: • Excel Charts & Graphs:...
Matrix charts in Excel: • How to Create Panel Ch...
Fully Playlist on Charts: • Excel Charts
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ 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

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/line-chart-zero-file

[@dseaq]

The fact that you bring up real business problems to discuss, makes these classes so interesting! And the solutions discussed are simple yet elegant. Thank you so much.

[@saikarlsass710]

Leila, it's just magic what your doing with Excel. Awsome!

[@speedylogic]

Thanks Leila, great content, great presentation, simple, elegant and very useful.

[@excelisfun]

Thanks for the Actual charting fun : )

[@LeilaGharani]

You're welcome Mike :)

[@FinnJenkins]

As always, you have saved the day. I've been battling with a challenging graphic situation for 2 days and just found the answer in your na() trick. Thank you.

[@LeilaGharani]

Glad it helped, Alex!

[@nz2555]

Amazing tutorial!

[@widuralatest]

Oh, dear. thanks a lot for the video. You saved my day. I didn't know about "na()" that can be used to omit specific values from a chart. thanks a lot for pointing it out.

[@yeddyable]

Wow this what i've been looking for... Thanks...

[@saraz9179]

You explain so well and make it so simple Thank you مرسی

[@Max_-]

Awesome videos!!! Thank you very much for the pragmatic advice, excellent quality and your really friendly style!

[@LeilaGharani]

You're very welcome Max. I'm glad you like the videos!

[@dmarti47]

Thank you, Leila. This helped a lot.

[@MohammedShajialpetta]

Great thanks for productive lessons in excel.. you are an Angel for people need to learn ..

[@AbhishekTripathidce]

Awesome video. Thank You LG .

[@mazarata73]

I was just looking for the button to giv you at least 3 thumbs up. So very precious your tips. Thank you very much Leila!

[@LeilaGharani]

That's very kind! Thank you.

[@stephengibson100]

Fantastic...again. Thanks Leila

[@muazabdullah3124]

Thanks Ms. Gharani for saving my day! Seeing #N/A in tables isn't giving me aches anymore!

[@LeilaGharani]

My pleasure 😊

[@bricc322]

Such awesome and smart tricks! Thank you, Leila!

[@LeilaGharani]

Glad you like it Celia. You're very welcome.

[@matthewbrierley88]

Leila, you're a legend.

[@internet_wanderer8316]

Too good. Keep going. Thanks.

[@rajnajat]

You always give a simple solution to complex problems. Thanks and God bless !

[@LeilaGharani]

Our pleasure!

[@ashpatacpan6215]

I need a Leila Gharani by my side everyday. Love your vids ❤️❤️❤️ The best!

[@gauravmore1908]

Exactly what I was looking for 👍

[@nazmulkalam9859]

Thanks! Helpful indeed.

[@Waragog]

Thank you!

[@michaeldiamond2726]

Great video Leial, as always. I like the moving label trick I think this should also be added to your chart course. I do remember you addressing this several times in some of your advanced charting techniques.

[@LeilaGharani]

Hi Michael. Thank you for your comment. Yes - this one is included. Although I have to redo the chart course sometime next year on the latest Excel version....

[@robertojunqueira]

Exactly what I needed! Congratulations

[@LeilaGharani]

Perfect!

[@terrymadeley]

Very helpful, thanks a lot. And a great tip about hiding the NA from view too.

[@LeilaGharani]

You're very welcome Terry.

[@craigvoss1468]

Another great video. You're awesome.

[@LeilaGharani]

You're very welcome Craig :)

[@processengineer5088]

You have amazing skills...

[@arnabdebnath1223]

Was really not expecting to find a solution to this - thank you

[@semperdiscendum7439]

That was Awesome!!! Will wait for next one!! 👍👍👍👌👌

[@LeilaGharani]

Thanks. Glad you like it. Next one coming in a few days :).

[@SyedMuzammilMahasanShahi]

Thanks for the share.

[@samuelkodjoe1645]

powerful wisdom shared!!! love it!!

[@LeilaGharani]

Thanks Samuel! Hope it will come in handy.

[@Chef-1707]

solved so many problems at once for me. genius and cannot thank you enough. Great channel

[@LeilaGharani]

I’m glad you found this helpful! Thanks for sharing.

[@09shirish]

Thanks for your valuable guidance !!

[@LeilaGharani]

You're very welcome Shirish.

[@manuelvalbuena2755]

god bless you, i kept having that drop to zero... awesome!

[@esthergrace3516]

Oh gosh amazing, i feel am learning more here than any other paid courses....Love it..I am gonna watch all :)

[@LeilaGharani]

Wonderful! I'm happy to hear that.

[@RaviVerma-zp2fz]

You are a magician

[@salehmohammad5170]

Beautiful leila

[@ismailismaili0071]

OMG you are just an awesome teacher chart is your life you blow my mind thank u so much for this video i can't wait for the next one.

[@LeilaGharani]

Thank you Ismail for your kind comment! Glad you like it :)

[@rienthongw]

This is a great VDO. Thank you for sharing.

[@LeilaGharani]

You're very welcome.

[@neranjankarunaratne1690]

Thanks !

[@Stepford]

Nice one Leila! The zeros on charts had always bugged me, so it's great to hear that there is a dynamic solution to the problem. Thanks for sharing :)

[@LeilaGharani]

You're very welcome! Glad to hear that.

[@rjvandan]

@@LeilaGharani Hi Can You Share details on Custom Cell and Number Formatting in Deep As I am always getting freezed and unable to understand system of it

[@katerina6495]

Great Video Leila, thank you

[@LeilaGharani]

You're very welcome Katerina.

[@feliperodriguessousa5759]

Girl, I want to be just like when I grow up! You're truly amazing!

[@Otisawide]

Thank you

[@johnborg6005]

Thanks Leila. Very helpful for my work :)

[@LeilaGharani]

Great! thanks for your comment John.

[@henryleonrivera7385]

Excelente miss.

[@gulfway111]

Thank you for great short lessons

[@LeilaGharani]

You're very welcome Abdul.

[@zipp141]

Great video. Thanks a lot.

[@LeilaGharani]

You're very welcome :)

[@estrouse123]

Very helpful

[@rudi-gruber]

This is really helpful. Thx a lot

[@LeilaGharani]

You're very welcome Rudolf.

[@Detailedtravelers]

Excellent lesson thank you.

[@LeilaGharani]

You're welcome Darrin.

[@ahmedramadan4837]

thank you

[@44.7b.kaharaditya6]

Great job, Very Informative knowledge

[@LeilaGharani]

Thank you Manoj for your comment.

[@1gopalakrishnarao]

One MORE BIG SALUTE/ROYAL SALUTE to our greatest teacher, " GODDESS OF EXCEL"- Many Many countless thanks.

[@LeilaGharani]

Thank you very much for your very kind comment Gopala. Many many welcomes :)

[@stuksy4321]

so smart!

[@johnn5728]

Great videos I’m a senior analyst and some of these tips are awesome👍👍👍

[@LeilaGharani]

Great to hear!

[@huseinshajrawi8455]

Thank you Laila, awesome

[@LeilaGharani]

You're very welcome Husein.

[@jbcanlas]

Awesome!

[@LeilaGharani]

Glad you like it Jeffrey!

[@backendadmin1217]

You are awesome... 😘😘😘

[@TanzanianRoots]

3:45am. Im saved. My Hero :)

[@PraveenKumar-hv9is]

Super thanks for you new tricks....

[@LeilaGharani]

My pleasure Praveen. Thanks for watching.

[@prashantsapale3822]

Thanks

[@joefromdc]

Great video

[@LeilaGharani]

Thanks Joe!

[@Sal_A]

Very useful technique :)

[@LeilaGharani]

Thanks Sal :)

[@saifahmed5246]

great thanks a lot Leila

[@LeilaGharani]

You're welcome Saif.

[@tamim2545]

Thank you very much

[@LeilaGharani]

You're very welcome Tamim.

[@shaikhzahid2363]

Awesome trick

[@LeilaGharani]

Glad you like it Shaikh :)

[@eng.hafizhabibhabib1268]

many thanks

[@LeilaGharani]

You're very welcome!

[@Dev_Bartwal]

Amezing topic LG Your class method is really meaning full and suitable... thanks LG ❣️

[@LeilaGharani]

Charts are one of my favorite topics :) Thanks for your comment and feedback.

[@Dev_Bartwal]

Leila Gharani your welcome Lg Plzz share scroll chart bar if possible

[@LeilaGharani]

I've added it to my list. Thanks for the suggestion.

[@eng.hafizhabibhabib1268]

Very good

[@LeilaGharani]

Glad you like it!

[@chatkishore]

Thanks for sharing this. Is it possible also mention the budget YTD to actual?

[@hosseinhosseinpoor4845]

thanks thanks

[@chamindabasnayake4844]

Great !

[@LeilaGharani]

Thank you for watching :)

[@WWShorter]

Thanks for your insightful and easy to understand tutorials. There may be another way to hide data rather than use the font color of the background. When setting the formatting, use a custom setting using three semi-colons; e.g. “;;;”. The cell will appear blank even though the cell contains your content.

[@nkoketsengkonopi6316]

True but the only problem with this solution is that you cannot use it in a formula. You would have to do this manually for all the cells returning NA.

[@lebronimad9941]

Merci beaucoup Leila.....Thnx so much ^_^

[@LeilaGharani]

You're very welcome Lebron.

[@mmc9305]

Hi Leila, thanks for sharing. I really enjoy your videos. Could you also do some video(s) for Get & Transform + Data Model + Power Pivot? A combination tutorial of the 3 would be awesome. Thanks.

[@LeilaGharani]

Thank you. Glad you like the videos. I don't do PQ videos yet - but Mike from ExcelisFun and Oz du soleil (Excel on Fire) have great KZbin channels that cover all this. Make sure you check them out.

[@andylenihan9738]

Hi Leila, is it possible to remove the big line drop and subsequent zeros on a stacked line pivot chart?

[@elisacarari4750]

Leila, is it possible to overcome overlapping data labels in line chart with multiple series?

[@sandipgumtya130]

Hi @leila: Suppose I have only one data series, Actual profit which changes as per the month selection. but the months in X-axis does not update it is till Dec only. Can it also be updated as per the month selection i.e. X-axis will show months what is selected.

[@garzagarza01]

Hi Leila, What if the first 3 and last 3 values for both curves are empty but eventually may be values?? How can I tell my graph to extend the middle values?

[@punchbuddies220]

Kiss kiss to you, Lei! Amazing!

[@franciscojaviergomezrodrig8325]

Me encantas 😍

[@sudhansugrahacharya7094]

Thank you madam all your videos are very educative, I would request you to make a video on macro VB language..

[@LeilaGharani]

You're very welcome Sudhansu. Glad you like the content. Yes - I'm currently working on my online VBA course. Due out hopefully in March. I will for sure put VBA content on KZbin as well. Coming up soon.

[@CarlosAguilar-xw7ot]

Hi Leila, i think you can also hide the "n/a" using IF.ERROR in the begining of the formula. Thanks for sharing your knowledge

[@AlokMishra2]

Please suggest how to make s curve

[@phatnq2002]

Thanks for your tutor. But I have a question: If the month is from Jan to Oct, the "Actual label" is displayed well, but not if accidentally select Nov or Dec. How to solve this?

[@user-ng2hy2zu3l]

درود بر شما

[@nagesh530]

Hi Leila, Is there a way to hide the line in a line chart, for example, I plot growth 2017 2018 2019 for one country in x-axis and again show another country growth for 2017 2018 2019 in X-axis, the problem is it shows a continuous line from 2019(data ends for the first country) to 2017(when the data for the second country begins). Thanks

[@user-cu5jr6eb4y]

Is there is any alternative methods? I have sevelar data coloums. I just want to omit data beyond give month(want to avoid falling to zeros after given month). Please reply.

[@tlee7028]

Hi Leila, I have Pivot Line Chart, how do i prevent a line series from dropping to Zero?

[@UbaidUllah-nj5et]

Hi mam...such a nice video and explanation...mam i have started to visit mr.excel.com and started reading peoples qureies and answer to them ...as per ur instructions mam...

[@LeilaGharani]

Thanks for your comment Ubaid! That sounds great! Way to go :) I find that every time I go over there with the intention of answering questions, I end up learning more.... :)

[@UbaidUllah-nj5et]

Same here mam...lots of questions plus lots of lerning there mam...

[@ralfprechtel2966]

Hi Leila, great chart! May I suggest a small improvement? If you select November or December, you will have a drop in the chart. I adjusted the formulas a little to avoid this problem: =WENN(ODER(C10="";ANZAHL2($B$10:B10)>VERGLEICH($D$8;Months;0));NV();C10) =WENN(UND(ISTFEHLER($E11);$E10"NV()");$E10;NV()) You just have to change the cells to your sheet. Kind regards, Ralf

[@harshanaweragama270]

is there any way that you can type this in English.. finding it difficult to understand the formula.

[@salamamohammad7276]

To avoid drop to zero, I use this IF formula format: If(plot value="",NA(),plot value).

[@josephjoachim8755]

Suppose the chart is create from pivot table (Pivot chart) how do you handle that?

[@pumpkhim]

Hi Leila, I have a problem here. When i change the B8 to other column, it will now show NA for future month. Please help me, I don't understand what when wrong.

[@DK_85]

Great video! One advice: it is much easier and faster to use the "iferror(if(..);"") function to hide the #nv's

[@LeilaGharani]

Yes - but then the line chart wouldn't work properly. This is one of the few times we actually need the NVs in the cells :)

[@DK_85]

Leila Gharani Oh yes, thought too fast and too wrong:D You are right! Sorry

[@LeilaGharani]

No problem - happens to me too :)

[@TheJoshtheboss]

I appreciate the creativity, but would it not be better just to use pivot chart?