+ Reply to Thread
Results 1 to 2 of 2

Bar Chart Showing Errors / Empty Values

  1. #1
    Registered User
    Join Date
    New York, NY
    MS-Off Ver

    Bar Chart Showing Errors / Empty Values

    Hi all - I am new to this forum but I am looking forward to being active on it moving forward.

    I am having a lot of difficulty with a bar chart I am trying to put together showing any combination of "NAME?", a blank (""), or #N/A depending on how I set up by If functions from the source data.
    I have the data pulling from 35 rows, for which the number of rows actually being used will depend on that specific month's underlying data.

    However, I want to have it set up in a way so that it automatically pulls and formats correctly, regardless of how many rows are being used.
    Thus far, I have been unable to ignore "blank" (I put blank in quotes since there are still formulas there) for this bar chart like I was able to do for a pie chart (which seems more capable to ignore #N/A values).

    Hope the above makes sense, and please let me know if not. Any support is so so greatly appreciated!
    Attached Images Attached Images

  2. #2
    Forum Guru
    Join Date
    North America
    MS-Off Ver
    2002/XP and 2007

    Re: Bar Chart Showing Errors / Empty Values

    I see two possible ways to do this:

    1) Filters. Assuming the source data is arranged so that autofilter can be applied to the source range (https://www.wikihow.com/Use-AutoFilter-in-MS-Excel ), you can create the chart exactly as you have now. Then, tell Excel to use autofilter on this range, click on the dropdown for one of the columns, and apply an appropriate filter that will show only the meaningful rows. Assuming your chart's hidden and empty cell setting is set to "not show", then the chart will follow the filter. Everytime new data comes in, refresh the filter to get the chart up to date.

    2) Use dynamic named ranges to build a dynamic chart. https://chandoo.org/wp/dynamic-chart-data-series/ The tutorial uses COUNTA() (assuming that you are only needing this so the chart knows how many used rows there are) where you will need more of a COUNTIFS() (to determine how many of the used rows are meaningful).

    Try those two approaches and see which one is more in line with how you want to interact with the spreadsheet.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

+ 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. Chart showing Three values
    By rp83 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 10-13-2018, 11:22 AM
  2. Replies: 1
    Last Post: 09-09-2017, 04:54 AM
  3. Not showing 0(zero) values in a chart.
    By scottishmac in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 06-17-2015, 04:48 AM
  4. Replies: 2
    Last Post: 10-06-2014, 01:03 AM
  5. Chart Source values showing as zero in chart
    By Bernieboo62 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 01-29-2014, 10:53 PM
  6. Handling Errors in Values & Empty cells in Excel 2007
    By ExcelTip in forum Tips and Tutorials
    Replies: 0
    Last Post: 11-28-2007, 06:37 AM
  7. [SOLVED] How can I chart a data series without showing empty cells?
    By Brent in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-19-2006, 11:40 AM

Tags for this Thread


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