Rebuild reply threads in a spreadsheet
An export lists comments in the order they were read, which scatters a conversation across the file. Each reply carries the ID of the comment it sits under, so four helper columns are enough to put every thread back together and measure it.
Updated Nov 19, 2026 · 7 min read
How a reply is stored
| B comment_id | C username | E comment | H is_reply | I reply_to_comment_id | J reply_to_username |
|---|---|---|---|---|---|
| …8901 | priya.plans | Does this come in green? | no | ||
| …9950 | theo.bikes | Lovely colours | no | ||
| …9977 | fernhill.market | @priya.plans yes, from Friday | yes | …8901 | priya.plans |
| …9981 | priya.plans | @fernhill.market perfect, thanks | yes | …8901 | priya.plans |
Threads in the file are one level deep. A reply points at the top-level comment of its thread, also when it answers another reply. In the last row priya.plans is answering fernhill.market, and the row still points at the first comment. Who a reply is aimed at inside the thread is usually visible from the @mention it starts with.
This guide relies on IDs matching exactly. Use the Excel workbook or Copy for Sheets, which keep IDs as text. A CSV opened by double-click rounds Instagram and TikTok IDs and replies then attach to the wrong comments: see why Excel rounds long comment IDs.
Step 1: a thread key
Column M holds platform, so the helper columns start at N. In N1 type thread, and in N2, filled down:
=IF(H2="yes", I2, B2)
Every row of a conversation now carries the same value: the ID of the comment that started it. The formula is the same in Excel and Google Sheets.
Step 2: when each thread started
Sorting by the thread key alone groups conversations, but in ID order, which is not always time order. Add the start time of each thread. In O1 type thread_start, and in O2:
=IF(H2="no", F2, IFERROR(INDEX(F:F, MATCH(I2, B:B, 0)), F2))
For a top-level comment it is the comment's own time. For a reply, MATCH finds the row of its parent and INDEX returns that row's time. If the parent is not in the file, the reply falls back to its own time so it still sorts somewhere sensible. Format column O like column F.
Step 3: sort into reading order
By menu. Select the data, then Data, Sort (Excel) or Data, Sort range, Advanced range sorting options (Sheets), with four levels: thread_start oldest first, thread A to Z, is_reply A to Z, posted_at_utc oldest first. "no" sorts before "yes", which puts each top-level comment above its replies.
By formula, on a new tab, leaving the original untouched. Google Sheets:
=SORT(Comments!A2:O, 15, TRUE, 14, TRUE, 8, TRUE, 6, TRUE)
Excel (Microsoft 365):
=SORTBY(Comments!A2:O5000, Comments!O2:O5000, 1, Comments!N2:N5000, 1, Comments!H2:H5000, 1, Comments!F2:F5000, 1)
To make the result read like a conversation, add a display column next to it:
=IF(H2="yes", " ", "") & C2 & ": " & E2
Replies are indented under their comment, and the whole section can be read top to bottom or pasted into a document.
Step 4: measure each thread
These go on the top-level rows of the Comments sheet and stay blank on replies.
Replies in the thread, column P, replies:
=IF(H2="no", SUMPRODUCT(--(I$2:I$5000=B2)), "")
SUMPRODUCT compares the IDs as text, character for character, in Excel and in Google Sheets. COUNTIF(I:I, B2) is shorter and faster, and is fine on YouTube exports, whose IDs contain letters. On Instagram and TikTok, Excel's COUNTIF treats long digit-only text as numbers and can count a neighbour's replies as well.
Different people in the thread, column Q, people. Google Sheets:
=IF(H2="no", COUNTUNIQUE(FILTER(C$2:C$5000, N$2:N$5000=B2)), "")
Excel (Microsoft 365):
=IF(H2="no", ROWS(UNIQUE(FILTER(C$2:C$5000, N$2:N$5000=B2))), "")
A thread with 12 replies and 2 people is an argument. One with 12 replies and 11 people is a topic.
Minutes until the first reply, column R, first_reply_min, in both apps:
=IF(AND(H2="no", P2>0), (MIN(FILTER(F$2:F$5000, I$2:I$5000=B2)) - F2) * 1440, "")
A day has 1,440 minutes and dates are stored in days, hence the multiplication. This needs posted_at_utc as a real date, which it is in the workbook.
Did the account that posted reply? Column S, owner_replied, with your handle:
=IF(H2="no", SUMPRODUCT((I$2:I$5000=B2) * (C$2:C$5000="fernhill.market"))>0, "")
The longest conversations
Google Sheets:
=ARRAY_CONSTRAIN(SORT(FILTER({Comments!C2:C, Comments!E2:E, Comments!P2:P, Comments!Q2:Q}, Comments!H2:H="no"), 3, FALSE), 10, 4)
Excel (Microsoft 365):
=TAKE(SORT(FILTER(CHOOSECOLS(Comments!A2:Q5000, 3, 5, 16, 17), Comments!H2:H5000="no"), 3, -1), 10)
Ten threads: who started each, what they wrote, how many replies and how many people. In any version, sorting the sheet by column P, largest first, gives the same list.
What usually tops it: a question many people share, a complaint, a joke, or a comment the creator answered. The first two are worth acting on. The page on exporting comments with replies has the formula that lists questions with no reply from you, and find questions and buying intent ranks them.
Replies whose parent is missing
Sometimes a reply points at a comment that is not in the file. In a spare column:
=AND(H2="yes", ISNA(MATCH(I2, B:B, 0)))
Count the TRUE values with =COUNTIF(T:T, TRUE). A handful is normal. The causes:
- The parent is hidden. The network filtered or hid the top-level comment and still shows its replies. Nothing can read the parent.
- You filtered before downloading. A file made with the Replies filter or a search holds replies without their parents. Download the unfiltered export for thread work.
- The IDs were rounded. If most replies show as orphans, the file was opened in a way that turned IDs into numbers. Start again from the workbook.
The opposite gap also exists. On a free TikTok or Facebook export, reply threads are read up to a limit, so some top-level comments show 0 replies in the file while the app shows several. The export page and the workbook's Read me sheet state how many replies were left unread. Instagram and YouTube exports read every reply. Network by network, the details are in the guides for TikTok, YouTube and Facebook Page posts.
Who answered whom inside a thread
reply_to_username names the author of the top-level comment. To see who a reply is really aimed at, read the mention it opens with. Google Sheets:
=IF(H2="yes", IFERROR(REGEXEXTRACT(E2&"", "^@([A-Za-z0-9._]+)"), J2), "")
Excel, any version:
=IF(H2="yes", IF(LEFT(E2, 1)="@", MID(E2, 2, FIND(" ", E2&" ")-2), J2), "")
Both return the handle after a leading @, and fall back to the thread starter when the reply does not open with a mention. A pivot table with this column in Rows and username in Columns shows who talks to whom: the layout is pivot 4 in pivot tables for comment exports. On Facebook, tags are names instead of @handles, so this column falls back to the thread starter almost every time.
FAQ
Are replies to replies nested in the file?
No. Threads are one level deep: every reply points at the top-level comment of its thread. A reply to another reply usually starts with an @mention of the person answered.
Why do my replies attach to the wrong comments?
The IDs were rounded when the file was opened, so different comments now share an ID. Use the Excel workbook or Copy for Sheets, which keep IDs as text.
Can I rebuild threads from a filtered download?
Only partly. A filtered file leaves out rows, so some replies have no parent in it. Download the export without filters for thread work.
Why does a comment show replies in the app and none in the file?
On a free TikTok or Facebook export, reply threads are read up to a limit. The export page and the Read me sheet say how many replies were not read. Instagram and YouTube exports include every reply.