- 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:
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”:
Now you’re ready to summarize the data.
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.