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

Leila Gharani November 30, 2017
Video Thumbnail
Leila Gharani Logo

Leila Gharani

View Channel

About

Excel. Power Query. Copilot. ChatGPT. Power BI. PowerPoint. You use them every day to automate Excel and your work - so why not actually master them? I’ll show you how to: ✔️Automate the stuff that drives you nuts ✔️ Spend less time on grunt work, more time on wow work ✔️ Build reports that look good AND actually make sense What You’ll Learn Here: ✔️ Excel, Power BI, PowerPoint, VBA, Google Sheets & more ✔️ AI-powered Excel & the future of work ✔️ Data & finance skills that give you an edge I don’t just teach this stuff - I use it daily. My goal? To help you think differently about the tools you already have, so you can work faster, and with confidence. 🎓 My background: MA in economics / Economist / Business Consultant / Accounting Systems Expert / Oracle & SAP implementations (for Finance) / Teacher & Microsoft MVP 💡 400,000+ professionals have taken my courses at XelPlus - because working smarter just makes sense. Join here 👉 https://www.xelplus.com/courses/

Video Description

🔥 Create charts that wow your audience. Learn the secrets now—start today! 👉 https://link.xelplus.com/yt-bizcharts-course 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: https://pages.xelplus.com/line-chart-zero-file 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: https://youtu.be/DAU0qqh_I-A Matrix charts in Excel: https://youtu.be/5nf8rucx80E Fully Playlist on Charts: https://www.youtube.com/playlist?list=PLmHVyfmcRKyyEj7oQkCf7TL9yQQWXbGOQ ★ My Online Excel Courses ► https://www.xelplus.com/courses/ ➡️ Join this channel to get access to perks: https://www.youtube.com/channel/UCJtUOos_MwJa_Ewii-R3cJA/join 👕☕ Get the Official XelPlus MERCH: https://xelplus.creator-spring.com/ 🎓 Not sure which of my Excel courses fits best for you? Take the quiz: https://www.xelplus.com/course-quiz/ 🎥 RESOURCES I recommend: https://www.xelplus.com/resources/ 🚩Let’s connect on social: Instagram: https://www.instagram.com/lgharani LinkedIn: https://www.linkedin.com/company/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

You May Also Like

Upgrade Your Everyday

AI-recommended products based on this video

Loading...
Kasa Smart Outdoor Smart Plug by TP-Link (KP400) - Smart WiFi Outlet with 2 Sockets, IP64 Waterproof, Works with Alexa and Google Home, 2.4GHz WiFi Required, No Hub Required, Sunset & Sunrise Offset

Kasa Smart Outdoor Smart Plug by TP-Link (KP400) - Smart WiFi Outlet with 2 Sockets, IP64 Waterproof, Works with Alexa and Google Home, 2.4GHz WiFi Required, No Hub Required, Sunset & Sunrise Offset

(28,846)
$21.99$17.99
FREE delivery Wed, Aug 20 on your first order
1K+ bought in past month
Loading...
Wireless Earbuds, Sports Bluetooth Headphones, 80Hrs Playtime Ear Buds with LED Power Display, Noise Canceling Headset, IPX7 Waterproof Earphones for Workout/Running(Black)

Wireless Earbuds, Sports Bluetooth Headphones, 80Hrs Playtime Ear Buds with LED Power Display, Noise Canceling Headset, IPX7 Waterproof Earphones for Workout/Running(Black)

(299)
$32.99
Prime
Loading...
Wireless Earbuds, Bluetooth 5.4 Headphones HiFi Stereo with 4 HD Mic, 50H Playtime Bluetooth Earbuds with LED Display, IPX7 Waterproof Wireless Earphones, Touch Control Ear Buds,USB-C White

Wireless Earbuds, Bluetooth 5.4 Headphones HiFi Stereo with 4 HD Mic, 50H Playtime Bluetooth Earbuds with LED Display, IPX7 Waterproof Wireless Earphones, Touch Control Ear Buds,USB-C White

(228)
$26.99
Prime
Loading...
Wireless Earbuds, Bluetooth Ear Buds 5.4 Mini HiFi Stereo with 4 ENC Noise Cancelling Mics Wireless Headphones, in Ear Earphones 40H IP7 Waterproof, USB C, Bluetooth Earbuds for Sports, Gym, Rose

