0

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).

neo33
  • 1,809
  • 5
  • 18
  • 41
  • Is column A 'products' and column E 'Total'? Do you have a hidden column in between, given that your example only shows 2 columns in between? Anyway, that's beside the point. Why not set your range to Sheet!$A:$E (i.e. the whole lot) and then only actually use the columns you want to chart? – sk877 Aug 09 '16 at 13:43
  • Yes 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, sorry for not to be so clear, but any how i am getting the column E applying a formula to the column B and C, that is: =SUM(B2:C2). – neo33 Aug 09 '16 at 14:11

1 Answers1

1

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Thanks for the support, i really appreciate the help, i am going to prove it. – neo33 Aug 10 '16 at 14:57
  • I really appreciate your consideration, I tried with this but i am getting: The Pivot field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns, if you are changing the name of a PivotTable field, you must type a new name for the field. – neo33 Aug 11 '16 at 14:11
  • I tried with adding text into the cell that was empty but do you know if i have to tell excel that I am using that row as heading? – neo33 Aug 11 '16 at 15:33
  • 1
    Thanks I finally was able to plot this graph, I really appreciate the support. – neo33 Aug 11 '16 at 16:19