+ Reply to Thread
Results 1 to 5 of 5

Removing #N/A or blank cells from excel bar chart

  1. #1
    Registered User
    Join Date
    07-11-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    78

    Removing #N/A or blank cells from excel bar chart

    HI,

    I have a "Usage" chart which is taking values from cell range C3:D38. The chart is displaying #N/A values which i don't want to be displayed.
    I don't want the user to manually adjust the cell range to C3:D11 and i would want a formula that would automatically detect the range of values for the chart ie start from Cell C3 and stop at the last non blank cell value so that when new data gets added to "Data" tab, then the new values get included in the chart.

    How do i remove #N/A's from the chart and how do i teach the chart to auto detect the range of cells with values and not include blank or #N'A values.
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Removing #N/A or blank cells from excel bar chart

    Try in c4:

    =IFERROR(VLOOKUP(CONCATENATE(A4,B4),Data!A:C,3,FALSE),"")

    Drag down
    Quang PT

  3. #3
    Registered User
    Join Date
    07-11-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    78

    Re: Removing #N/A or blank cells from excel bar chart

    I tried that...however, my chart is displaying those blank cells and my actual values are getting pushed all the way to the bottom. How can the chart automatically exclude blank cells so that my chart only displays cells with values in themAttachment 697425?

  4. #4
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Removing #N/A or blank cells from excel bar chart

    SO, try to use Define name to create a dynamic range

    Name: "usea"
    Refer to: =OFFSET(Pivot!$D$4,,,COUNTA(Pivot!$A$4:$A$38),)

    The adjust the data of chart:
    Edit the Series:
    Series Value: Full of current file and username
    Attached Images Attached Images
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-11-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    78

    Re: Removing #N/A or blank cells from excel bar chart

    wow...that worked...thank you so much !!
    Last edited by redJohn89; 09-29-2020 at 12:17 PM.

+ 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. Removing Blank Cells in the Pivot Table. (Blank) - Out of Range Cells
    By satishmen in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-09-2019, 06:30 AM
  2. Removing blank columns in clustered column chart
    By Pharmacisticus in forum Excel General
    Replies: 1
    Last Post: 05-07-2019, 10:30 AM
  3. [SOLVED] Removing blank cells
    By Notters in forum Excel General
    Replies: 4
    Last Post: 02-07-2014, 09:12 AM
  4. Removing blank cells from csv
    By steve777888 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-27-2013, 10:43 AM
  5. Removing Blank cells from chart.
    By agricola in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-02-2012, 09:28 AM
  6. removing blank values (blank or "0") from a dynamic chart
    By rob78 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-26-2011, 04:19 PM
  7. [SOLVED] Removing blank cells...
    By Jambruins in forum Excel General
    Replies: 0
    Last Post: 03-31-2006, 03:10 PM

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