Why does your pasted HTML table show #VALUE! in column C? Why do dates like '2024-03-15' become 45366? Why does Excel split one logical row across three rows when you paste from a vendor’s pricing page?
Quick Answer
You don’t actually "convert" HTML to Excel — you import or parse its structure. The cleanest results come from using Excel’s built-in Get Data > From Web (for live tables), or Paste Special > Text (for static snippets). Avoid Ctrl+V alone — it triggers Excel’s auto-formatting engine, which guesses badly on dates, numbers, and hyperlinks.
All the Methods
| Method | Steps | Best For | Limitations |
|---|---|---|---|
| Get Data > From Web | Data tab → Get Data → From Web → Paste URL → Select table → Load | Live web tables with stable structure (e.g., government stats, financial dashboards) | Fails on JavaScript-rendered content; requires internet; can’t handle password-protected pages |
| Paste Special > Text | Copy HTML table → In Excel, right-click → Paste Special → Text → OK | Static reports, internal wiki pages, email tables | Loses hyperlinks, images, and background colors; merges multi-line cells into single cells |
| Save as .htm → Open in Excel | Save webpage as Web Page (.htm) → Double-click file → Choose "Open with Excel" | Complex layouts with nested tables or CSS styling | Excel may prompt to enable editing; some CSS classes break alignment; not viable for bulk use |
| Power Query HTML Parser (M code) | Advanced Editor → Paste custom M code that parses HTML nodes → Transform → Load | Repetitive scraping of same-site tables with consistent markup | Requires basic M language knowledge; fragile if source HTML changes |
| Third-party add-ins (e.g., ASAP Utilities) | Install add-in → Highlight HTML → Click "Convert HTML Table" button | Teams with standardized HTML sources and no IT approval for Power Query | Adds macro security prompts; not allowed in locked-down enterprise environments |
Method 1 Deep Dive
Let’s walk through Get Data > From Web — the most reliable method for public HTML tables. Say you need the latest quarterly revenue data from Acme Corp’s investor page. You open Excel, go to the Data tab, click Get Data, then From Web.
Now paste the full URL — not just the domain, but the exact page path. Excel fetches the page and shows a navigation pane listing every table it detects. You’ll see something like:
- Table 0: Navigation menu (ignore)
- Table 1: Q1–Q4 2024 Revenue Summary (✔️ select this)
- Table 2: Executive bios (skip)
Click Table 1, then Load. Excel creates a new worksheet named "Table1" with clean columns: Quarter, Revenue ($M), Growth %, Report Date. Values appear correctly: Q1 2024, $45,200,000, 12.3%, 2024-03-15. No manual date fixing needed.
Here’s the counterintuitive tip: If Excel doesn’t detect the table you want, don’t panic. Click Transform Data instead of Load. In Power Query Editor, go to View → Advanced Editor. You’ll see M code like Web.Page(Web.Contents("https://...")). Add this line right before the final in:
Source = Web.Page(Web.Contents(url)),
Data = Source{1}[Data], // ← change the index {0}, {1}, or {2} to test different tables
#"Changed Type" = ...
Try {0}, {1}, {2} until your target table appears in the preview pane. Trust me — I learned this the hard way after wasting 47 minutes on a hospital’s patient census page that hid its main table at index {3}.
Once loaded, the data stays linked. Right-click the table → Refresh pulls fresh values next time the site updates. That means your dashboard auto-updates without re-copying anything.
Method 2 Deep Dive
Sometimes you can’t use URLs — maybe it’s an internal Confluence page, an email from Legal, or a screenshot-turned-HTML by a colleague. That’s where Paste Special > Text shines. But you must do it *exactly* right — or you’ll get garbage.
First: Copy the HTML table *as rendered*, not the source code. In Chrome, right-click the table → Copy. Don’t use “Copy outerHTML” — that gives raw tags.
Now, in Excel, don’t press Ctrl+V. Instead, select cell A1 (or wherever you want the top-left corner), right-click, and choose Paste Special. In the dialog, select Text — not “Unicode Text”, not “HTML”, just plain Text. Click OK.
You’ll get clean, tab-delimited data. Here’s what appears in A1:E7 after pasting a vendor quote table:
| Item # | Description | Qty | Unit Price | Total |
|---|---|---|---|---|
| INV-8821 | Wireless Charging Dock (Black) | 12 | $89.99 | $1,079.88 |
| INV-8822 | USB-C to HDMI Adapter (Gen 3) | 8 | $42.50 | $340.00 |
| INV-8823 | Ergonomic Keyboard Bundle | 5 | $129.00 | $645.00 |
| INV-8824 | Noise-Cancelling Headset (Pro) | 3 | $249.99 | $749.97 |
| TOTAL | 28 | $2,814.85 |
Notice how numbers align right and text left — Excel inferred types automatically. But here’s where people mess up: they try to format the “Total” row as currency *before* converting the last column to Number. Do that, and Excel turns "$2,814.85" into text — and SUM() stops working. Fix it: select E3:E6 → Ctrl+1 → Number tab → Currency → OK. Then type =SUM(E3:E6) in E7. Done.
Keyboard shortcut pro tip: To quickly apply Number format to selected cells, press Ctrl+Shift+1. It’s faster than hunting through ribbons — and yes, it works even if your regional settings use commas for decimals.
Cheat Sheet
| Task | Action | Shortcut |
|---|---|---|
| Paste HTML as plain text | Right-click → Paste Special → Text → OK | None (right-click only) |
| Open HTML file directly | File → Open → Browse → Change file type to "All Files" → Select .htm → Open | Alt+F → O |
| Refresh live web query | Right-click any cell in imported table → Refresh | Alt+F5 |
| Convert text numbers to values | Select column → Data tab → Text to Columns → Finish (no delimiter) | Alt+A → E → F |
| Force date recognition | Select column → Ctrl+1 → Date → Choose format matching source (e.g., YYYY-MM-DD) | Ctrl+1 |
