I'm using ImportHTML() on a Google Sheet to import a large table from a website. It's great, and it's now all there, but now I want to hand-modify the data to my needs. Unfortunately, every time I try to make a change all the data goes away and I get a #REF error in the cell which contained the ImportHTML() equation.

So, I now have a document with a single ImportHTML() cell that magically displays all the data from the website. I want to have an array of cells containing fixed data fetched using ImportHTML(). How can I do this?

  • Do you want to filter the data or perform a query or just keep track of a few values?
    – Jacob Jan
    Commented Mar 16, 2015 at 18:44
  • Nothing that complex; I'm just hand-editing a table of GPS products to my needs (e.g. removing formatting, adding prices, making certain items clearer to me, etc). Commented Mar 16, 2015 at 22:28

2 Answers 2


The solution was to select all the cells (I can't find a Select All menu item, but on OS X Command-A worked), then choose Copy, then Paste special/Paste values only. Bingo: all the cells whose values depend on the ImportHTML() in A1 now have their values pasted.

  • 1
    That's just like it works in MS Excel! I didn't know that was possible in Google Docs. Thanks! Commented Mar 15, 2015 at 23:44

You cannot modify the data provided fetched with IMPORTHTML: if you do, you'll indeed either erase it all (if you modify the cell containing the IMPORTHTML function), or a reference error.

However, you can achieve your goal by referencing the imported data from another sheet. Let's assume you have your IMPORTHTML call within sheet Sheet1. You can create a second sheet, e.g. Sheet2, and insert the following statement in its A1 cell: =Sheet1!A1. Then, by dragging diagonally the blue square that appears at the bottom of cell A1 towards the bottom right of your sheet, you'll get a copy of your imported cells. You can modify any cell you want and leave those you want to keep untouched. For a clean look, you probably want also to hide Sheet1 and only work with Sheet2 from that point on.

  • Your solution didn't work for me. First, your equation for A1 in Sheet2 was wrong (I kept getting "Error: an array value could not be found"); it needed to be =Sheet1!A1. Second, the result was a Sheet2 where each cell was a reference to Sheet 1, which meant I had to completely re-type any cell I edited, rather than just modifying it. Commented Mar 14, 2015 at 19:57
  • Clarification: the second problem was due to my preference, and probably wouldn't be an issue for someone else. The first problem, though, was real. Commented Mar 14, 2015 at 20:28
  • You're correct, an array formula won't work but a simple =Sheet1!A1 will work as advertised. The solution with copy paste value that your propose has the advantage that you can edit instead of replace values, however, if the source data changes for some of your unedited values, your spreadsheet will not remain up to date with them. Commented Mar 16, 2015 at 12:08
  • Thanks; you should update your answer to include the correct formula; it will help someone else with different needs from me. Commented Mar 16, 2015 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.