Question

Is there a way to make a pivot table from a data set to show the following:

- make gender the columns (one column for male and one for female)
- rows are age increments (18 - 30, 31 - 40, 41 - 50, 51 - 60, 61 - 70)
- information provided within the pivot table is the average salary of everyone within the age increment (for example, I want to find the average salary of a male between the ages of 41 - 50, or the average salary of a female between the ages of 31 - 40). I'm not able to provide a data set because it's too large, but if instructions can be provided, that would be amazing!

Answer 1

Go to Insert in the menu bar and choose pivot table. After you have selected the range of the table with all the columns with unique header names click OK.

Choose gender in columns, Age in rows and salary in Values. Then click on the option as shown below and go to Value Field Settings.

PivotTable Fields Choose fields to add to report: Search Gender Age Salary in thousands More Tables... Move Up Move Down Move to Beginning Move to End Move to Report Filter Move to Row Labels Move to Column Labels Move to Values Drag fields between areas below: Y ▼ Filters Σ Remove Field Value Field Settings... Rows

Chosse Average as shown below and click OK. You will get your required pivot table.

PivotTable Fields to add to report: Value Field Settings Source Name: Salary in thousands Custom Name: Average of Salary in thousands Summarize Values By Show Values As Summarize value field by Choose the type of calculation that you want to use to summarize data from the selected field Sum Count Average Max Min Produc between areas below: Cancel Number Format OK Columns Gender Σ Values Rows Age Sum of Salary in thousands