Have a Seat at the Pivot Table

I have a confession to make – I LOVE EXCEL. Ok, maybe “love” is too strong of a word, but I find ways that Excel makes my life easier on a daily basis. And you can, too. In the latest post of our Excel Series for PR pros, we’ll walk through setting up a pivot table, which come in handy when you’re working with big spreadsheets. Pivot Tables allow you to summarize data from your spreadsheet quickly and easily, cutting down on reporting time and minimizing the potential for human error.

First – the basics – what is a pivot table? According to our friends at Wikipedia, a pivot table is a tool that can “automatically sort, count, total or average data stored in one table or spreadsheet, displaying the results in a second table.” With a few clicks of your mouse, you can tabulate and sort through pages and pages of results; let Excel do the dirty work!

The Data

Let’s say you’ve been tasked with analyzing your client’s or brand’s social media activity to pinpoint trends around posting times and engagement. Everyone wants to know the magic “best time to tweet” – but as data-driven PR professionals, we know there’s no one-size-fits-all answer. With the help of pivot tables, you can find the sweet spot for your handle.

For this example, I’m looking at Ellen DeGeneres’ Facebook page data year-to-date.

1. Insert – PivotTable

Make sure your mouse has selected cell A1. Next, you’ll select ‘PivotTable’ from the Insert menu. By default, the pop-up PivotTable builder will put your table into a new tab in your spreadsheet. Select OK.

2. PivotTable Builder

By default, you’ll be taken to the new tab and you’ll see the PivotTable Builder. The builder works simply as drag and drop. You’ll see your column headings in the ‘FIELD NAME’ box and can drag and drop them into the different areas of the table:

• Filters: Filters limit the data included in the table. In this example, we could use ‘Created Date’ as a filter if we only wanted to include data from January and our spreadsheet includes data from January through April.
• Columns: The column field should be the data you’re after – this will (surprise!) serve as the column of the new PivotTable.
• Rows: Your rows should be the metric(s) you want the PivotTable to calculate.
• Values: These are your calculations. You can change how you’re calculating your value by selecting the icon shown below with the red arrow.

For my example, I’ve chosen to summarize Ellen’s Facebook posts by looking at total engagements per day. For accounts where you’re only publishing one post per day, this example wouldn’t apply because your original data set would provide that information. But for brands where you’re publishing several posts per day, the PivotTable will now easily show you which days generate the most engagements.

3. Visualization

For some projects, the raw data from the PivotTable will be all you need. For others, you’ll want to visualize the data in some way to gather insights. For this example, let’s take a look at the data in a line chart.

In the above chart, we can now clearly see the biggest spikes for Ellen’s Facebook activity, rather that sorting through rows and rows of posts and manually calculating (or worse – just eyeballing!).

This is just one of MANY ways you can use PivotTables to make your reporting efforts more accurate and streamlined. Give it a try with your data, and take a seat at the data-driven (pivot) table.

Tori Sabourin
Senior Marketing Analyst

[cta]