Wireless Earbuds, Bluetooth Ear Buds 5.4 Mini HiFi Stereo with 4 ENC Noise Cancelling Mics Wireless Headphones, in Ear Earphones 40H IP7 Waterproof, USB C, Bluetooth Earbuds for Sports, Gym, Rose

(323)
$31.99
FREE delivery Wed, Dec 3 on your first order
500+ bought in past month
Loading...
soundcore P31i by Anker, Real-Time Adaptive Noise Cancelling, Hi-Res Sound, Translation Earbuds, 50H Playtime, Wireless Earbuds, Bluetooth Earphones, Spatial Audio, Fast Charging, IP55 ClimatePartner certified

soundcore P31i by Anker, Real-Time Adaptive Noise Cancelling, Hi-Res Sound, Translation Earbuds, 50H Playtime, Wireless Earbuds, Bluetooth Earphones, Spatial Audio, Fast Charging, IP55 ClimatePartner certified

(105)
$39.99
FREE delivery Thu, Nov 27
Loading...
Brita Stainless Steel Premium Filtering Water Bottle, BPA-Free, Reusable, Insulated, Replaces 300 Plastic Water Bottles, Filter Lasts 2 Months or 40 Gallons, Includes 1 Filter, Carbon - 20 oz.

Brita Stainless Steel Premium Filtering Water Bottle, BPA-Free, Reusable, Insulated, Replaces 300 Plastic Water Bottles, Filter Lasts 2 Months or 40 Gallons, Includes 1 Filter, Carbon - 20 oz.

(2,652)
$81.37$76.36
FREE delivery Sep 29 - Oct 2
Loading...
Simple Modern Filtered Water Bottle | Insulated Stainless-Steel Carbon Filter Travel Water Bottles | Reusable for Clean Drinking Water On The Go | 24oz, Sea Glass Sage

Simple Modern Filtered Water Bottle | Insulated Stainless-Steel Carbon Filter Travel Water Bottles | Reusable for Clean Drinking Water On The Go | 24oz, Sea Glass Sage

(26)
$30.13
FREE delivery Tue, Sep 23 on your first order
Loading...
FITVII Health & Fitness Tracker (Answer/Make Calls), Smart Watch with 24/7 Heart Rate and Blood Pressure, Sleep Tracking Monitor, 120+ Sport Mode Activity Tracker

FITVII Health & Fitness Tracker (Answer/Make Calls), Smart Watch with 24/7 Heart Rate and Blood Pressure, Sleep Tracking Monitor, 120+ Sport Mode Activity Tracker

(198)
$59.99
FREE delivery Sun, Aug 10
100+ bought in past month
Loading...
AYATAHA AYATAHA Smart Watch for Kids, Smartwatch Fitness Tracker for Boys Girls, Children's Activity Watch 37 Sports Modes SMS Notification, HD Full Touchscreen IP67 Waterproof, Blue

AYATAHA AYATAHA Smart Watch for Kids, Smartwatch Fitness Tracker for Boys Girls, Children's Activity Watch 37 Sports Modes SMS Notification, HD Full Touchscreen IP67 Waterproof, Blue

(55)
$41.99
PrimeFREE delivery Sat, Jun 14
Loading...
Iaret Iaret Smart Watch for Women, 1.83" HD Fitness Tracker with 4 Bands, Answer/Make Calls, Heart Rate/Sleep/SpO2/Step Tracking, 100+ Sport Modes, Android/iPhone Compatible Gift (Rose Gold)

Iaret Iaret Smart Watch for Women, 1.83" HD Fitness Tracker with 4 Bands, Answer/Make Calls, Heart Rate/Sleep/SpO2/Step Tracking, 100+ Sport Modes, Android/iPhone Compatible Gift (Rose Gold)

(42)
$59.99
FREE delivery Thu, Jun 19
100+ bought in past month
Loading...
Smart Watch for Men Women 1.8" Fitness Tracker, Bluetooth Call, DIY Dial, Heart Rate Sleep Blood Oxygen Monitor, 100+ Sports Modes, IP68 Waterproof Smartwatch for Android iPhone, Alexa Built-in

