Quillfold

Get a Wikipedia table into Excel or CSV: footnotes, merged headers and sortable tables

Published by Quillfold, which makes TablePick.

Wikipedia has tables for almost everything: populations, election results, record lists, award winners. Getting one into a spreadsheet usually works, but three things come up again and again:

  • Footnote marks like [1] or [a] end up glued to names and numbers, so China[a] doesn’t match China and a number with [3] after it is text.
  • Two-row headers (“City proper” over “Population, Area, Density”) arrive as two rows, or out of line with the columns.
  • Hidden sort keys: some cells carry invisible text that only exists so the table can be sorted.

Below: how the tables are built, what each method does with them, and how to clean up by hand.

First, look at how the table is built

These observations come from the HTML of three pages, fetched on 16 October 2026: List of countries and dependencies by population (United Nations) and List of largest cities on the English Wikipedia, and 都道府県の面積一覧 (prefectures by area) on the Japanese Wikipedia.

  • Data tables have the class wikitable. Tables you can sort by clicking a header also have sortable. Wikipedia’s other boxes (navigation boxes, notices) are tables too, so a page usually has more <table> elements than you see as data.
  • Footnotes are <sup class="mw-ref reference"> inside the cell. The text is a number or a letter in brackets: the population table has [1] in two header cells and [a] to [w] after country names. The Japanese pages use labels such as [注 1].
  • Merged headers use rowspan and colspan. List of largest cities has “City”, “Country” and “UN 2025 population estimates” spanning two header rows, and “City proper” (4 columns), “Urban area” (3) and “Metropolitan area” (3) over a second header row. The population table has a single header row and no merged cells at all, so not every Wikipedia table has this problem.
  • Hidden sort keys. The “Change (%)” cells of the population table each contain a display:none element with a value like 6999880000000000000♠ (238 of them). In 都道府県の面積一覧, each prefecture name is preceded by a hidden 01, 02… so the table can return to its original order. You don’t see these on screen, but they are in the HTML.
  • Minus signs. Negative changes are written with the Unicode minus sign − (U+2212), not the hyphen-minus -. The population table has 47 of them, such as −0.18%. Spreadsheets may treat these cells as text.
  • Sorting moves the rows. On the population page, clicking a column header re-ordered the rows in the page itself (checked in Chrome on 16 October 2026). Tools that read the open page see the order on screen; tools that fetch the address get the original order.

Google Sheets: IMPORTHTML

The simplest method, if you use Google Sheets:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_largest_cities", "table", 2)

The last number is the position of the table among the <table> elements “as defined in the HTML source” (Google’s help page). Because notices and navigation boxes count, it is often not the number you’d guess from the screen. In the HTML fetched on 16 October 2026, the population table was the 1st <table> and the List of largest cities table the 2nd. How to count, and the errors Google documents: IMPORTHTML returns an error or the wrong table.

Things to expect:

  • Google’s help doesn’t say what IMPORTHTML does with footnote marks, merged cells or hidden elements. Look at the result for [1]-style marks, a second header row, and values like 7000…♠ or 02 that weren’t on the screen.
  • The formula refreshes while the sheet is open. If someone edits the article, your numbers can change. If you need a fixed copy, copy the cells and paste them back as values only.

Excel: From Web (Windows)

Excel for Windows can load a page with Data > From Web and list its tables. The steps, the versions that have it, and what Microsoft documents are in Excel “From Web” can’t see your table. Microsoft’s documentation doesn’t describe how footnote marks, merged headers or hidden elements are handled, so compare the preview with the page before loading it. If merged headers leave blank cells, Power Query’s Fill down and Use first row as headers are described in Merged cells and “Column 1” headers.

Copy and paste, then clean up

Select the table from the first header cell to the last cell, copy, and paste into the spreadsheet. Then:

Remove footnote marks.

  • Excel: press Ctrl+H (Find and Replace). In “Find what” type [*] and leave “Replace with” empty. Microsoft documents * as “any number of characters”, so this removes a bracket, anything inside it and the closing bracket. If a cell has text between two marks (A[1] and B[2]), that text can go too; check those cells.
  • Google Sheets: Edit > Find and replace, tick Search using regular expressions, find \[[^\]]*\], replace with nothing. Google Sheets uses RE2 syntax. This removes everything in square brackets, including brackets that are part of the data, so look at what it matches first.

