How to Not Show Zero Values in Excel Line Chart (prevent drop to zero & dynamic legend positioning)
HTML-код
- Опубликовано: 29 ноя 2017
- 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
Grab the file I used in the video from here 👉 pages.xelplus.com/line-chart-zero-file
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.
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.
Glad it helped, Alex!
Leila, it's just magic what your doing with Excel. Awsome!
Thanks for the Actual charting fun : )
You're welcome Mike :)
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!
That's very kind! Thank you.
Thanks Leila, great content, great presentation, simple, elegant and very useful.
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.
Thanks Ms. Gharani for saving my day!
Seeing #N/A in tables isn't giving me aches anymore!
My pleasure 😊
You always give a simple solution to complex problems. Thanks and God bless !
Our pleasure!
Awesome videos!!! Thank you very much for the pragmatic advice, excellent quality and your really friendly style!
You're very welcome Max. I'm glad you like the videos!
You explain so well and
make it so simple
Thank you
مرسی
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.
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....
I need a Leila Gharani by my side everyday. Love your vids ❤️❤️❤️
The best!
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 :)
You're very welcome! Glad to hear that.
@@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
Oh gosh amazing, i feel am learning more here than any other paid courses....Love it..I am gonna watch all :)
Wonderful! I'm happy to hear that.
Was really not expecting to find a solution to this - thank you
Great thanks for productive lessons in excel.. you are an Angel for people need to learn ..
solved so many problems at once for me. genius and cannot thank you enough. Great channel
I’m glad you found this helpful! Thanks for sharing.
Such awesome and smart tricks! Thank you, Leila!
Glad you like it Celia. You're very welcome.
Very helpful, thanks a lot. And a great tip about hiding the NA from view too.
You're very welcome Terry.
Leila, you're a legend.
Girl, I want to be just like when I grow up! You're truly amazing!
One MORE BIG SALUTE/ROYAL SALUTE to our greatest teacher, " GODDESS OF EXCEL"- Many Many countless thanks.
Thank you very much for your very kind comment Gopala. Many many welcomes :)
Wow this what i've been looking for... Thanks...
That was Awesome!!! Will wait for next one!! 👍👍👍👌👌
Thanks. Glad you like it. Next one coming in a few days :).
Amazing tutorial!
Thank you, Leila. This helped a lot.
Exactly what I needed! Congratulations
Perfect!
god bless you, i kept having that drop to zero... awesome!
Awesome video. Thank You LG .
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.
Thank you Ismail for your kind comment! Glad you like it :)
Another great video. You're awesome.
You're very welcome Craig :)
Fantastic...again. Thanks Leila
Exactly what I was looking for 👍
This is a great VDO. Thank you for sharing.
You're very welcome.
Great videos
I’m a senior analyst and some of these tips are awesome👍👍👍
Great to hear!
Thanks for your valuable guidance !!
You're very welcome Shirish.
powerful wisdom shared!!! love it!!
Thanks Samuel! Hope it will come in handy.
Thanks Leila. Very helpful for my work :)
Great! thanks for your comment John.
You have amazing skills...
Thank you for great short lessons
You're very welcome Abdul.
Too good. Keep going. Thanks.
Great job, Very Informative knowledge
Thank you Manoj for your comment.
Thanks! Helpful indeed.
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.
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.
Great Video Leila, thank you
You're very welcome Katerina.
Thanks for sharing this. Is it possible also mention the budget YTD to actual?
Thank you!
Excellent lesson thank you.
You're welcome Darrin.
3:45am. Im saved. My Hero :)
You are a magician
Thanks for the share.
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.
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 RUclips channels that cover all this. Make sure you check them out.
This is really helpful. Thx a lot
You're very welcome Rudolf.
Great video. Thanks a lot.
You're very welcome :)
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
Beautiful leila
Super thanks for you new tricks....
My pleasure Praveen. Thanks for watching.
Excelente miss.
Thanks !
Thank you
Hi Leila, is it possible to remove the big line drop and subsequent zeros on a stacked line pivot chart?
Thank you Laila, awesome
You're very welcome Husein.
Leila, is it possible to overcome overlapping data labels in line chart with multiple series?
thank you
Amezing topic LG
Your class method is really meaning full and suitable... thanks LG ❣️
Charts are one of my favorite topics :) Thanks for your comment and feedback.
Leila Gharani your welcome Lg
Plzz share scroll chart bar if possible
I've added it to my list. Thanks for the suggestion.
Very useful technique :)
Thanks Sal :)
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.
Very helpful
Thanks
Awesome!
Glad you like it Jeffrey!
Merci beaucoup Leila.....Thnx so much ^_^
You're very welcome Lebron.
Thank you very much
You're very welcome Tamim.
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?
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?
great thanks a lot Leila
You're welcome Saif.
Great video
Thanks Joe!
Thank you madam all your videos are very educative, I would request you to make a video on macro VB language..
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 RUclips as well. Coming up soon.
Awesome trick
Glad you like it Shaikh :)
You are awesome... 😘😘😘
many thanks
You're very welcome!
so smart!
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
is there any way that you can type this in English.. finding it difficult to understand the formula.
Very good
Glad you like it!
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.
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
thanks thanks
To avoid drop to zero, I use this IF formula format: If(plot value="",NA(),plot value).
Please suggest how to make s curve
Great !
Thank you for watching :)
Hi Leila, I have Pivot Line Chart, how do i prevent a line series from dropping to Zero?
In line chart can i manage the minimum start point and max point in axis and make it dynamic. I know you touched this in one of the video but i cant find it. I do not want the axis starting with zero but start with the minim value of the series and the maximum is the max value of the series. And i want to make it dynamic so that based on my series axis gets adjusted to min and max. Thanks for educational videos.
Hi - I did this for the secondary axis once. Unfortunately, you cannot do this automatically (yet) for the primary axis. You can either manually set it or let Excel do it for you. There is a uservoice on this which I'm supporting. If you'd like to see this feature implemented in the next versions of Excel, please click on this link and press the vote button: excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/9046207-link-the-min-and-max-values-of-a-chart-axis-to-cel
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...
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.... :)
Same here mam...lots of questions plus lots of lerning there mam...
Kiss kiss to you, Lei! Amazing!
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.
Hi Leila, thanks for your videos. I follow you here on RUclips and have all your courses on Udemy! I'm struggling mightily with one little thing that should be fairly simple only if Excel have include it in its functionality. I tried to use the methods used on this video but not getting it right. I want to show just the data labels for the start and end of each line for a line chart with multiple categories. I know that I can just select the data points and show the labels but this will be a fixed solution, I want to do it more flexible in case more years are added so the label "moves" with the year. Do you have any video on this? Help please! Thanks!
Hi Adolfo - happy to see you here too :) The start can be fixed right? For the end to be dynamic and have your chart update automatically, you could possibly turn your data into an Excel table and for the dynamic label add a similar formula: =IF(ROWS([Year])-(ROW([@Year])-ROW($A$3))=0,[@Label],NA()) Whereas row A3 is the header of the table. This gives you #NA in all the cells except the last one..... It's a good idea for a video! I'll add this to my list. Thanks for your comment.
Great video! One advice: it is much easier and faster to use the "iferror(if(..);"") function to hide the #nv's
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 :)
Leila Gharani
Oh yes, thought too fast and too wrong:D You are right! Sorry
No problem - happens to me too :)
You should have showed how did u initially start the pivot graph by taking all values ,,
Suppose the chart is create from pivot table (Pivot chart) how do you handle that?