negative number to postive numbers in pivot table/ line graph

[Izhar]

Good Morning,

i have finance data that has credit values (-ve) when i use this data


i want to flip the blue line in the attacment to show as + ve and run with the red line

dont want to change the source data

please help

thanks

 

[r2c2]

@Izhar I am afraid Pivot charts do not offer such flexibility. I could be wrong, but without adding a new field (thru Pivot Options > Fields, Items & Sets > Calculated Field) that ABS() the amount, you won't be able to cheat the pivot chart.

That said, you can create a normal line chart from the pivot data. Just select any blank non-pivot cell in your worksheet and insert regular line chart.

1. Select credit's column in your pivot and go to Formulas > Define name. Give this range a name like credits, but in the formula area use a reference like this: =-Sheet1!$J$4:$J$39 (note the negative sign)
2. Right click on the blank line chart and go to select data
3. Add debits (+ve values) from your pivot
4. Add credits (-ve values) by using the named range. you may need to give the name as Sheet1!debits if Excel complains when you use just the name.
5. Close the data screen and your chart shows negative line above zero.

See this attachment with made up data. I have used the words debit for negative and credit for positive as that is the norm.

 

[Izhar]

thank you for your reply and solution

soloves the issue in a easier way than what i thought, however there is another problem

i want the chart to auto arrange for scale that is if i choose to have fewer Row labels the line chart should show chart for only those items selected and not the entire range

how do i do that?

Thanks,

 

[NARAYANK991]

Hi ,

See the attached file.

Narayan

 

[Izhar]

Hi ,

See the attached file.

Narayan

Many Thanks, works like a dream

can you please share how you went about doing this?

Thanks,

 

[NARAYANK991]

Hi ,

Basically the 2 chart series and the horizontal axis are now referring to named ranges credits , debits and Row_Labels.

If you click on the Name Manager , you can access these named ranges and see what they are referring to.

You need to ensure that the Pivot Table does not have the Grand Total row.

When using the named ranges in charts , go into Select Data , and in the box for the series enter the named range including the sheet name , as in :

=Sheet1!credits

Excel will automatically replace the sheet name with the complete name inclusive of the workbook name.

Narayan

 

[Izhar]

Thank you for the information