Pivot tables for comment exports: five pivots that answer most questions
A pivot table is the fastest way to turn a few thousand comment rows into an answer, and it needs no formulas. These five cover most of what people ask of a comment export. Each lists the fields to drag, in Excel and in Google Sheets, and the mistake that most often spoils it.
Updated Nov 19, 2026 · 7 min read
Before the first pivot
Work from the Comments sheet of the Excel workbook. Its columns, in order, are #, comment_id, username, user_id, comment, posted_at_utc, likes, is_reply, reply_to_comment_id, reply_to_username, mentions, hashtags and platform (A to M). In the workbook the IDs are text and posted_at_utc is a real date, which is what a pivot needs.
Excel: click any cell in the data, Insert, PivotTable, New Worksheet. Pressing Ctrl+T first turns the range into a table, so the pivot picks up rows you add later.
Google Sheets: click any cell in the data, Insert, Pivot table, New sheet. Uploading the workbook to Sheets with File, Import keeps all ten sheets; the Google Sheets page lists the other ways in.
Two of the pivots below are also delivered ready-made: the workbook's Commenters and Per day sheets. Build the pivot when you want to slice further, for example by network or by top-level versus reply.
Pivot 1: comments per person
| Area | Field |
|---|---|
| Rows | username |
| Columns | is_reply |
| Values | comment_id, summarised by Count (Excel) or COUNTA (Sheets) |
Sort by the grand total, largest first. The no column is top-level comments and the yes column is replies, so one glance separates people who comment a lot from people who mostly answer others.
The mistake: putting comment_id in Values and leaving Excel's default, Sum. IDs are text, so the sum is 0 on every row. Change Summarize Values By to Count.
What to read from it is covered in count comments per person, and the same table flags repeat entrants for finding duplicate commenters.
Pivot 2: activity per day, top-level against replies
| Area | Field |
|---|---|
| Rows | posted_at_utc, grouped by day |
| Columns | is_reply |
| Values | Count of comment_id |
Excel groups a date field by itself, often into months. Right-click a date in the pivot, Group, and select Days only (add Months for a long-running post). Google Sheets: right-click a date in the pivot, Create pivot date group, Day of the month or Year-Month-Day.
Add a line chart and you can see the first wave of comments and the later wave of replies. The days are UTC days. If your audience is far from UTC, add a local-time column first: convert UTC timestamps to your time zone.
The mistake: pivoting on a timestamp that arrived as text (from a CSV opened without date detection). Text cannot be grouped by day. Test one cell with =ISNUMBER(F2); if it says FALSE, make a date column with =DATEVALUE(LEFT(F2,10)) and pivot on that.
Pivot 3: who earns the likes
| Area | Field |
|---|---|
| Rows | username |
| Values | likes by Sum, likes again by Average, comment_id by Count |
| Filters | is_reply (optional) |
Sort by Sum of likes. The people at the top wrote the comments the rest of the audience agreed with, which is a different list from the people who commented most. Average of likes with a count of 1 is one lucky comment; a high average over five or more comments is someone whose opinion carries.
The mistake: a likes column that came in as text gives Count instead of Sum and an average of nothing. Check with =ISNUMBER(G2). In the workbook it is a number.
Pivot 4: who gets replied to
| Area | Field |
|---|---|
| Filters | is_reply, set to yes |
| Rows | reply_to_username |
| Values | Count of comment_id |
This counts replies received, not written. The top rows are the people whose comments started conversations. Add username under Rows, below reply_to_username, to see who answered each of them; if the account that posted is rarely among them, questions are going unanswered. The page on exporting comments with replies has a formula that lists the unanswered ones, and rebuild reply threads puts whole conversations back in order.
The mistake: forgetting the filter. Without it, every top-level comment lands in one row labelled (blank).
Pivot 5: an hour-by-weekday grid
This one needs two helper columns. Column M is platform, so use N and O. Both formulas work in Excel and Google Sheets when posted_at_utc is a real date:
N2: =HOUR(F2)
O2: =TEXT(F2, "ddd")
Name them hour and weekday, fill down, and refresh the pivot's range.
| Area | Field |
|---|---|
| Rows | hour |
| Columns | weekday |
| Values | Count of comment_id |
Select the numbers and apply a colour scale (Conditional Formatting, Color Scales in Excel; Format, Conditional formatting, Color scale in Sheets). The dark cells are when your audience comments. One post shows mostly the hours after it went up, so stack several exports before trusting the pattern: the regular commenters guide shows how to stack them.
The mistake: weekdays sorted alphabetically (Fri, Mon, Sat). In Excel, drag the column labels into order or use =WEEKDAY(F2, 2) (1 for Monday to 7 for Sunday) as the field instead. In Sheets, use the WEEKDAY number.
Fields that do not pivot well
mentionsandhashtagshold several values in one cell, separated by spaces. A pivot on them groups by the whole cell, so "@priya.plans @theo.bikes" and "@theo.bikes @priya.plans" are two rows. Use the workbook's Mentions and Hashtags sheets, which count each account and tag separately.commentas a row field gives one row per distinct comment, which is only useful for spotting copy-paste text. That use is covered in detecting bot and spam comments.user_idis the safer row field thanusernamewhen you combine exports made weeks apart, because people rename their accounts.
The same five without a pivot, in Google Sheets
QUERY builds a pivot-like table from a formula, which updates by itself when the data changes. Useful on a tab filled by a live =IMPORTDATA link.
=QUERY(Comments!A:M, "select C, count(B), sum(G) where B is not null group by C order by count(B) desc label count(B) 'comments', sum(G) 'likes'", 1)
=QUERY(Comments!A:M, "select J, count(B) where H = 'yes' group by J order by count(B) desc label count(B) 'replies received'", 1)
=QUERY(Comments!A:M, "select C, count(B) where B is not null group by C pivot H", 1)
The first is pivots 1 and 3 together, the second is pivot 4, and the third uses QUERY's own pivot clause to split each person's count into top-level and reply columns. In Excel 365 the function PIVOTBY does the same job where it is available; a normal PivotTable works in every version.
After a pivot changes its source data, remember to refresh it in Excel (right-click, Refresh). Google Sheets pivots refresh on their own.
FAQ
Do I need to build these if I have the workbook?
Not the basic ones. The Commenters, Mentions, Per day and Hashtags sheets already hold those counts. Build a pivot when you want to split by another field, such as replies against top-level comments or one network against another.
Why does my pivot show (blank) as the biggest row?
The row field is empty on most rows. reply_to_username is empty for every top-level comment, for example. Filter is_reply to yes, or untick (blank) in the row filter.
How do I count unique people in a pivot?
In Google Sheets, summarise user_id by COUNTUNIQUE. In Excel, tick Add this data to the Data Model when creating the pivot, then choose Distinct Count under Summarize Values By.
Can one pivot cover several posts?
Yes. Stack the exports in one sheet, add a column naming the post, and put that column under Columns or Filters. The platform column already separates networks.