Smart Watch for Men Women 1.8" Fitness Tracker, Bluetooth Call, DIY Dial, Heart Rate Sleep Blood Oxygen Monitor, 100+ Sports Modes, IP68 Waterproof Smartwatch for Android iPhone, Alexa Built-in

(980)
$39.99
FREE delivery Sun, Sep 21
300+ bought in past month
Loading...
Hand Warmers 2 Pack, 14000mAh Rechargeable Hand Warmers, Electric Hand Warmer Reusable, Portable Power Bank USB Hand Warmers 4 Levels 8 Heating, Gifts for Raynauds Ski Golf Camping

Hand Warmers 2 Pack, 14000mAh Rechargeable Hand Warmers, Electric Hand Warmer Reusable, Portable Power Bank USB Hand Warmers 4 Levels 8 Heating, Gifts for Raynauds Ski Golf Camping

(683)
$74.08$59.47
FREE delivery Jun 13 - 18
Loading...
Hand Warmers Rechargeable, 10000mAh Electric Heated Gloves Power Bank Portable Graphene Handwarmers Pouch with 3 Levels & Double-Sided Heating for Hunting Camping Golf Xmas Gifts for Women Men Kids

Hand Warmers Rechargeable, 10000mAh Electric Heated Gloves Power Bank Portable Graphene Handwarmers Pouch with 3 Levels & Double-Sided Heating for Hunting Camping Golf Xmas Gifts for Women Men Kids

(651)
$44.99
PrimeFREE delivery Sun, Jun 15
Loading...
2Pack Rechargeable Hand Warmer, 8000mAh Electric Hand Warmer Power Bank, Portable USB-C Hand Warmer for Pocket, Reusable Hand Warmer Up to 8 hrs Each, Warm Gift for Men Women, for Hunting, Camping
Amazon's Choice

2Pack Rechargeable Hand Warmer, 8000mAh Electric Hand Warmer Power Bank, Portable USB-C Hand Warmer for Pocket, Reusable Hand Warmer Up to 8 hrs Each, Warm Gift for Men Women, for Hunting, Camping

(1,128)
$15.99
FREE delivery Sat, Jun 14 on your first order
Loading...
GTOCE Portable Charger,40000mAh Power Bank with 22.5W Fast Charging LED Digital Display Battery Pack with 6 Outputs 2 Inputs, Type C Powerbank Portable Charger for iPhone 16 pro Samsung AirPods,Black

GTOCE Portable Charger,40000mAh Power Bank with 22.5W Fast Charging LED Digital Display Battery Pack with 6 Outputs 2 Inputs, Type C Powerbank Portable Charger for iPhone 16 pro Samsung AirPods,Black

(334)
$42.99
FREE delivery Thu, Oct 16
1K+ bought in past month
Loading...
YMZ Smart Watch for Men Women, IP68 Waterproof, Bluetooth Calling, Fitness Tracker, Sleep Monitoring, 1.85'' DIY Dial, AI Built-in, 100+ Sports Modes, 7 Days Battery, Compatible with Android & iPhones

YMZ Smart Watch for Men Women, IP68 Waterproof, Bluetooth Calling, Fitness Tracker, Sleep Monitoring, 1.85'' DIY Dial, AI Built-in, 100+ Sports Modes, 7 Days Battery, Compatible with Android & iPhones

(331)
$49.99
FREE delivery Thu, Jun 19
1K+ bought in past month
Loading...
Smart Watch for Men Women 1.8" Fitness Tracker, Bluetooth Call, DIY Dial, Heart Rate Sleep Blood Oxygen Monitor, 100+ Sports Modes, IP68 Waterproof Smartwatch for Android iPhone, Alexa Built-in

Smart Watch for Men Women 1.8" Fitness Tracker, Bluetooth Call, DIY Dial, Heart Rate Sleep Blood Oxygen Monitor, 100+ Sports Modes, IP68 Waterproof Smartwatch for Android iPhone, Alexa Built-in

(980)
$39.99
FREE delivery Sun, Sep 21
300+ bought in past month
Loading...
Smart Watch for Men Women with Bluetooth Call, Alexa Built-in, Heart Rate & Sleep Monitor, 1.83" DIY Dial Fitness Watch with 100+ Sport Modes, IP68 Waterproof, Compatible with Android iOS

