Pivot Tables

%3CLINGO-SUB%20id%3D%22lingo-sub-1326097%22%20slang%3D%22en-US%22%3EPivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326097%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20appreciate%20assistance%20regarding%20the%20attached%20spreadsheet.%20I%20have%20removed%20dollar%20information%20for%20privacy%20reasons.%20I%20am%20attempting%20to%20create%20a%20pivot%20table%20based%20on%20columns%20B%2C%20C%2C%20D%2C%20E%2C%20and%20F%20%3CSPAN%3Ebuilding%26nbsp%3B%3C%2FSPAN%3Ea%20pivot%20from%20information%20in%20the%20gray%20highlighted%20rows%20which%20say%2C%20%22Total%22%20(column%20%22Account%20Numbr2%22).%20Dollar%20amounts%20would%20be%20reflected%20under%20columns%20E%20and%20F.%20Thank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1326097%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1326645%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20Tables%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1326645%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F631290%22%20target%3D%22_blank%22%3E%40Garrett_Turner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20are%20two%20ways%3A%3C%2FP%3E%3CP%3E1.%20Add%20new%20tab%2C%20select%20insert%20pivot%20table%2C%20and%20select%20the%20range%20B1%3AF166.%26nbsp%3B%20Then%20build%20your%20pivot%20table.%3C%2FP%3E%3CP%3E2.%20Export%20the%20data%20to%20Power%20Query.%26nbsp%3B%20To%20do%20this%2C%20you%20have%20to%20change%20the%20range%20of%20table1%20down%20to%20row%20166.%26nbsp%3B%20Then%20under%20data%2C%20select%20%22from%20table%2Frange%22%20in%20the%20%22get%20and%20transform%20data%22.%26nbsp%3B%20This%20open%20PQ-there's%20lots%20of%20formatting%20you%20could%20do.%26nbsp%3B%20I%20only%20filled%20down%20on%20A%20and%20B%2C%20then%20filtered%20to%20only%20your%20%22total%22%20row.%26nbsp%3B%20Then%20close%20to%20insert%20a%20pivot%20table.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Regular Visitor

I would appreciate assistance regarding the attached spreadsheet. I have removed dollar information for privacy reasons. I am attempting to create a pivot table based on columns B, C, D, E, and F building a pivot from information in the gray highlighted rows which say, "Total" (column "Account Numbr2"). Dollar amounts would be reflected under columns E and F. Thank you.

1 Reply
Highlighted

@Garrett_Turner 

Here are two ways:

1. Add new tab, select insert pivot table, and select the range B1:F166.  Then build your pivot table.

2. Export the data to Power Query.  To do this, you have to change the range of table1 down to row 166.  Then under data, select "from table/range" in the "get and transform data".  This open PQ-there's lots of formatting you could do.  I only filled down on A and B, then filtered to only your "total" row.  Then close to insert a pivot table.  

Hope this helps.