Summarize Data with Pivot Table

February 09, 2002 - by Juan Pablo Gonzalez

Paul asks:

I have a number of columns of text data in my Excel database which contain boys and girls names some of which are repeated many times, I have to return a summary of the columns to a different spreadsheet. The information that should appear on the summary sheet is the the name and the amount of times it appears in the data base columns. the name should only appear once in the summary sheet and along side it in another column is a count of the number of times it appears in the data base column. Can you help with the solution.

This is the PERFECT job for a VERY FAST AND EASY Pivot Table. Let's assume that your names are in Column A, with the heading in A1. Select this column (A1:A100 if you have 99 names), go to Data, Pivot Tables, make sure that the origin range is set up correctly (Should appear Sheet1!A1:A100 or similar), and in the table definition, drag the heading to the ROW fields, AND to the DATA fields. You should see something like:

The Pivot Table should only show unique names, and the count of the times each name appeared on the list.