Smart Watch for Men Women with Bluetooth Call, Alexa Built-in, Heart Rate & Sleep Monitor, 1.83" DIY Dial Fitness Watch with 100+ Sport Modes, IP68 Waterproof, Compatible with Android iOS

(182)
$49.99
FREE delivery Fri, Oct 31
2K+ bought in past month
Loading...
Smart Watch for Men Women with Bluetooth Call, Alexa Built-in, Heart Rate & Sleep Monitor, 1.83" DIY Dial Fitness Watch with 100+ Sport Modes, IP68 Waterproof, Compatible with Android iOS
Best Seller

Smart Watch for Men Women with Bluetooth Call, Alexa Built-in, Heart Rate & Sleep Monitor, 1.83" DIY Dial Fitness Watch with 100+ Sport Modes, IP68 Waterproof, Compatible with Android iOS

(142)
$44.99
FREE delivery Tue, Oct 21
Loading...
Hydroponics Growing System Indoor Garden - Herb Garden with Grow Light, 15 Pods Stainless Steel Indoor Garden Kit, Auto Timer, Gardening Gift for All Ages

Hydroponics Growing System Indoor Garden - Herb Garden with Grow Light, 15 Pods Stainless Steel Indoor Garden Kit, Auto Timer, Gardening Gift for All Ages

(24)
$206.57$200.61
FREE delivery Jun 13 - 17
Loading...
Umbra Triflora Hanging Planter for Window, Indoor Herb Garden, Set of 5, White/Black

Umbra Triflora Hanging Planter for Window, Indoor Herb Garden, Set of 5, White/Black

(7,808)
$71.52
PrimeFREE delivery Sat, Jun 14
Loading...
Large Hydroponics Growing System 14 Pods, Indoor Herb Garden with LED Grow Light, 5L Water Tank, Hydroponic Grow Kit with 3 Auto-Timers, Rotatable Light Panel and Child Lock for Home School Gardening

Large Hydroponics Growing System 14 Pods, Indoor Herb Garden with LED Grow Light, 5L Water Tank, Hydroponic Grow Kit with 3 Auto-Timers, Rotatable Light Panel and Child Lock for Home School Gardening

(119)
$167.81
FREE delivery Jun 16 - 20
Loading...
Hanging Planter Hanging Plant Holder, 6 Inch 4 Indoor Plant Pots, Wall/Window Plant Hanger Indoor Herb Garden

Hanging Planter Hanging Plant Holder, 6 Inch 4 Indoor Plant Pots, Wall/Window Plant Hanger Indoor Herb Garden

(269)
$35.99$28.90
PrimeFREE delivery Sat, Jun 14
Loading...
Ninja Air Fryer Pro 5-in-1, 5 QT Capacity, Air Fry, Bake, Roast, Reheat, and Dehydrate, Air Crisp Technology, Nonstick Basket & Crisper Plate, Grey, AF142C

Ninja Air Fryer Pro 5-in-1, 5 QT Capacity, Air Fry, Bake, Roast, Reheat, and Dehydrate, Air Crisp Technology, Nonstick Basket & Crisper Plate, Grey, AF142C

(650)
$100.99
FREE delivery Thu, Oct 16
500+ bought in past month
Loading...
Glass Oil Sprayer for Cooking, 180ml Olive Oil Sprayer Bottle with Brush, Olive Oil Spray Mister, Kitchen Gadgets Accessories for Air Fryer, Canola Oil Spritzer (GREY)

Glass Oil Sprayer for Cooking, 180ml Olive Oil Sprayer Bottle with Brush, Olive Oil Spray Mister, Kitchen Gadgets Accessories for Air Fryer, Canola Oil Spritzer (GREY)

(2,436)
$7.99
FREE delivery Mon, Jun 16 on your first order
Loading...
Hi.FANCY Portable Laptop Stand with Dual Cooling Fans for 14-17inch Laptops, Grey, 23.5 x 25.9 x 0.95cm

Hi.FANCY Portable Laptop Stand with Dual Cooling Fans for 14-17inch Laptops, Grey, 23.5 x 25.9 x 0.95cm

