• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide base columns in a pivot table

C

Clemens

New Member
Hi,

I have created a pivot table with a field represented as a percent of another field.

I now want to create a graph based on this report.

However, the base columns show up (always 100%) and I do NOT want them in the graph!

If I wanted to print the report I could simply hide the base columns BUT for graphing I am unable to suppress the bars for the base columns.

In the attached spreadsheet:
1. Sheet "Proportion", I want to graph just "Discharge / Death" (the yellow column).
2. Sheet "SPINE vs HIMS" I want to graph only Admission, Death and Discharge for SPINE only (The yellow columns)

I can click on a data series, select "format data series"and set fill to "no fill". This might be if i Have only one series. But with several series as in (2) above it is visually wrong. More importantly this is not the correct way to do it....!

Any Idea how I could resolve this?

Clemens
 

Attachments

i'm confused as to why when i try to filter your pivot tables 'source' field to show only spine, all the values turn to "#NA"...

the closest i got due to the above was to put you event field in the filter area/box and then just fill the HIMS with white... ?

another questions... if you are summing 'numbers' why are they a percentage? how are you converting it to a percentage?

EDIT: when i try to recreate your pivots & charts, it say that the data range is not a valide reference. excel doesnt like your data/arrangement for some reason.. any ideas?
 
Last edited:
Hi Clemens!

AFAIK, When working with Pivot Chart.. you have to loose.. a alot of feature of Normal Chart.. like Change Series, add Daata Label.. but you have a huge advantage of reflect the view as per applied filter and data Crunching in Pivot..

So if you want to loose those facility.. and just want to display the chart with a single bar.. you can..

  • Select a Blank cell.
  • Insert a Bar Chart.
  • and Now simple Add Data Series 1 & Series 2
19-12-2013 02-50-39.png
 
@Debraj nailed it. Just delete the pivot chart and insert a normal chart.

Then, manual select the data that you want.

pic1.PNG

You'll probably find that the horizontal axis has numbers, like Debraj's does; if you want the months shown, make sure to select the month's column as your Horizontal (Category) Axis Labels.

pic2.PNG

I think the attached file might be what you are looking for.
 

Attachments

Thanks Debraj,

It works like a charm. I Actually do not loose a lot of the pivot chart functionality as when I expand / collapse part / the whole pivot table the graph created as you suggest also changes to reflect the changes in the pivot table.

You have solved my problem, thanks.

Clemens
 
You must log in or register to reply here.
Back
Top