Pls help me create Pivot Table example

%3CLINGO-SUB%20id%3D%22lingo-sub-3278329%22%20slang%3D%22en-US%22%3EPls%20help%20me%20create%20Pivot%20Table%20example%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278329%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20create%20a%20PT%20example%20to%20demonstrate%20an%20issue%20and%20a%20possible%20solution.%26nbsp%3B%20But%20sigh%2C%20I%20know%20nothing%20about%20PTs.%26nbsp%3B%20I%20know%3A%26nbsp%3B%20I%20should%20take%20the%20time%20to%20learn.%26nbsp%3B%20But%20every%20time%20I%20try%20(not%20very%20hard%3B%20sigh)%2C%20I%20lose%20patience.%26nbsp%3B%20My%20guess%20is%3A%26nbsp%3B%20a%20PT-knowledgeable%20person%20can%20create%20the%20example%20in%20a%20heartbeat%3B%20or%20maybe%20a%20New%20York%20minute%20(wink).%26nbsp%3B%20So%20I%20would%20appreciate%20it%20if%20some%20kind%20soul%20would%20take%20a%20moment%20to%20do%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESee%20the%20textbox%20in%20the%20attached%20Excel%20file.%26nbsp%3B%20I%20hope%20it%20is%20self-explanatory.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20indulging%20my%20laziness.%26nbsp%3B%20(Mea%20culpa!)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPS....%26nbsp%3B%20I%20hope%20my%20example%20lends%20itself%20to%20creating%20a%20Pivot%20Table.%26nbsp%3B%20If%20not%2C%20please%20embellish%20the%20data%20as%20needed%20so%20that%20the%20PTs%20make%20sense.%26nbsp%3B%20Also%2C%20I%20know%20that%20I%20can%20eschew%20the%20PTs%20and%20use%20SUMIF%20instead.%26nbsp%3B%20In%20fact%2C%20that%20is%20what%20__I__%20would%20normally%20do%2C%20since%20obviously%20I%20do%20not%20use%20PTs.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3278329%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278859%22%20slang%3D%22en-US%22%3ERe%3A%20Pls%20help%20me%20create%20Pivot%20Table%20example%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278859%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EI%20hope%20it's%20not%20%3DROUND(SUMIF(%24B%244%3A%24B%2425%2C%20H4%2C%20%24C%244%3A%24C%2425)%2C%203).%26nbsp%3B%20That%20would%20seem%20to%20defeat%20the%20purpose%20of%20using%20a%20PT.%26nbsp%3B%20(Perhaps%20because%20my%20example%20is%20too%20simple.)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBingo!%20Take%20that%20%22perhaps%22%20seriously%3A%20If%20you%20can%20get%20from%20point%20A%20to%20point%20B%20by%20doing%20a%20formula%20such%20as%20you've%20written%2C%20why%20make%20it%20more%20complicated%3F%20The%20purpose%20of%20a%20Pivot%20Table%20comes%20into%20play%20when%20the%20data%20you're%20dealing%20with%2C%20the%20summary%20you're%20seeking%2C%20can't%20be%20as%20simply%20resolved%20with%20a%20single%20simple%20formula.%20One%20of%20the%20wonderful%20features%20of%20Excel%20is%20that%20there%20are%20frequently%20(I%20would%20even%20say%20%22usually%22)%20several%20ways%20to%20get%20from%26nbsp%3Bpoint%20A%20to%20point%20B%3B%20so%20unless%20the%20way%20you've%20found%20is%20convoluted%2C%20there's%20not%20a%20lot%20of%20reason%20to%20go%20looking%20for%20another.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%20style%3D%22%20padding-left%20%3A%2030px%3B%20%22%3E%3CEM%3EYes%2C%20I%20know%20that%20PTs%20are%20most%20useful%20with%20more%20complex%20data%20and%20designs.%26nbsp%3B%20But%20when%20I%20create%20examples%20for%20educational%20purposes%2C%20I%20like%20to%20KISS.%26nbsp%3B%20%22Everything%20should%20be%20as%20simple%20as%20possible%2C%20but%20not%20simpler.%22%20(wink)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20is%20complex%20and%20there%20is%20complex.%20The%20example%20I%20sent%20you%20is%20NOT%20particularly%20complex%3B%20it%20just%20has%20another%20dimension%20to%20it%20and%2C%20as%20such%2C%20is%20a%20good%20%3CU%3Esimple%3C%2FU%3E%20example%20of%20where%20the%20Pivot%20Table%20helps%20summarize%20data%3A%20it%20creates%20a%20two%20dimensional%20array.%20Your%20examples%20are%20so%20simple%20that%20they%20don't%20need%20the%20Pivot%20Table%20tool.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20the%20way%2C%20did%20you%20even%20look%20at%20the%20resources%20I%20pointed%20you%20to%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278382%22%20slang%3D%22en-US%22%3ERe%3A%20Pls%20help%20me%20create%20Pivot%20Table%20example%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278382%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F425987%22%20target%3D%22_blank%22%3E%40mathetes%3C%2FA%3E%26nbsp%3B%20wrote%3A%26nbsp%3B%20%60%60I%20did%20your%20first%20PT%60%60%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%20I%20hope%20someone%20can%20provide%20PT%202%20with%20explicitly-rounded%20values%20or%20formulas%20(calculated%20fields%3F).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20it's%20not%20%3DROUND(SUMIF(%24B%244%3A%24B%2425%2C%20H4%2C%20%24C%244%3A%24C%2425)%2C%203).%26nbsp%3B%20That%20would%20seem%20to%20defeat%20the%20purpose%20of%20using%20a%20PT.%26nbsp%3B%20(Perhaps%20because%20my%20example%20is%20too%20simple.)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYes%2C%20I%20know%20that%20PTs%20are%20most%20useful%20with%20more%20complex%20data%20and%20designs.%26nbsp%3B%20But%20when%20I%20create%20examples%20for%20educational%20purposes%2C%20I%20like%20to%20KISS.%26nbsp%3B%20%22Everything%20should%20be%20as%20simple%20as%20possible%2C%20but%20not%20simpler.%22%20(wink)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3278358%22%20slang%3D%22en-US%22%3ERe%3A%20Pls%20help%20me%20create%20Pivot%20Table%20example%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3278358%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F146717%22%20target%3D%22_blank%22%3E%40Joe%20User%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20did%20your%20first%20PT.%20It%20was%20so%20easy%2C%20I'm%20not%20sure%20what%20your%20difficulty%20is%2C%20other%20than%20an%20unwillingness%20to%20%22play%22%20a%20bit.%20Frankly%2C%20one%20usually%20sees%20Pivot%20Tables%20done%20with%20more%20data%2C%20more%20rows%20and%20columns.%20See%20the%20attached%20example%20of%20a%20pivot%20table%20to%20summarize%20things%20by%20category%20and%20month.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMaybe%20this%20website%20will%20help.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fglossary%2Fpivot-table%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fglossary%2Fpivot-table%3C%2FA%3E%3C%2FP%3E%3CP%3EThere%20are%20also%20many%20MANY%20YouTube%20videos.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Super Contributor

