General Pivot Table Questions 1

[MattGreer]

Is it "normal" (or at least not unexpected) to create multiple pivot tables off the same source table in order to display the data multiple different ways and in different groupings?

Is it possible to create two different charts off the same pivot table? Maybe I should ask if it's *expedient*, because if it's possible but it's a very convoluted solution maybe I shouldn't attempt it.

Is it possible to include the Grand Total (Grand Totals for Rows) in a Pivot Chart? If so, how?

Thanks for your help y'all.

-Matt

Thanks!!


Matt

 

[SkipVought]

Hi,

Why should something be "normal"? You have a very versatile tool. Why look for "normal"? You ought to be asking, what's possible! Aren't you playing around with PTs in order to discover what's possible?

You can create lots of PTs from one data source.

However, a PT controls what's displayed in a PC. If you want different PCs you need different PTs.

Frankly, I've not been too satisfied with PCs. So I've created my own Summary Table rather than a PT, often using MS Query with one or more criteria (parameter query) which can be controlled on the sheet. From such, I can create dual axes. For instance, I had a chart that displayed for a selected PartID (from a Data>Validation dropdown), a supply/demand over fixed time showing inventory, requirements and replenishments on one axis and surplus and shortage on the other axis.

What happens in vagueness, stays in vagueness. More specific questions may yield more specific answers.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Heh, well, when I ask for "normal" it's more about what knowledgeable folks such as yourself would do vs someone like me (I know enough to be dangerous). And "normal" from pros would probably be a logical, quick, easy to understand, maintainable way. But you never know until you ask!

I guess my concern with multiple pivot tables is how much this is adding to the size of the workbook because my computer has limited resources. And is there a way around having to copy/paste copy/paste what seems to be sort of the same thing? But I understand now.

Understood, you can basically have one PC per PT.

I did find a way around this, however, because I have access at work to Power Pivot using the OLAP tools and converting the table to Formulas. At that point you can create as many charts as you want, they'll all update whenever the source data updates.

I really should get more familiar with MS Query. And 15 years ago I was really, really adept in using Excel as a front end to Access. But I've slept since then lol.

My vagueness is intentional as I find sometimes when I get too specific folks sometimes focus on the wrong things. I wanted to share the actual spreadsheet, but this particular spreadsheet has oil and gas production data which is confidential. I tried to create something that was anonymized/randomized so that I could share it without the data but that ended up being a good bit of work in and of itself.

Thank you for your help and confirming some of my questions.

-Matt





Thanks!!


Matt

 

[SkipVought]

And is there a way around having to copy/paste copy/paste…?

Depends on specifically what you're attempting to accomplish.

BTW, great reference for Excel charts by J. Walkenbach

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[MattGreer]

Sorry Skip, which reference? I looked up on Amazon but didn't see anything specific to Charts in Excel by Walkenbach. Is the info on charts part of another reference? I have the VBA reference already. It's good stuff.

edit: There's this, but it's from the early 2000's. I didn't see anything else but I do often fail at Google.

Thanks!!


Matt

 

[SkipVought]

Well, yes. I've referred to this book within the past 5-10 years with regard to Excel charts.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein