How to create a pivot table that records percentages of success

Hi @ZiggyVanGon

Thank you for contacting Microsoft Q&A Support.

To make sure I fully understand your goals and provide the most suitable solution, could you please clarify the following points: 

1. About the dropdown in the input sheet: 
   • Do you want users to be able to select multiple issues in one cell (e.g., “Late; No quote”), or just one issue per entry? 
   • If you’re looking to create a dropdown list, here’s a guide that may help: Create a drop-down list - Microsoft Support 
2. About the report sheet: 
   • Do you want to break it down per person, showing how many issues each name has and what types? 
   • Would you like to see which issue type is most common per name, or overall? 
3. About the Pivot Table: Is your goal to use the Pivot Table to show counts or percentages?

Once I understand your intended setup, I’ll be happy to guide you through building the report with the right structure and filtering options. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.  

In the pivot table, group your Issues into 2 groups:

1. Group everything except None…
2. Which will automatically provide you with a None group.

In the linked-to workbook below you'll find your pivot table with such grouping and a percent column.

There are many things you can do in a pivot table but here's a selection:

Starting with:

Now double click the Issues cell (it expands/collapses things), it shows:

Now double click say, the No Quote cell, it shows:

etc.

If you always want to see subtotals, for say the Issues column then:

which can leave you with the likes of:

The link to the workbook:

https://app.box.com/s/4qs64ocagolu8tx3opzy5atfelxyt8o3

Hi @ZiggyVanGon

And a big thank you to p45cal for sharing a helpful starting point. The original guidance was spot on, and this reply builds on it with a more detailed, step-by-step walkthrough so you can follow along and recreate the Pivot Table setup yourself. 

Step 1: Prepare Your Source Data 

As you shared: 

Step 2: Insert a Pivot Table 

1. Select your entire data range. 
2. Go to Insert > PivotTable. 
3. Choose to place the Pivot Table in a new worksheet or existing one. 
4. Click OK. 

Step 3: Set Up the Pivot Table Fields 

In the PivotTable Fields pane: 

• Rows: 
  • Drag Issue to the Rows area. 
  • Drag Name below Issue (optional, for breakdown by name). 
• Values: 
  • Drag Issue to the Values area → it will default to Count of Issue. 
  • Drag Issue again to Values → right-click the second one → choose Show Values As > % of Column Total. 

Rename this second column to Percent. 

Step 4: Group the Issues 

To group all issues except "None": 

1. In the Pivot Table (not the field pane), hold Ctrl and click each issue type you want to group: 
   • "Late" 
   • "Missing item" 
   • "No quote" 
   • (Exclude "None") 
2. Right-click one of the selected items → choose Group. 

Excel will create: 

• A group (e.g., "Group1") containing the selected issues. 
• A separate group for "None" (you can rename it to "No Issue"). 

To rename groups: 

• Click the cell with "Group1" → type Issues. 
• Click the cell with "None" → type No Issue. 

Step 5: Show Subtotals 

To show subtotals for each issue group: 

1. Right-click on any item in the Issue field. 
2. Choose Subtotal 'Issue'. 
3. Go to the Design tab → click Subtotals > Show all Subtotals at Bottom of Group. 

Step 6: Final Result 

Your Pivot Table should now look like this: 

Also, if you're looking for help with dropdowns, don’t forget to check the link I shared earlier in the thread it walks you through how to create and use dropdowns in Excel.

I hope the above information is clear. Thank you for your understanding and cooperation. 

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".   

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.