A
1
a
2
granulargroup.com/blog/free-spreadsheets-tool-topical-analysis
3
4
This is an intro sheet - refer to the "wordlist" sheet for the tool.

Note the values currently entered in the INPUT columns of the "wordlist sheet serve only as an example, use your own values in those columns.

IMPORTANT: This is not a data science tool, which means there are some limits like the fact that Sheets can't handle more than cca 600 lines of output per some formulas (depending on the length of each query). Future updates will most likely solve this more elegantly through AppScript. This also means there is some waiting time between data entry and calculation, depending on your computer capabilities :)
5
6
How to use this tool?

0. Make a copy of this document (File -> Make a copy...) in your drive so you can edit it

1. Go to the "wordlist" sheet and paste the list of queries in column A

2. If you have data on volume or clicks of those queries, paste those in column B

3. Columns E, F and G are used for output data where you can see the list of unique words along with their respective counts and volume / click contributions

4. If you want to exclude some words (like stop words) in the output, enter them in column C separated by commas

5. After the calculation is done, copy the output columns and paste them as values in a separate sheet or wherever you plan to do the analysis.

6. Optional but helpful: share your thoughts/comments on Twitter by tagging the creator of this sheet @tlukinic
7
8
FAQ

Q: How long does the data crunching usually take?
A: On a stronger CPU like i7 with 64-bit architecture the calculation usually takes up to about 3-4 minutes on a dataset of 10-15k rows and about 5-6 minutes closer to current max rows (25k).

Q: I've entered my data some time ago, why is it still calculating?
A: If stuff isn't working even after a couple of minutes, just try refreshing the entire document - often Sheets finishes with calculations and still show the "calculating" gauge until a reload. Also sometimes the gauge can freeze even after refresh, so just check the last rows of the OUTPUT columns, the data might already be populated completely.

Q: Why do I get a lot of #VALUE and #NA errors in the OUTPUT columns after entering my data?
A: Try doing it all over again but this time use "paste as values" when on the INPUT columns. You can also try selecting the keyword and volume/traffic columns in the INPUT section and formatting them as plain text and as numbers, respectively. After that, refresh/reload the document.