Counting email address statistics in Excel spreadsheet?

[jent]

I have an Excel spreadsheet that is approximately 1,850 rows long. In it, my organization has a list of which email addresses opened our weekly newsletter. Every week we pull up our statistics and copy and paste the list of email addresses that have opened that week's newsletter into the Excel document, meaning I have a few months' worth of email addresses, many of which repeat often or fairly frequently in the "Email address" column. What I'd like to do is somehow get a list the email addresses sorted by frequency, so I can see how many times each unique email address has opened the newsletter over the last few months. For example, if billybob@macrumors.com opened the newsletter every week for two months, I would like a list with his email address in one column and the number "8" in another column, signifying the number of opens he's responsible for. Because of the high number of unique email addresses, I can't just do a search for every email address and manually make a note of it. Thanks for any suggestions!

 

[Darth.Titan]

Excel is a spreadsheet program, and is ill-equipped for the type of function you're looking for.

What you need to do is dump the spreadsheet into a database table that you can run queries on.

 

[James_C]

Actually this is very easy to do in Excel using Pivot Tables. I am using Excel for Mac 2011.

First step is to get your data into one list of email addresses. Make sure the data has a column header above column containing the email addresses. In the example below I have named it e-mail address list. Then select the header and all the data below it as in the screenshot below. An easy way to do this, assuming you have no empty cells is to select the first cell, then press shift + ⌘ and the down arrow on the keyboard.

Then from the data menu select PivotTable.



Next confirm the selected range and press 'OK'



In the pivot table builder, drag the header that you gave for your column of data into both the Row Labels and Value fields as shown below. You will now have a summary table with a count of how many times the email address appears in the list.



Hope this helps

 

[jent]

Thank you James! I will mention for others looking to do the same thing that in Excel 2007 for Windows, PivotTables are under Insert.

A follow-up question I have is how can I sort by number? Now that I have the number of matches counted, I'd like to sort from highest to lowest, but the PivotTable doesn't seem to want to sort.

Also, is there a way to keep some additional columns so that I can retain the columns I have for first and last name? Just wondering!

 

[James_C]

Thank you James! I will mention for others looking to do the same thing that in Excel 2007 for Windows, PivotTables are under Insert.

A follow-up question I have is how can I sort by number? Now that I have the number of matches counted, I'd like to sort from highest to lowest, but the PivotTable doesn't seem to want to sort.

Also, is there a way to keep some additional columns so that I can retain the columns I have for first and last name? Just wondering!

Yes you can sort by the value field. Just select the first data element in the value field of the pivot table (the first one under 'Total') and from the menu choose Data > Sort.



Then choose if you want ascending or descending



You can add additional Columns for first and last name, in the example I have just added first name, but principle is the same for additional columns.

First select all the data, including the columns for additional row names that you want in your pivot table

The create the pivot table as before, but you will need to add the additional Columns for first name and last name to the Row Labels box. Make sure that you have the unique sort first in the list (the e-mail address), otherwise it will sort by first name/last name instead.



Lastly you will need to turn off subtotals for e-mail address and first name, so it keeps all the items on one row. To do this select the First Row header for e-mail address and right mouse click and choose 'Field Settings' then select none for subtotals. Do the same for the First Name field as well.



For anyone using Excel 2011 you will also need to make sure you have Tabular Layout selected in the Pivot Table > Design > Layout Toolbar. I don't think you need to do this with previous versions of Excel for Mac, as Tabular Layout is the only option from memory.

 

[jent]

Fantastic—thank you so much!

 

[jent]

If the original data in the original Excel worksheet (tab) changes, how can I get the PivotTable to update as well?

 

[James_C]

Hi Jent,

Ok this is pretty easy to do. We need to do two things.

1. Firstly we need to make sure the Pivot Table is pointing towards the table with the additional data (new rows) added.

2. Secondly we need to refresh the Pivot Table to take in the new data.

When we built the first pivot table it is likely that the Pivot Table was linked to your data by a fixed range. In my original example this was Sheet1!$A$2:$B$12. If you are going to reuse the same pivot table, but add new data then the best way is to give your data a named range.

To create a named range is very easy. Select your entire table that you use in the pivot table plus one blank row at the bottom, and click in the Name box, to the left of the formula bar and enter a suitable name, "Pivot_Range" and press return.



The reason that we included a blank cell at the bottom is if you insert new data between the last row of the table and the blank row, Excel will automatically adjust the range of the defined name. This does mean you will need to insert new rows every time you add data, rather than copy and paste. However if you make a mistake you can manually change the range of a defined name, by using Insert > Name > Define from the main menu, select the name you want to amend and change the cell references.

Now we need to amend the Pivot table to use the Named Range, rather than the fixed reference. In Excel 2011, click the Pivot Table, select Pivot Table menu bar and click the change source button.



Now enter the name you defined for your pivot table in the dialog box.



I think you might have a previous version of Excel and from memory I think you need to click on the pivot table and go back in the pivot table wizard and then step back to you get to the part that you define the range of the pivot table, and enter the defined name, but I know there is a way to do it.

To refresh the data, right mouse click on the pivot table and you will see an option to refresh the pivot table.



In future copy and insert the rows with the new data into the source table between the last row and the blank row that you defined in the named range, then refresh the pivot table.

Hope this helps

James

 

[Padaung]

Thank you

Hi James,

I'm relatively new to pivot tables, and just found your posts incredibly useful.

Thank you very much for taking the time to write such detailed replies to the OP - they have been of benefit to me as well.

Charlie

 

[James_C]

Hi James,

I'm relatively new to pivot tables, and just found your posts incredibly useful.

Thank you very much for taking the time to write such detailed replies to the OP - they have been of benefit to me as well.

Charlie

Hi Charlie,

Thanks, I am glad you found it useful, it's good to know that others found it helpful as well.

James

 

[Roquefort]

When I create a special blank row at the bottom of a range, for the purpose of inserting new data above it, I do two extra steps to leave myself a reminder:

1. Color the extra row: Select the row (or just the columns of interest in that row) and choose Format -> Cells -> Patterns and pick any color you like. I usually pick light gray.
   
   
2. Put reminder text in that row (or the row below it), e.g.,
   
   Insert new data above the gray row.​
   I put this text in the leftmost cell and set that cell to Alignment "General" or "Left".

That way I never forget to insert new data above the special row.