Fix a two-row header. Join the two rows into one by hand (for example City proper / Population), then delete the extra row. Blank cells under merged values can be filled with Power Query’s Fill down; for a pasted range, load it first with Data > From Table/Range (see Merged cells and “Column 1” headers).

Make the numbers numbers. After the footnotes are gone, replace − (U+2212) with -. In Excel, figures that are still text show a green triangle in the corner of the cell; Microsoft’s help describes selecting them and choosing Convert to Number, or using =VALUE() in a new column.

Copying takes what’s on screen: the current sort order, and only the rows that are shown.

Dedicated tools

  • wikitable2csv (wikitable2csv.ggor.de): a web page where, in its own words, you “enter the URL of the Wiki page containing the table(s)”, press Convert, then copy the result or download it as a CSV file. The site was up and its source repository (github.com/gambolputty/wikitable2csv) was not archived on 16 October 2026. We did not test how it handles footnotes or merged headers.
  • pandas read_html (for people who write Python): returns every table on a page as a list of DataFrames, and “attempts to properly handle colspan and rowspan attributes” (pandas 3.0 documentation). Use its match parameter to pick the table by a word it contains.

Reusing the data: the licence

Wikipedia’s text is available under the Creative Commons Attribution-ShareAlike licence (CC BY-SA 4.0), and unless a page says otherwise, also under the GNU Free Documentation License. If you publish a table you took from Wikipedia, or something built from it, Wikipedia’s “Reusing Wikipedia content” page asks you to:

  • credit the authors, for example with a link or the URL of the article,
  • include a licence notice with a link to the licence,
  • say that you changed the material, if you did,
  • release your adapted version under the same licence.

The licence notice is required when you publish or distribute the material. The figures in many Wikipedia tables come from other sources (the population table cites the United Nations). For anything you publish, the original source is usually the better citation, and it may have its own terms.

What TablePick does

TablePick is our extension. It reads the tab you have open. On 6 October 2026, on List of countries and dependencies by population (a different article from the UN list above; List of countries by population redirects to it), it ranked the population table first and exported 240 rows × 6 columns, with % and – intact. Its behaviour on Wikipedia-style tables is covered by automated tests, including sample pages modelled on a Wikipedia table with grouped headers and on hidden rows and footnotes.

  • Footnote marks. Kept by default, as on the page. Turn on Remove footnote marks like [1] in the cleanup options to drop them, including letter marks such as [a] and labels such as [注 1].
  • Merged cells are laid out the way the browser shows them, and a merged value is repeated in every row it covers (switchable).
  • Two-row headers become one row, joined with ” / ”. On List of largest cities the header cell is City proper[b], so the default gives City proper[b] / Population; with Remove footnote marks like [1] turned on, it becomes City proper / Population.
  • Hidden text such as the sort keys above is skipped, because by default only the text visible on screen is read.
  • Sort order. It reads the rows in their current order, so if you sort by a column on the page first, the export follows that order.
  • Formats. XLSX, CSV, TSV, JSON, Markdown, or copy to the clipboard.

All of the above is on the free plan. Two limits worth knowing: in XLSX, numbers are written as numeric cells only when they are plain (1425423212, -0.5). Figures with thousands separators, % or − arrive as text unless you turn on Clean numbers, which is a Pro feature (it also turns % into a fraction: −0.18% becomes -0.0018). And TablePick doesn’t refresh: each export is a snapshot of the page as it was.

When you don’t need any of this

  • One small table with no footnotes in the cells you need: copy and paste.
  • You use Google Sheets and want the table to follow edits to the article: IMPORTHTML.
  • You write Python and want several tables from many articles: pandas read_html.

For other ways to get web tables into a spreadsheet, see Getting a web table into Excel. For Markdown or JSON instead of a spreadsheet, see Turn a web table into Markdown or JSON.

Sources

Read on 16 October 2026.

All guides