Tiny Lever home

How to copy a table from a website into Excel

Selecting a table on a web page and pasting it into Excel usually works for simple tables, and fails in predictable ways for the rest. This guide shows the quickest method first, then what to do when the paste goes wrong.

Method 1: select, copy, paste

  1. Put your cursor just before the first cell of the table (or on the heading above it).
  2. Click and drag to just past the last cell. On long tables, click at the start, scroll down, then hold Shift and click at the end.
  3. Press Ctrl+C (Cmd+C on Mac).
  4. Click an empty cell in Excel or Google Sheets and press Ctrl+V.

If Excel offers paste options, "Keep Source Formatting" brings over the page's colours and fonts, while "Match Destination Formatting" or "Paste Values" gives you plain data. For analysis, plain data is usually what you want.

Common problems and fixes

Everything lands in one column or one cell

This happens when the page's table is built from styled blocks rather than a real table, or when you copied from a viewer that strips structure. Try pasting into Google Sheets first, which is often more forgiving, then download from there. If the page is not a real table, selecting and copying will not give you rows and columns, and you will need to split the text yourself (Data tab, Text to Columns).

Numbers are left-aligned and will not add up

Excel is treating them as text, often because of commas, currency symbols or hidden spaces. Our guide on cleaning up pasted table data in Excel covers the fixes.

Footnote markers such as [1] appear in cells

Wikipedia-style tables carry reference numbers inside the cells. Use Find and Replace (Ctrl+H) with the pattern [*] in the Find box, and leave Replace empty. In Excel, the asterisk is a wildcard, so this removes bracketed markers. Check the result, because it also removes any legitimate bracketed text.

Merged cells leave blank gaps

When a cell spans several rows on the page, only the first row gets the value. Select the column, press Ctrl+G, choose Special, then Blanks. Type an equals sign, press the Up arrow, and press Ctrl+Enter. Every blank now repeats the value above it. Convert the column to values afterwards (copy, then Paste Special, Values) so the formulas do not break when you sort.

Method 2: Excel's own web import (Power Query)

For tables you will refresh again, Excel can read a page directly. In the Data tab choose Get Data, From Other Sources, From Web (the exact menu names vary a little between Excel versions). Paste the page address, and Excel shows a list of tables it found with a preview. Pick one and load it.

This avoids copy-and-paste formatting problems and can be refreshed later. The limits: it only sees what the server sends. Tables that appear after the page loads through scripts, or that sit behind a login, often do not show up in the list.

Method 3: Google Sheets IMPORTHTML

In an empty cell type:

=IMPORTHTML("https://example.com/page", "table", 1)

Replace the address with the page, and the last number with which table on the page you want (1 is the first). Sheets fills the grid with the data and keeps it updated. Like Power Query, it works only for tables present in the page's original HTML and for pages that do not need a login.

Check the result before you rely on it

  • Compare the row count with the page.
  • Click a numeric cell. If the formula bar shows a leading apostrophe or the cell is left-aligned, it is text.
  • Look for hidden columns the page showed only on hover.
  • Scroll to the bottom for total rows that should not be mixed with the data.

Tables that load as you scroll

Some pages only create the rows currently visible on screen. If you select and copy, you only get those. Scroll through the whole table first, or look for a "show all" or "rows per page" setting, then copy. If the site offers its own Download or Export button, use that, since the data will be more complete than anything scraped from the screen.

Where TableTidy fits

If you do this often, TableTidy, our Chrome extension, finds the tables on a page, shows a preview and copies the table for pasting into Excel or Google Sheets. The free version copies up to 250 rows; Pro adds cleanup for merged cells, footnote markers and numbers. It runs in your browser and the data stays on your computer. It is not released yet; you can ask to be emailed once at launch. The methods above work without it.