Excel 2013 Pivot table, how to filter rows/columns before pivoting

Question

I have some raw data in my spreadsheet with columns like role, role status, object, object status, so the data indicates the relationship between role and object.

The some data view looks like

role1,1,obj1,1
role1,1,obj2,0
role2,0,obj1,1
role3,1,obj3,1

I want to see a matrix to represent what role can access what object. Meanwhile I only want to include role status = 1 and object status = 1 in my pivot table.

╔══════╦═══════╦═══════╗
║      ║ role1 ║ role3 ║
╠══════╬═══════╬═══════╣
║ obj1 ║     1 ║       ║
║ obj3 ║       ║     1 ║
╚══════╩═══════╩═══════╝

I can generate pivot table from data easily, but I have no clue how to do a pre-filtering of the data.

Answer 1

You can't do exactly what you're looking for.

Closest workaround is to add a new column to your data where you enter different values for rows to include / exclude. Then it's easy to filter on pivot table.

Answer 2

All data sets should be columns and then apply pivot table to make the summery table as you like. Example:

COURSE  SEMISTER
C11    First
C12    Second
C13    First
C14    Second

One pivot table will be:

Row Labels  Count of COURSE
First          2
Second         2
Grand Total    4