+ Reply to Thread
Results 1 to 3 of 3

How to auto-adjust chart plotting when there is "" in the data series?

  1. #1
    Registered User
    Join Date
    10-23-2019
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    17

    How to auto-adjust chart plotting when there is "" in the data series?

    Hi all,

    The chart plots the whole data series including cells which are "".

    What can I do to make the chart auto adjust accordingly to only cells which have values?

    I am very keen to learn.
    Attached Files Attached Files
    Last edited by WalaVita; 05-01-2020 at 09:34 AM.

  2. #2
    Forum Moderator Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,799

    Re: How to auto-adjust chart plotting when there is "" in the data series?

    Try the dynamic chart ranges outlined here: https://exceljet.net/chart/dynamic-c...e-empty-values

    I created two names (press CTRL+F3):

    Name: Age
    Refers to: =Sheet1!$C$3:INDEX(Sheet1!$C$3:$C$32,COUNT(Sheet1!$C$3:$C$32))

    Name: Savings
    Refers to: =Sheet1!$D$3:INDEX(Sheet1!$D$3:$D$32,COUNT(Sheet1!$D$3:$D$32))

    Right-click chart and choose Select Data.

    Below 'Legend Entries (Series)' click Edit.
    Set 'Series Name' to: =Sheet1!$D$3
    Set 'Series Values' to: =Sheet1!Savings
    Click OK

    Below 'Horizontal (Category) Axis Labels' click Edit.
    Set 'Axis Label Range' to: =Sheet1!Age
    Click OK

    Click OK to return to your worksheet/chart.

  3. #3
    Registered User
    Join Date
    10-23-2019
    Location
    Singapore
    MS-Off Ver
    2013
    Posts
    17

    Re: How to auto-adjust chart plotting when there is "" in the data series?

    Thanks Sir! You are a champion!

    I am a newbie to advanced formulas, only started last year to do up a planner for my clients.
    I am very keen to learn more!

    So now i will need to find out how i can input your guidance into my planner.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 07-21-2015, 03:48 AM
  2. Formula Adjust. Need =IF"" data to remain once entered when =IF"" changes.
    By EDDYJAMES in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2015, 03:44 PM
  3. [SOLVED] Chart legend adds verbiage "(small team)" at end of data series name
    By jopeters in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-16-2013, 10:01 AM
  4. [SOLVED] Excel Stock Chart - "Close marker" refers to incorrect data series
    By MartinShort in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 10-07-2012, 10:50 AM
  5. Removing specific data "of a series" from a chart only
    By ez08mbba in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-01-2011, 05:03 PM
  6. how to adjust chart series formula to include the "indirect" function
    By luv2glyd in forum Excel Charting & Pivots
    Replies: 13
    Last Post: 06-13-2010, 07:35 AM
  7. How can I make a Chart data series treat blanks as "Empty" cells
    By XLADLK in forum Excel Charting & Pivots
    Replies: 10
    Last Post: 01-11-2006, 12:25 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1