I have a very large table in Excel (1000's of rows) and I filter it to only show 10 rows.

I wonder if there is a way to delete the rows not shown (i.e. don't meet filter conditions)? This would enable me to reduce the file size before I send it.

There are many thousands of rows down under the table the user has created complex formulas and graphs which wont carry if I copy across to another worksheet if I just copy the rows.

  • 2
    If the data is required for the "complex formulas and graphs", you can not delete the rows. These constraints seem to eliminate most ways to answer your question. You need to ask yourself what you are willing to give up in order to reduce the file size?
    – CharlieRB
    Commented Feb 8, 2012 at 16:04
  • I was able to invert my selection, select the first column on the rows I didn't want, and then right-click and select the option to delete the rows - that did the trick for me. Commented Mar 17, 2017 at 13:53

7 Answers 7


Try this way for a quick solution:-

  1. Copy the filtered 10 results into another sheet
  2. Delete the actual sheet


As per the update, below are the steps:-

  1. Before starting, take a backup copy of excel sheet
  2. Assuming you are filtered all the records and showing only 10 Rows
  3. Remaining 1000's are hidden
  4. Click on Office Button
  5. Click on Prepare option
  6. Click on Inspect Document
  7. Refer this screenshot, how it looks enter image description here
  8. Click on Inspect button
  9. You will see a option "Hidden Rows and Columns" with "Remove All" button
  10. Click on Remove All button
  11. Click on close button
  12. Finally if you see, it has removed all "Hidden Rows and Columns"

Refer this screenshot

enter image description here


In Office 2010, Inspect Document can be found here:

enter image description here

  • 1
    Impressive stuff, i would never have found this. however running this it says "could not complete" and then gives warnings about messed up references and the graph data disappears :-/
    – GreyCloud
    Commented Feb 8, 2012 at 15:34
  • It seems that some of the visible cells use data stored in hidden cells, therefore you can't just copy the visible cells and paste them into a new workbook, and you can't just delete the hidden cells. Copy the visible cells as explained above, then in the new workbook "paste special" and then "paste values". This inserts values rather than formulas, this should work for you
    – ssollinger
    Commented Feb 8, 2012 at 15:49
  • I added the location of the inspect document thing for Excel 2010. Hope that's ok.
    – Timmmm
    Commented Jan 15, 2015 at 11:57
  • 1
    be sure to have your backup, this totally wiped out my entire spreadsheet
    – lewis
    Commented Apr 21, 2016 at 15:46
  • Is there a way to do this only for a particular Sheet? Commented Jul 2, 2020 at 0:59

The way that worked for me was, assuming the filter is easy to reverse:

  1. Clear your filter.
  2. Create a temporary column, say called 'TEMP ORDER'.
  3. Set every value in that column to 0
  4. Reverse your filter (filter for everything you want to delete)
  5. Set every value in the 'TEMP ORDER' column to 1 on the filtered results
  6. Clear your filter.
  7. Sort your data by the 'TEMP ORDER' column, smallest to largest.
  8. Find on which row the first '1' occurs
  9. Resize your table (Design tab), having the last row be the row before the first '1'
  10. Delete the rows that are no longer in your table.

This may be a preferable solution if you don't want to mess up any other sheets in your workbook and are concerned about what might happen if you copy and paste your data around.

  • 2
    The one marked as answer above is good in most cases, but with 800,000+ rows of data in a CSV file this way worked much better for me. Excel kept hanging by trying to make a copy of all that data.
    – Fütemire
    Commented Oct 18, 2017 at 17:42

Why not just copy visible cells to a new sheet? Go to:

quick access tool bar drop downmore commandscommands not in the ribbonselect visible cellsadd

When you click this it will select everything that is visible and you can copy and paste everything that's visible.

  • 1
    When I tried to do this, I lost all kinds of cell size/formatting. Commented Jan 5, 2017 at 14:24
  • 1
    @JaySullivan did you copy with formatting? You can copy and determine what you paste with paste special and it shouldn't lose anything. Commented Jan 9, 2017 at 12:24

The accepted Answer above relating to "inspect document" is excellent.

In addition, the procedure indicated would apply to the whole workbook, so you might be messing other worksheets in the same workbook. In this case, you have to move the worksheet to a separate workbook, apply the procedure, and move the worksheet back to your original workbook. Cross linking of references/formulas/chart series among worksheets, involving the worksheet in question, might be a challenge.

As an alternative to this other answer (which cannot handle the case of charts, etc., as requested by the OP), Home -> Find & Select -> Go To Special -> Visible cells only. It appears to be exactly the same command (and then I wonder why it is listed under Commands Not in the Ribbon).

  • 1
    shortcut for "select Visible cells" is (alt+;) then just right click the selection to delete rows. Hope this helps! :)
    – tinker
    Commented Jul 27, 2017 at 7:34

I had this exact same problem. To solve:

  1. Highlight the 10 rows that you want to keep and change their background color
  2. Clear all filters
  3. Apply a new filter on one of the columns, select "Filter by Color". Instead of picking the color that you used, pick "no fill".
  4. This brings up all of the unwanted rows. Highlight them all and delete.
  5. Remove the filter and you'll be left with just the 10 rows that you want. All charts and cell references will be in tact.

Easy... I had the same problem.

  1. Select All in the filter and untick all unwanted info and click OK.
  2. Clear all filters. (You will notice that all rows that were unticked are now highlighted.)
  3. Press Ctrl- to delete those rows.

This might be overly simplistic but why not just copy/paste the 10 rows you've filtered down to into a new spreadsheet?

  • 1
    because many thousands of rows down the user has created complex formulas and graphs which wont carry if i copy across :*(
    – GreyCloud
    Commented Feb 8, 2012 at 14:25

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .