Plotting empty cells in charts

    I have been looking for a solution for some time to a charting problem.


    If the source cell of a linier chart is empty no value is plotted. If the source cell has anything in it (a zero, a string or a formula) the cell is plotted as zero and the line on the chart plots to the x-axis.


    Example: Say you’re charting sales history by month and you’re six months into a year. The sales data is a roll-up of company divisions by using sum formulas in each of the 12 months source cells of the year. Then July through December plot as zero sales and the chart line shows a sudden drop to zero sales in July.


    One solution is to leave the monthly cells empty and manually or through VBA add the sum formula as the months progress. What a drag.


    Is there any way to keep all the formulas in the source cells and have the chart plot the line only when the value of the source cell is not zero?

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    I had a similar problem, and to resolve this issue, I did the following:


    Whenever a cell is found blank, set the formula of that cell to "=NA()"


    A chart will not plot anything if the cell contains #N/A

    Another way is to use the formula below:
    Months are in column A
    The calculated formula is column B
    Column C is sales
    Column D is forecast
    Your formula would be =IF(OR(C2=0,D2=0),"",C2-D2)
    This way B would produce a blank and threfore not be plotted.

    I have a follow up question to this post.


    The formula I have is as follows:


    =IF(ISERROR(VLOOKUP(Year5,INDIRECT(DataTable),COLUMN(),FALSE))=TRUE," ",VLOOKUP(Year5,INDIRECT(DataTable),COLUMN(),FALSE))


    As you can see, the formula pulls the data from another table. If I change the formula to put NA() in the true portion, it does not plot (as desired).


    But how can I then "Hide" the #N/A in the chart's data table? I tried to use conditional formatting but could not get it to work. I tried if cell = "#N/A", if cell =NA(), if Cell="=NA()", if Formula = NA() but no luck?

    I had the same problem but got around it with conditional formating by checking to see if the cell had a value other than zero.


    First set color of text to same color as background say white on white. This will hide the #N/F.


    Then conditional format Cell Value Is > Not Equal To > 0
    Set conditional format to text color other than background say black on white.

    [FONT="Arial Black"][COLOR="blue"][SIZE="4"]Bill[/SIZE][/COLOR][/FONT]
    Tip: To avoid chasing code always use Option Explicit.

    The way to directly tie the conditional formatting to #N/A is to use
    Formula Is =IsError(A10)
    where A10 refers to the active cell. Then set the desired conditional format if that cell contains an error, which in theis case is #N/A.

    Thanks Derk, that worked. I was close. I had tried IsError but did not have the cell referance after it. Worked like a charm.

    Re: Plotting empty cells in charts


    I had the same problem and this was the solution, except it lead to another problem. My table averaged these cells, and now with the error, the average does not work. Any ideas?

    Re: Plotting empty cells in charts


    I'm forcing the cells to "N/A#" if null, so they don't plot on the graph. My average includes these cells, and others that have valid data, and returns a N/A# as well. Is there a formula I could use to overlook these cells if they contain N/A#? Thanks!

    Re: Plotting empty cells in charts


    Here is one way. Enter the following formula (after adjusting the references) as an array function by holding down the Ctrl and Shift keys when you push the Enter key.
    =SUM(IF(ISNUMBER(B2:F2),B2:F2,0))/COUNT(B2:F2)
    Excel will then put curly brackets around the formula to indicate it's an array formula.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login