Recently, I needed to analyze a log file that had lines of data, where each line had a date stamp and some other information. Each line of the log file represented a single email message. The goal was to determine how many messages were sent out each month and each year. I used Excel’s pivot table feature to solve this problem.

Here’s a short screen capture that demonstrates the steps required.

In the video, I’m trying summarize email messages and so I create something I call the “Base Subject”. It’s used as a counter so that I can get the count of messages that get sent during a particular period. I should probably clarify what I’m doing so that you can apply the example to other kinds of files.

Imagine that you have a file that looks something like the following. Assume that the vertical bar separates each column:

Suppose that you would like to know the number of widgets purchased each month. In order to do that you need to count each widget that you buy. The easiest way to do that is to create a column that is identical for every widget. This may seem counter-intuitive but it’s what you need to do.

When you create the pivot table, do the following in the wizard:

Drag “Date” into the Rows area

Drag “Counter” into the “Data” area and verify that “Sum of Counter” is displayed.

Hit Finish to display the pivot table by days. To consolidate the information, right click on one of the days and add in years and months, by choosing years and months under the “Group” command, as described in the video.

Hit Finish and now you can see how many widgets were purchased in August and how many were purchased in September.

To determine how much you spent on widgets during a given month, try dragging the “Amount” column into the “Column” area in the pivot table wizard. This will give you totals for each period.

This only just scratches the surface of the interesting things you can do with pivot tables. Is this helpful? Do you have pivot table tips to share? Other questions? Let us know in the comments.

Advertisements

Like this:

LikeLoading...

Related

7 thoughts on “Excel: Use Pivot tables to summarize daily data by months, quarters, and years”

By the way, you can also use the “Text to Columns” wizard in Excel 2007 to split data (like the data I was using above) into columns. This is an easy way to separate first names and last names, or any other kind of delimited data into columns in Excel. See more detail here:

Nice example of using a pivot table for daily tasks. I especially liked your (unintentional?) pun, “counter-intuitive”, when you described the Counter column. 😉

Debra (commenter above) is a published author on pivot tables in Excel. Take a look at her books on Amazon – just do a search for “Debra Dalgleish” and you’ll find three books of pivot table recipes.

Ben,

Thanks for posting this – it’s very helpful!

Very easy and informative-Thanks.

Tim – Thanks for stopping by!

By the way, you can also use the “Text to Columns” wizard in Excel 2007 to split data (like the data I was using above) into columns. This is an easy way to separate first names and last names, or any other kind of delimited data into columns in Excel. See more detail here:

http://office.microsoft.com/en-us/excel-help/split-names-by-using-the-convert-text-to-columns-wizard-HA010102340.aspx

Nice example of using a pivot table for daily tasks. I especially liked your (unintentional?) pun, “counter-intuitive”, when you described the Counter column. 😉

Folks,

Debra (commenter above) is a published author on pivot tables in Excel. Take a look at her books on Amazon – just do a search for “Debra Dalgleish” and you’ll find three books of pivot table recipes.

Ben

Thanks Ben! I hope you convince a few more people of the value in using pivot tables.