Short answer: first check whether the site already gives you every row: a "Show entries" or rows per page menu, an export button, or all rows hidden in the page source, where IMPORTHTML or Excel's From Web reads them in one go. If the rows really load page by page, stack one IMPORTHTML per page when the page number is in the address, or use a browser add-on that clicks Next and joins the pages, such as List & Table Scraper.
Step 1: look for a shortcut before you scrape
Many paged tables can give you everything at once.
- Rows per page menu. Look above or below the table for "Show 10 entries", "Rows per page" or similar. Pick the largest number. Some tables offer "All".
- Export, Download or Print button. Some tables have one next to the search box. A print view often lists every row.
- A number in the address. If the address changes to
?page=2or&start=21when you click Next, try a size setting such asper_page=orlimit=if the address has one. Some sites accept a larger value. - The page source test. Go to the last page of the table and note one value. Go back to page 1, right click, choose View page source, and search for that value. If it is there inside the table's cells (
<td>tags), every row is already in the page and the table only hides them. Then=IMPORTHTML(url, "table", 1)in Google Sheets reads all of them at once (change the 1 if it picks the wrong table). If the value only appears inside a<script>block, the rows are data for a script, not a table, and IMPORTHTML will not read them: go to step 3.
If the value is not in the source, the site loads each page from its server when you click Next. Go to step 2 or step 3.
Step 2: the page number is in the address
When clicking Next changes the address in a predictable way, formulas can read each page.
Google Sheets
Put one IMPORTHTML per page inside curly brackets, separated by semicolons, and Sheets stacks them under each other. Paste it into one cell as one line:
={IMPORTHTML("https://example.com/list?page=1", "table", 1); IMPORTHTML("https://example.com/list?page=2", "table", 1); IMPORTHTML("https://example.com/list?page=3", "table", 1)}
- Each page brings its own header row. Remove the extra ones with a filter afterwards.
- All pages must return the same number of columns, or the formula shows an error.
- If your spreadsheet uses a comma as the decimal separator, put semicolons between the arguments too:
IMPORTHTML("..."; "table"; 1). - It still reads the page source, so a table built by scripts comes back empty. See IMPORTHTML not working.
Excel (Power Query)
- In Excel open Data, then Get Data, From Other Sources, Blank Query.
- In the Power Query Editor open Advanced Editor and replace the text with this, changing the address and the page range:
let
Pages = {1..10},
Tables = List.Transform(Pages, each Web.Page(Web.Contents("https://example.com/list?page=" & Text.From(_))){0}[Data]),
All = Table.Combine(Tables)
in
All
- Click Done, check the preview, then Close & Load.
{0} takes the first table on each page. If you get the wrong one, try {1} or {2}. Like IMPORTHTML, this downloads each address itself, so pages that need you to be signed in usually do not work.
Step 3: Next does not change the address
Many tables load the next rows with a script when you click Next, and the address stays the same. A formula cannot click a button, so the free options are:
- Set the rows per page menu to its largest value, then copy and paste each page into the sheet under the last one.
- Use the browser's Network tab in developer tools: click Next and look for the request that returns the rows (often JSON). Some sites expose clean data that way. It takes time and breaks when the site changes.
Let a browser add-on click Next for you
List & Table Scraper is an add-on we make for Firefox and Chrome. It reads the table as your browser shows it, so it also works when the table is built by scripts or you are signed in.
- Open page 1 of the table and click the add-on's toolbar button. It outlines the table it picked and shows a preview. Real tables use their header cells as column names. If it picked the wrong block, click Try another.
- Click Locate Next button, then click the table's own Next link or button.
- Set the Delay between pages (1 to 10 seconds, default 2) and Max pages. The free version reads up to 3 pages per run. With Pro Max pages goes up to 500.
- Click Crawl pages. When Next is a normal link, the next pages load in the background from the same site. When Next is a button that updates the table in place, the add-on clicks it and waits for the new rows.
- It stops when there is no Next, when a click brings no new rows, at the page limit, or when you click Stop. If a free run stops at 3 pages, the panel says so and you can still export what it collected.
- Download CSV, Excel (.xlsx) or JSON, or click Copy and paste into Google Sheets.
Every page uses the columns found on page 1. A header row made of header cells is not added again for each page, and duplicate rows are dropped.
Limits worth knowing
- When Next is a link, the add-on loads the next page's HTML. If that page builds its table with scripts, the add-on may not find it there, and the panel tells you when that happens.
- It stops if the Next link goes to another website or back to a page it already read.
- Free reads up to 3 pages per run. Pro (from USD 4 a month) reads up to 500 and saves a recipe per website. Export is free either way.
- Scraped data stays in your browser. The free version needs no account, and the add-on has no server.
- Use a delay of a few seconds and respect each website's terms of use.
For lists of cards rather than tables, see scraping a list from multiple pages.
Frequently asked questions
Why does IMPORTHTML only return the first page of the table?
IMPORTHTML reads the page source for one address. If the site sends only the first page and loads the rest when you click Next, the other rows are not in that source. Use the page source test in step 1: if a value from the last page is missing from the source, no IMPORTHTML index will find it.
Can Excel Power Query follow a Next button?
No. Power Query downloads addresses, it does not click. It can read many pages only when each page has its own address, as in step 2.
How do I get all entries when the "Show entries" menu stops at 100?
Set it to 100 and check the address after you click Next. If a page number or start number appears, use step 2. If not, copy each page or use an add-on that clicks Next, as in step 3.
How many pages can I scrape for free?
The formulas in step 2 cost nothing, but you need one formula per page, and a site may block many requests at once. List & Table Scraper reads up to 3 pages per run for free and up to 500 with Pro.
More guides
- How to export a Wikipedia table to Excel, Google Sheets or CSV (3 free ways)
- How to scrape a list from multiple pages into Excel or CSV without code
- How to scrape an infinite scroll page into Excel or CSV
- IMPORTHTML not working in Google Sheets? Why it fails and how to get the table anyway
- How to scrape product cards or search results (not a table) into Excel or Google Sheets