Excel: Use Pivot tables to summarize daily data by months, quarters, and years

Excel mac 2008 icon
Image via Wikipedia

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:

Date|Item|Amount
08/01/2009|Widget1|9.23
08/13/2009|Widget1|9.23
08/27/2009|Widget3|19.27
09/01/2009|Widget1|9.23
09/20/2009|Widget5|17.22

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.

Add an extra column called “Counter”:

Date|Counter|Item|Amount
08/01/2009|1|Widget1|9.23
08/13/2009|1|Widget1|9.23
08/27/2009|1|Widget3|19.27
09/01/2009|1|Widget1|9.23
09/20/2009|1|Widget5|17.22

Now you’re ready to summarize the data.

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

  1. Drag “Date” into the Rows area
  2. Drag “Counter” into the “Data” area and verify that “Sum of Counter” is displayed.
  3. 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.
  4. Hit Finish and now you can see how many widgets were purchased in August and how many were purchased in September.
  5. 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.

Reblog this post [with Zemanta]
Advertisements

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

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s