I want to create a PT example to demonstrate an issue and a possible solution.  But sigh, I know nothing about PTs.  I know:  I should take the time to learn.  But every time I try (not very hard; sigh), I lose patience.  My guess is:  a PT-knowledgeable person can create the example in a heartbeat; or maybe a New York minute (wink).  So I would appreciate it if some kind soul would take a moment to do that.

 

See the textbox in the attached Excel file.  I hope it is self-explanatory.

 

Thanks for indulging my laziness.  (Mea culpa!)

 

PS....  I hope my example lends itself to creating a Pivot Table.  If not, please embellish the data as needed so that the PTs make sense.  Also, I know that I can eschew the PTs and use SUMIF instead.  In fact, that is what __I__ would normally do, since obviously I do not use PTs.

3 Replies

@Joe User 

 

I did your first PT. It was so easy, I'm not sure what your difficulty is, other than an unwillingness to "play" a bit. Frankly, one usually sees Pivot Tables done with more data, more rows and columns. See the attached example of a pivot table to summarize things by category and month.

 

Maybe this website will help. https://exceljet.net/glossary/pivot-table

There are also many MANY YouTube videos.

@mathetes  wrote:  ``I did your first PT``

 

Thanks.  I hope someone can provide PT 2 with explicitly-rounded values or formulas (calculated fields?). 

 

I hope it's not =ROUND(SUMIF($B$4:$B$25, H4, $C$4:$C$25), 3).  That would seem to defeat the purpose of using a PT.  (Perhaps because my example is too simple.)

 

Yes, I know that PTs are most useful with more complex data and designs.  But when I create examples for educational purposes, I like to KISS.  "Everything should be as simple as possible, but not simpler." (wink)

@Joe User 

I hope it's not =ROUND(SUMIF($B$4:$B$25, H4, $C$4:$C$25), 3).  That would seem to defeat the purpose of using a PT.  (Perhaps because my example is too simple.)

 

Bingo! Take that "perhaps" seriously: If you can get from point A to point B by doing a formula such as you've written, why make it more complicated? The purpose of a Pivot Table comes into play when the data you're dealing with, the summary you're seeking, can't be as simply resolved with a single simple formula. One of the wonderful features of Excel is that there are frequently (I would even say "usually") several ways to get from point A to point B; so unless the way you've found is convoluted, there's not a lot of reason to go looking for another. 

 

Yes, I know that PTs are most useful with more complex data and designs.  But when I create examples for educational purposes, I like to KISS.  "Everything should be as simple as possible, but not simpler." (wink)

 

There is complex and there is complex. The example I sent you is NOT particularly complex; it just has another dimension to it and, as such, is a good simple example of where the Pivot Table helps summarize data: it creates a two dimensional array. Your examples are so simple that they don't need the Pivot Table tool.

 

By the way, did you even look at the resources I pointed you to?