Search code examples
excelpivot-chart

How to fix the following error plotting some data?


Hello I want to plot some data, I am using excel i want to use a Pivot Chart just to plot two columns, one is called Products and the other Total, it seems really easy but when i press insert PivotChart and select the corresponding columns excel gives me the following range:

Sheet1!$A:$A,Sheet1!$E:$E

Then i press enter and i get:

Data source reference is not valid

I believe that is due the fact that i am using a formula to get the value of the column E however i would like to appreciate any help or suggestion to overcome this situation. in addition i tried saving before to make the graph but i get the same result.

My file is called example.xlsx and looks as follows:

Products    valueA  valueB          Total
ProductA    57        48            105
ProductB    26        17            43

Just to be more clear the column A corresponds to the products, and the column E to the total,there are three columns between them that are B, C and D, but any how i am getting the column E applying a formula to the column B and C, that is: =SUM(B2:C2).


Solution

  • I believe that is due the fact that i am using a formula to get the value of the column E

    That is not the reason.

    How to fix the following error plotting some data?

    Change:

    Sheet1!$A:$A,Sheet1!$E:$E  
    

    to:

    Sheet1!$A:$E  
    

    (as already recommended by @sk877).

    Your PivotChart is expecting a contiguous range, ie , does not serve as a union operator in the Table/Range: (singular) field.