The Scenario
You are a college football historian. You are writing a longform piece on top-10 consistency — which programs appeared in the AP Top 25's top 10 most often across the decade from 2015 to 2024. You need weekly AP poll data for ten full seasons in a single Excel workbook before you can run a single COUNTIF.
You have no existing script. The CollegeFootballData.com API returns one week at a time. Ten seasons, roughly fifteen weeks per season, is about 150 API calls.
The bad version:
- You write a Power Query M script to loop through 150 API calls
- You realize the response format changed slightly in 2019 and your query breaks on those years
- You spend four hours debugging the merge
- You paste the combined result into Excel and the week numbering is inconsistent across seasons
- You give up and pull only five years, which weakens the piece.
The fast version is one prompt.
The Easy Way: One Prompt in SheetXAI
SheetXAI handles the looping, format normalization, and write-back inside the workbook.
Open the SheetXAI sidebar and type:
Pull the AP Top 25 poll rankings for every week of the 2024 regular season and postseason and paste all rows into my "Poll History" tab with columns for week, rank, team, conference, and first-place votes.
Once that tab is ready, extend it:
Fetch AP poll rankings for every week of the 2015 through 2023 seasons and append them to the "Poll History" tab, keeping the same column structure. Do not overwrite the 2024 data already there.
SheetXAI handles the multi-season loop and appends without disturbing existing rows.
What You Get
A single "Poll History" tab with ten seasons of weekly AP poll data:
- Week — regular season weeks plus bowls and playoffs
- Rank — final ranking for each team each week
- Team — program name, consistent across seasons
- Conference — affiliation at time of poll
- First-place votes — for analysis of dominance beyond the ranking
The data is COUNTIF-ready. Add a formula column to flag top-10 appearances, pivot by team, and your decade-level consistency chart is done in an afternoon.
What If the Data Is Not Quite Ready
Multi-season poll data has structural quirks. SheetXAI handles them without requiring you to touch the API again.
When week numbering is inconsistent across seasons
Some seasons label the championship game as "Week 15," others as "Championship." The inconsistency breaks your week-by-week timeline chart.
Look at the week column in the "Poll History" tab. Normalize the bowl and playoff week labels so all bowl games use "Bowl" and all national championship games use "Championship," regardless of season. Leave regular season week numbers as-is.
When you want SP+ ratings alongside the poll history
For the piece, you want to compare poll rank to SP+ rating by season to check whether the polls tracked real team quality.
Fetch SP+ ratings for all teams for seasons 2018 through 2024 and add them to my "SP+ Trends" tab with columns for year, team, overall rating, offensive rank, and defensive rank.
When you want a summary of top-10 appearances by program
The raw week-by-week data is too granular for the article. You need a program-level summary.
In a new tab called "Top 10 Consistency," count the number of weeks each program appeared in the AP Top 10 across the 2015–2024 seasons using the "Poll History" data. Sort by total top-10 weeks descending. Also calculate the number of distinct seasons each program appeared in the top 10 at least once.
When you want to cross-reference poll rank with season outcomes
The real analytical question is whether high rankings held up through the postseason.
In the "Poll History" tab, identify every team that was ranked in the AP Top 5 at the start of bowl season (week 14 or later in each year) for 2015 through 2024. For each such team, note their final-week ranking and whether they appeared in a New Year's Six bowl or the CFP. Write the results into a new tab called "Preseason vs Postseason."
The pattern: ten seasons of data is one prompt. The analysis is the next prompt. You do not have to separate them.
Try It
Get the 7-day free trial of SheetXAI and ask it to pull AP poll rankings for any season range into a workbook. The CollegeFootballData.com integration is included in every SheetXAI plan. For more, see how to pull Elo and FPI ratings for dynasty analysis or the CollegeFootballData.com in Excel overview.