WhatsApp Chat Analysis in Excel, Step by Step
A WhatsApp chat analysis in Excel needs one thing first: the chat in a spreadsheet with one message per row. After that, three tools answer most questions: COUNTIF counts messages per person, a PivotTable summarizes them by sender and month, and a helper column for the hour shows the busiest times of day. WhatsApp itself only exports a text file (WhatsApp Help Center), so step 1 is converting it.
Step 1: Get the WhatsApp chat into Excel
WhatsApp’s Export chat gives you a ZIP with the conversation as text, not a spreadsheet. On iPhone: open the chat, tap the contact or group name, tap Export chat, choose a media option, then choose where to send it.
The quickest way from there to a clean sheet on iPhone is Exportly: share the export to Exportly, tap Export Chat, choose Excel and save the .xlsx. Every message becomes a row with five columns: Date, Time, Sender, Message and Attachment. Exporting needs Exportly Pro (weekly or yearly, price shown in the app before you buy); the chat is processed on your iPhone and never uploaded. Other ways to get there are in how to export a WhatsApp chat to Excel.
Exportly only builds the spreadsheet. The counting and charting below happen in Excel.
The sample chat used in this guide
The examples use a made-up sample, not a real conversation. Columns A to E match Exportly’s Excel export:
| A: Date | B: Time | C: Sender | D: Message | E: Attachment | |
|---|---|---|---|---|---|
| 2 | 2024-05-03 | 08:12 | Anna | Morning! Train is late again | |
| 3 | 2024-05-03 | 08:15 | Ben | Of course it is | |
| 4 | 2024-05-03 | 19:40 | Chris | Pizza on Friday? | |
| 5 | 2024-05-04 | 21:05 | Anna | IMG-0001.jpg | |
| 6 | 2024-06-11 | 21:30 | Ben | Look at this view | |
| 7 | 2024-06-11 | 21:31 | Anna | Wow | |
| 8 | 2024-06-12 | 07:55 | Chris | Pizza place closed, sorry |
Row 1 holds the headers. In Exportly’s export, the date (year-month-day) and the 24-hour time are stored as text, not as Excel date values. That’s why the formulas below use text functions, which work regardless of your computer’s date settings.
Step 2: Count messages per person with COUNTIF
COUNTIF counts the cells in a range that match a condition (Microsoft: COUNTIF function).
- In an empty column, say G, type each sender’s name once: Anna in G2, Ben in G3, Chris in G4.
- In H2, enter
=COUNTIF(C:C,G2)and fill it down to H4.
With the sample, Anna gets 3, Ben 2 and Chris 2. COUNTIF isn’t case sensitive. If a name doesn’t match when it looks identical, there may be an extra space; Microsoft suggests cleaning the data with TRIM or CLEAN.
COUNTIF also accepts wildcards, so =COUNTIF(D:D,"*pizza*") counts messages that mention pizza anywhere in the text (2 in the sample).
Step 3: Build a PivotTable of messages per person
A PivotTable does the same count without typing names, and updates as you change it (Microsoft: Create a PivotTable).
- Click any cell in the data.
- Select Insert > PivotTable, choose New Worksheet and select OK.
- In the PivotTable Fields pane, drag Sender to Rows.
- Drag Sender again to Values. Because it’s text, Excel shows it as a count.
Use Sender rather than Message in Values: a photo sent without a caption has an empty Message cell and wouldn’t be counted.
Step 4: See messages per month
To group by month, add a helper column that turns the text date into a month key.
- In F1 type
Month. In F2 enter=LEFT(A2,7)and fill it down. “2024-05-03” becomes “2024-05” (Microsoft: LEFT function). - Create a PivotTable as in step 3, with Month in Rows, Sender in Columns and Sender in Values.
The sample shows 4 messages in 2024-05 and 3 in 2024-06. Insert a chart from the PivotTable to see the trend over the year.
If your dates are real Excel dates (from another source), you can skip the helper column and group the date field in the PivotTable by Months instead (Microsoft: Group data in a PivotTable).
Step 5: Find the busiest hours
The hour of each message tells you when the chat is most active.
- In a new column, enter
=LEFT(B2,2)and fill it down. “21:05” becomes “21”. - Put that Hour column in Rows of a PivotTable and Sender in Values.
In the sample, hour 21 leads with 3 messages. For times stored as real Excel times, =HOUR(B2) returns the hour as a number from 0 to 23 (Microsoft: HOUR function).
More questions you can answer
- Who sends the most photos? In a PivotTable, put Sender in Rows and Attachment in Values: empty cells aren’t counted, so you get the number of attachments per person.
=COUNTIF(E:E,"?*")gives the total for the whole chat. - How did a group change over time? Month in Rows and Sender in Columns shows who became more or less active. For groups, see how to export a WhatsApp group chat.
- When did a topic come up? Filter the Message column for a word and read the Date column.
If you’d rather keep a readable copy of the conversation next to the numbers, export a PDF as well; see how to export a WhatsApp chat to PDF.
Questions people ask
How do I count WhatsApp messages per person in Excel?
Use =COUNTIF(SenderColumn,"Name"), or a PivotTable with Sender in Rows and Sender again in Values.
How do I get WhatsApp chat statistics by month?
Add a month column with =LEFT(A2,7) on a year-month-day date, then build a PivotTable with that column in Rows.
Can I do this on an iPhone or iPad?
Microsoft documents creating PivotTables in Excel on iPad (version 2.82.205.0 and later). Its PivotTable page doesn’t cover Excel on iPhone, so a larger screen is the safer choice.
Does Exportly analyze the chat for me?
No. Exportly creates the Excel file; the analysis is up to you in Excel.
Also available in: Deutsch · Español (México) · Français · Italiano · Português (Brasil) · Türkçe