Creating a Spreadsheet and Pivot Table for Defected Products

[u/LumpySmurf]

Hi guys, I'm looking for some advice on how to create this spreadsheet in the most professional way possible. I work for an electronics company and we receive defected products everyday that need to be sorted and repaired. I want to create a sheet that includes the date of arrival at the factory, which client, the issue, if it's still under warranty, etc. Afterwards I'd like to create a pivot table(i have very little experience) that can use this data to see trends in what issues are arising the most, which products are being returned most often, etc. Could anybody give me advice on where to start? Thanks

[u/Solid_Text_8891]

This seems pretty simple. I would create a table with the columns you need. Start by typing the name of the first column in A1, the second in A2 etc and fill in the data you need.

I would then make the day into a table by clicking insert-> table. If any of the columns are conditional or calculated add the formulas you need into the cell in the table.

Then click anywhere inside your table and click insert->pivot table. This will open the dialogue to the pivot table which you can create by dragging and stopping the fields into the boxes. A quick google search can help you with any details that may be tricky.

[u/Excelerator-Anteater]

It sounds like you have your table layout set out in a reasonable way.

When you're at your Pivot Table stage:

1. Add one of your columns (e.g. Issue) to the Rows field
2. Add your Quantity field to Values (defaults to Sum)
3. Add your Quantity field to Values again, which we will change to Count
   1. Right click on the Values column that just appeared
   2. Select Summarize Values By
   3. Select Count

You should now see the how many items came back, as well as the count of lines that came in by Issue. You can swap out (or create new Pivot Tables off the same data) with different values in the Rows field. Or you put multiple items in the Rows Field (e.g. Product and Issue) to see more detail.

Also, one thing many people forget about is the [Pivot Table] Design Menu, where you can drastically change the look of your Pivot Table. Try playing with the Subtotals, Grand Totals, Report Layout, and Blank Rows sub-menus to get a look that makes sense for your audience.