Pivot table column Filtering

%3CLINGO-SUB%20id%3D%22lingo-sub-1681715%22%20slang%3D%22en-US%22%3EPivot%20table%20column%20Filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681715%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20receive%20a%20file%20in%20which%20project%20expenses%20are%20being%20reported%20with%20the%20following%20layout%20that%20goes%20so%20throughout%20the%20year%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22capture%204.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219419i91E390E47BBCF7C8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22capture%204.PNG%22%20alt%3D%22capture%204.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20create%20a%20pivot%20table%20in%20which%20I%20can%20easily%20view%20planned%2C%20actual%20or%20both%20values%20without%20having%20to%20manually%20drag%20the%2012%20months%20each%20time.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20best%20solution%20I've%20found%20so%20far%20is%20creating%20a%20new%20table%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Capture%206.PNG%22%20style%3D%22width%3A%20943px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219420iE90B3EA97D1FD6AD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Capture%206.PNG%22%20alt%3D%22Capture%206.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20gives%20me%20the%20desired%20pivot%20table%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22pivot.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219421iB3D0EACD33903A81%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22pivot.PNG%22%20alt%3D%22pivot.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20doing%20so%20requires%20to%20have%202%20rows%20on%20the%20original%20file%20and%20doesn't%20allow%20me%20to%20group%20the%20information%20by%20quarter%20when%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20get%20the%20second%20pivot%20table%20using%20the%20first%20table%20layout%20as%20source%3F%3C%2FP%3E%3CP%3EAdditionaly%2C%20is%20there%20a%20way%20to%20group%20the%20months%20into%20Quarters%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1681715%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682051%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20column%20Filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682051%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20grouping%20the%20months%20to%20querters%20you%20need%20to%20transform%20your%20table%20as%20fallows%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EType---Real%2FPlan---Date---Project---Budget%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20Date%20column%20need%20to%20have%20real%20dates%20like%201%2F1%2F2020.%20So%20when%20you%20create%20a%20pivot%20table%20you%20will%20have%20dates%20on%20the%20column%20and%20than%20right%20click%20on%20the%20column%20and%20select%20%22Group%20By%22%20to%20have%20quarterly%20and%20yearly%20grouping.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20your%20second%20question%20you%20can%20check%20below%20web%20site.%20Not%20the%20exact%20answer%20however%20it%20may%20help%20you.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexcelchamps.com%2Fblog%2Flink-slicer-to-multiple-pivot-tables%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexcelchamps.com%2Fblog%2Flink-slicer-to-multiple-pivot-tables%2F%3C%2FA%3E%3C%2FP%3E%3CDIV%3E%3CDIV%20class%3D%22gtx-trans-icon%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682088%22%20slang%3D%22en-US%22%3ERe%3A%20Pivot%20table%20column%20Filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682088%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798036%22%20target%3D%22_blank%22%3E%40Alejandro_Rivero%3C%2FA%3E%26nbsp%3BIf%20you're%20open%20to%20a%20solution%20using%20Power%20Query%20(PQ)%2C%20have%20a%20look%20at%20the%20attached%20file.%20I%20created%20a%20data%20table%20based%20on%20the%20first%20picture%20in%20your%20question.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20steps%20applied%20in%20PQ%20are%3A%3C%2FP%3E%3CP%3E1%3A%20Query%20the%20data%20table%20(A1%3AJ12)%3C%2FP%3E%3CP%3E2%3A%20Remove%20the%20Quarter%20columns%20(not%20needed%2C%20as%20the%20pivot%20table%20will%20calculate%20them%20for%20you)%3C%2FP%3E%3CP%3E3%3A%20Unpivot%20the%20month%20plan%20and%20real%20columns%3C%2FP%3E%3CP%3E4%3A%20Split%20Month%20name%20and%20Plan%2FReal%20into%20two%20separate%20columns%3C%2FP%3E%3CP%3E5%3A%20Rename%20the%20two%20new%20columns%3C%2FP%3E%3CP%3E6%3A%20Keep%20first%20three%20characters%20of%20month%20name%20(i.e.%20%22Mmm%22)%3C%2FP%3E%3CP%3E7%3A%20Create%20a%20date%20from%20%22Mmm%22%20and%20set%20correct%20type%3C%2FP%3E%3CP%3E8%3A%20Load%20table%20back%20to%20Excel%20(starting%20at%20A8)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20you%20can%20create%20a%20pivot%20table%20using%20the%20dates%20as%20row%20headers%20and%20group%20them%20by%20quarter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Visitor

Hello 

 

I receive a file in which project expenses are being reported with the following layout that goes so throughout the year:

capture 4.PNG

 

I want to create a pivot table in which I can easily view planned, actual or both values without having to manually drag the 12 months each time.

 

The best solution I've found so far is creating a new table like this:

Capture 6.PNG

 

Which gives me the desired pivot table:

pivot.PNG

 

However doing so requires to have 2 rows on the original file and doesn't allow me to group the information by quarter when needed.

 

Is there a way to get the second pivot table using the first table layout as source?

Additionaly, is there a way to group the months into Quarters?

 

Thank you

2 Replies
Highlighted

For grouping the months to querters you need to transform your table as fallows

 

Type---Real/Plan---Date---Project---Budget

 

and Date column need to have real dates like 1/1/2020. So when you create a pivot table you will have dates on the Pivot Table and than right click on the column name, one of the dates on the column of Pivot table (not the Date shown on "Pivot table fields" column area), and select "Group By" to have quarterly and yearly grouping.

 

For your second question you can check below web site. Not the exact answer however it may help you.

https://excelchamps.com/blog/link-slicer-to-multiple-pivot-tables/

 
Highlighted

@Alejandro_Rivero If you're open to a solution using Power Query (PQ), have a look at the attached file. I created a data table based on the first picture in your question.

 

The steps applied in PQ are:

1: Query the data table (A1:J12)

2: Remove the Quarter columns (not needed, as the pivot table will calculate them for you)

3: Unpivot the month plan and real columns

4: Split Month name and Plan/Real into two separate columns

5: Rename the two new columns

6: Keep first three characters of month name (i.e. "Mmm")

7: Create a date from "Mmm" and set correct type

8: Load table back to Excel (starting at A8)

 

Now you can create a pivot table using the dates as row headers and group them by quarter.