The power of pivot tables for your complaints analysis…

Pivot tables in excel are a really simple but effective way of displaying your complaints information, so you can quickly assess trends and produce quick charts and tables for reports.

For pivot tables to work properly you need to create an excel worksheet, where you enter all your complaints information in a standardized way, each time complaints are received – creating a list of details.

If your complaints are provided to you by a customer service team, it’s a good idea to set out your list in the same way in which the information is provided to you, so you can just copy and paste it in.

If you’re not used to using pivot tables it can be quite a daunting thought.  The best way to get the most out of pivot tables is not be scared of it and just have a play with the information to see what it shows you – once you get used to them, it’s easy!  If you’re scared of messing up your complaints data, create a copy of your file and then you can play with it, without being scared of losing any of your data.

If you want to save your pivot playtime for later then you can access my PDF instructions here, just click the link below. Don’t forget to come back and tell me what you thought!

Access my pivot playtime pdf instructions now!

An example is shown below:

complaints analysis

Once you have your standardized table that you’re going to use, you can then go ahead and create a pivot table and chart.

To do this select all the cells in your table.  Make sure you select extra rows below that do not currently contain any information – this way when you add more data in, the pivot will know to include it and you can ask it to do a quick refresh to pull the new information in. This is called selecting a range of cells.

An example is shown below:

Once you’ve got your area highlighted, go to ‘Insert’, then ‘Pivot Chart’ and choose ‘PivotChart & PivotTable’.

You may find that if you have an older version of excel than I do, that this function is in the drop down lists at the top, rather than in the header images.

An example is shown below:

complaints analysis

Now you get a pop up box called ‘CreatePivotTable’.

It will ask you if you would like your pivot table and pivot chart on the same worksheet as your data, or on a new worksheet.  I like to have them on a different sheet in my workbook (on a separate tab), as I find it easier to work with – but you can choose. Now press OK. You should now have a screen that looks something like this…

An example is shown below:

complaints analysis

Notice, all your table headings are now listed in the PivotChart Fields box to the right-hand side of your screen as shown above.  You can now use these to build your table and your chart.

To build your table and your chart, you can drag your table headings into one of the 4 squares below the box called – Filters, Legend, Axis and Values.  Each time you drag a table heading into one these squares it will display them in the chart as follows:

Filters – places them at the top of the chart, so you can then filter the information shown in the chart using this heading.
Legend – this shows the data by colour coding it to each of the entries in that heading.
Axis – any headings you put in here will show on the bottom of your chart (horizontally).
Values – this is where you tell the pivot to count the number of entries.  It’s generally best to ask it to count the number of complaints here, so I would put in complaint type.

You can put more than one heading in the axis square, so you can see the data split by each heading.

An example is shown below:

complaints analysis

Now for the fun part…I’ve added all the headings I need.  I can now ‘play’ with the information.

You can filter the information in the chart wherever you see a little black drop down arrow, like on the ‘Customer’ heading.  By clicking this you can pick to see ‘All’ or a particular customer.

An example is shown below:

complaints analysis

You can filter the headings under the chart too.  Just remember if you click a specific customer for example, you’ll need to go back and click (All) when you’re done, otherwise it will only ever show you information just for that customer.

If you want to see the information displayed differently, you can now move the headings around in the squares.  For example, I can move the ‘Customers’ heading from the filter area to the bottom of the table, so it will show the complaints by customer.

An example is shown below:

complaints analysis

Now you can see each of the customers in the table.

complaints analysis

You can also change the order in which the information is displayed.

Rather than showing the complaints by customer at the top, we can order it by line first.  To do this drag the customer heading to the top of the list in the Axis square, so that the production line is at the bottom.

An example is shown below:

complaints analysis

When you have your pivot table set up the way you want it, you can then save the document.

When you want to add more complaints information (as new complaints are received), you can add this into your original table.  Once you’re done though, you need to tell the pivot table and chart that there is new information that is needed to be pulled in.  To do this just right click on the table and then choose ‘Refresh’. It will then pull in the new information for you.

An example is shown below:

complaints analysis

Have you lost your PivotTable fields boxes on the right-hand side of your screen?

No need to worry, this is because your cursor is not on the pivot table or pivot chart. Just click onto it and it should appear…

I love pivot tables and I find them a really useful tool in excel for analysis. This is a different type of newsletter to normal so I’d love to hear what you think to the change this week.  Did you find it easy to follow and helpful? Please let me know using our comments box below.
Thanks, Kassy

 

Have your say…

Share your thoughts…

Your email address will not be published. Required fields are marked *

We've tagged this article as:

Extra Helpings

If you've enjoyed this post why not try these related articles…

header angle

Thousands of Food Techies all over the world read and trust our blog.

Join them and get our bi-weekly articles direct to your inbox - for free!