On-screen ranking reports are convenient for a quick look, but when you want to ask deeper questions — which group improved most this month, which device is dragging the whole keyword set down, which keywords moved the most — a spreadsheet is where the real work happens. Moving ranking data into a spreadsheet lets you sort, filter, average and compare in ways no ready-made report will match exactly. This guide covers the general method: which columns useful data needs, how to clean it, and what to analyse.
What columns should a useful ranking export have?
Whatever tool your data comes from, a useful export needs at least these columns: date measured, keyword, position, device, country, and the landing URL currently ranking. The date lets you draw trends over time, while device and country let you split the numbers, since rankings on phones and computers, or in two different countries, can differ a great deal. The ranking landing URL is a column many people forget, yet it matters a lot: it shows which page Google is choosing for that keyword, and when it changes, that is a clue about how Google's understanding of your site is shifting.
Beyond the essential columns, add a topic-group column for each keyword, showing the intent group it belongs to. This column usually does not come with the raw export; you add it yourself by matching against your own grouping table. With the group column in place, you can average by group instead of staring at individual terms, and that is the view that leads to the fastest decisions.
Clean the data before analysing it
Raw data almost always needs cleaning first. Start with days that have no recorded position, meaning days when the keyword sat outside the Top 30 and no exact rank was recorded. Set a clear rule for these cells: leave them blank and exclude them from averages, or assign an agreed value such as 31 for calculation purposes. What matters is that the rule stays consistent across the whole table, otherwise your averages will be skewed and you will end up comparing things that cannot fairly be compared.
Next, standardise keyword names and landing URLs: trim extra spaces, unify capitalisation, and make sure the same keyword never appears under two different spellings. If your table combines data from more than one source, check for duplicate rows on the same date and keyword, because one duplicated row distorts every average and count. A few minutes of cleaning at the start will save you hours of confusion later, when the numbers do not add up and you cannot find the error.
Average by group and by device
The first analysis to run is an average position table by topic group and by device. In a spreadsheet, use conditional averaging functions to calculate each group's average position on phones and on computers separately. The result shows immediately which groups are strong, which are weak, and which have a large gap between devices. A group that averages well on desktop but poorly on mobile often points to a landing page that displays badly on phones, and fixing that is usually the first job.
You should also calculate the share of each group's keywords sitting in the Top 3, Top 10 and Top 30. Average position can be pulled off course by a few extreme keywords, while the Top 10 share shows the group's overall strength more evenly. When a group's Top 10 share rises steadily across months, you have clear evidence of genuine progress, not just one or two lucky keywords climbing.
Find the biggest movers
The second analysis is finding the keywords that moved the most over your chosen period. Calculate the difference between each keyword's position at the start and at the end of the period, then sort from the biggest gains to the biggest falls. This list becomes your action list: the biggest gains show which content is working, so you can learn from it, while the biggest falls show which pages need urgent inspection. Do not judge by the size of the move alone; combine it with the current position, because a term moving from 28 to 22 is still less encouraging than a term moving from 12 to 5.
Beyond start-to-end movement, watch for keywords that swing up and down with a large range throughout the whole period. These often belong to topics where Google is re-testing rankings, or where competition is intense. For this group, resist the urge to edit content after every swing; wait a few more weeks to see whether the range narrows before deciding to intervene.
Compare month by month and keep a change log
The third analysis is a monthly comparison: each group's average position this month against last month, and each group's Top 10 share across months. Monthly comparison frees you from daily noise and reveals the real trend. A group dipping slightly for one week may not matter, but three consecutive months of decline certainly needs action. To keep the comparison fair, use the same keyword set throughout the period; if you add new keywords halfway through, separate them out when averaging so you are not comparing unlike with unlike.
Finally, add a notes column for important changes: the date you edited a page's content, the date the site had a technical fault, or the date a major competitor published new content. When you line up the trend chart against this change log, the link between action and result becomes far clearer. Over time, your spreadsheet stops being just a store of numbers and becomes a learning journal: which changes worked, which did not, and what is worth repeating. If you want to start collecting steady daily data to feed into your spreadsheet, you can set up your tracking project at the sign-up page and build a day-by-day ranking history from there.
Conclusion
Analysing rankings in a spreadsheet needs no advanced technique, only enough columns, careful cleaning, and a few well-chosen summary calculations. Once you have group averages, Top 10 shares, a biggest-movers list and a monthly comparison, you will make decisions from evidence instead of gut feeling. If you would like to read more rank-tracking guides, you can browse the Keyword.help blog at the Keyword.help blog and start building your first analysis table for your own keyword set.