Forum Discussion
Grouped pivot table
Hello guys,
I have a support agent data table, then I need to generate a report with the requirements data (creation date, type, requester, assigned agent, requester, requirement type and requirement age). So I need to create a pivot table where I can list all the requests grouped by agent and be able to see the requests that are older than 7 days sorted from the oldest to the most recent.
How can I do it?
What fields go in the rows and what fields go in the columns?
Thank you so much.
8 Replies
To create a pivot table in Excel 365 that lists support requests grouped by agent and displays requests older than 7 days, follow these steps:
- Ensure that your support agent data table has the necessary columns, such as creation date, type, requester, assigned agent, and requirement age.
- Select any cell within your support agent data table.
- Go to the "Insert" tab in the Excel ribbon and click on "PivotTable" (or "PivotTable" under "Tables" if you're using a newer version of Excel).
- In the "Create PivotTable" dialog box, make sure the correct range for your support agent data is selected. Choose whether to place the pivot table on a new worksheet or an existing one, and click "OK".
- The PivotTable Field List will appear on the right side of the Excel window. Arrange the fields as follows:
- Drag and drop the "Assigned Agent" field to the "Rows" area. This will group the requests by agents.
- Drag and drop the "Requirement Age" field to the "Values" area. This will display the count of requests for each agent.
- To filter and sort the requests that are older than 7 days, do the following:
- Click on the arrow next to the "Requirement Age" field in the "Values" area.
- Select "Value Filters" and then "Greater Than Or Equal To".
- In the dialog box, enter "7" and click "OK".
- Click on the drop-down arrow next to the "Requirement Age" field in the "Values" area again.
- Choose "Sort Largest to Smallest" to arrange the requests from the oldest to the most recent.
- You can add additional fields to the rows or columns area based on your reporting requirements. For example, you can add "Creation Date", "Type", and "Requester" to the "Rows" area to provide more detailed information in the pivot table.
- Customize the pivot table's formatting, layout, and any other settings as needed.
Your pivot table should now display support requests grouped by agents, with requests older than 7 days sorted from oldest to most recent.
Hello!
I've been playing around with this to see if I can get it to work, but have been unsuccessful.
Here is the way I think it might work:
1.- I add the status field as a filter.
2.- I add the agent field to the row
3.- I add the req ID field to the row, this makes the field to be placed below the agent, which is fine.
4.- I add the request date field to the row and this causes the field to be placed below the ID when it should be next to it. The same happens with all the fields.
So, how do I get the records to display as a columnar report grouped by agent?Thank you so much in advance,
First of all thank you so much for taking the time to answer me.
I have followed your indications and I have not obtained the expected report, surely I have not explained myself well, so I attach an image of what would be the report I am trying to make.
A picture says more than a thousand words 🙂
Thank you again,