(0)
$36.99
FREE delivery Aug 18 - 28
Loading...
Laptop Stand for Desk, Adjustable Laptop Riser ABS+Silicone Foldable Portable Laptop Holder, Ventilated Cooling Notebook Stand for 10-15.6” Laptops,Tablet-Black

Laptop Stand for Desk, Adjustable Laptop Riser ABS+Silicone Foldable Portable Laptop Holder, Ventilated Cooling Notebook Stand for 10-15.6” Laptops,Tablet-Black

(11,679)
$14.99
FREE delivery Sat, Jun 14 on your first order
500+ bought in past month
Loading...
JETech 5 in 1 Case for Samsung Galaxy S25 Ultra 5G with 2-Pack Each Tempered Glass Screen Protector and Camera Lens Protector, Non-Yellowing Shockproof Bumper Phone Cover (Clear)

JETech 5 in 1 Case for Samsung Galaxy S25 Ultra 5G with 2-Pack Each Tempered Glass Screen Protector and Camera Lens Protector, Non-Yellowing Shockproof Bumper Phone Cover (Clear)

(197)
$15.99
FREE delivery Sat, Jun 14 on your first order
100+ bought in past month
Loading...
TAURI for iPhone 17 Pro Max Case 6.9" with 1-Pack Screen Protector, Camera Lens Full Protection, Military-Grade Protection, Shockproof Transparent Back Bumper Phone Cover - Clear Global Recycled Standard

TAURI for iPhone 17 Pro Max Case 6.9" with 1-Pack Screen Protector, Camera Lens Full Protection, Military-Grade Protection, Shockproof Transparent Back Bumper Phone Cover - Clear Global Recycled Standard

(0)
$10.76
FREE delivery Thu, Oct 16 on your first order
2K+ bought in past month
Loading...
TAURI for iPhone 17 Pro Case 6.3" with 1-Pack Screen Protector, Camera Lens Full Protection, Military-Grade Protection, Shockproof Transparent Back Bumper Phone Cover - Clear Global Recycled Standard
Best Seller

TAURI for iPhone 17 Pro Case 6.3" with 1-Pack Screen Protector, Camera Lens Full Protection, Military-Grade Protection, Shockproof Transparent Back Bumper Phone Cover - Clear Global Recycled Standard

(61,696)
$10.76
Prime
2K+ bought in past month
Loading...
JOINPAYA 1Set Rechargeable Hand Warmer Hand Heater for Winter Heating Levels Compact

JOINPAYA 1Set Rechargeable Hand Warmer Hand Heater for Winter Heating Levels Compact

(0)
$9.29
FREE delivery Jun 20 - Jul 4
Loading...
Shakven Rechargeable Hand Warmer | Cute Comfortable Portable Hand Warmers,Ergonomic Adjustable Energy-Efficient Small Heater for Travel, Outdoor, Winter

Shakven Rechargeable Hand Warmer | Cute Comfortable Portable Hand Warmers,Ergonomic Adjustable Energy-Efficient Small Heater for Travel, Outdoor, Winter

(0)
$26.93
FREE delivery Jun 19 - Jul 2
Loading...
OCOOPA IP45 Waterproof Hand Warmer Rechargeable, Up to 15hrs Heat,10000mAh Durable Quick Charge Electric Hand Heater, PD Compatible, 3 Levels for Outdoors, Heavy Duty, H01-PD PRO

OCOOPA IP45 Waterproof Hand Warmer Rechargeable, Up to 15hrs Heat,10000mAh Durable Quick Charge Electric Hand Heater, PD Compatible, 3 Levels for Outdoors, Heavy Duty, H01-PD PRO

(50)
$39.99
PrimeFREE delivery Sat, Jun 14
Loading...
OCOOPA IP45 Waterproof Hand Warmer Rechargeable, Up to 15hrs Heat,10000mAh Durable Quick Charge Electric Hand Heater, PD Compatible, 3 Levels for Outdoors, Heavy Duty, H01-PD PRO

OCOOPA IP45 Waterproof Hand Warmer Rechargeable, Up to 15hrs Heat,10000mAh Durable Quick Charge Electric Hand Heater, PD Compatible, 3 Levels for Outdoors, Heavy Duty, H01-PD PRO

(649)
$39.99
PrimeFREE delivery Sat, Jun 14