Pivot Table data field

[Deb G.]

I'm sure this is simple and I know I've seen it asked before but can't find the answer via the search function.

I have data that is not numeric. It is set up in columns with Name, Address, Services, Code and Area.

I would like to pivot the data so I can choose various Codes or Area and list the Names, Address, Services under it. How do I go about this?

Using a regular filter isn't useful as I need to filter for more than two types of data in a field.

TIA.

 

[Greg Truby]

Sounds more like a case for advanced filtering than a pivot table.

 

[Deb G.]

Thank you Greg. I had forgotten about advanced filtering - I get so used to custom filters...anyway, if this was just for me to work with I would go with this suggestion. I will be giving this worksheet weekly to about a dozen managers who are NOT even remotely excel-familiar. As it was, I was going to have to teach all of them how to apply and use a filter (!) I soon realized that they have more requirements of the data. I thought a Pivot table that is already set up for them would be really simple for them to use; click a drop down box and choose your criteria.

Is there a way to get this to work using a Pivot? I have seen somewhere where you can set your field to reflect the actual data as opposed to counting, summing, averaging etc.

 

[Greg Truby]

Deb, yes, you can do that. Essentially you just drag your fields onto the Pivot and use Field Settings to change the Subtotals to none. You'll have to try to get your Excel-challenged managers to understand that the logical operator within each column is OR while the logical operator between the columns is an AND (which is the same as with AutoFilter). You (they) won't be able to OR between columns.

But if they are not familiar with Excel, then you are probably being very prudent in trying to give them an interface like PT's. I'm going to go out on a limb and say that the quantity of "how do I do that again" e-mails with the PT-Type interface is going to be a fraction of what you'd see if you try to teach greenhorns Advanced Filtering.

Regards,

Greg

PS - Did you notice that Contextures is run by another *Deb* - Deb Dalgleish, an MS-MVP.

 

[Deb G.]

Perfect!

Thanks for the help Greg. That works just fine and I too believe this is the best (easiest) solution for my managers.

And an added bonus for me is the Contextures site - WOW - great spot. Sorry to say I'm not as savvy as that Deb... yet!