Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I've got a rather large spreadsheet that I need to filter some rows out of. In the spreadsheet, all rows whose M-Column doesn't equal "on" must be removed. I applied a filter, and re-saved the document, but that kept the unwanted rows - how can I permanently delete these rows instead?

    A    |    B    | ... |  M
-------------------------------
Jonathan | Sampson | ... | on
Jeff     | Atwood  | ... | 
Joel     | Spolsky | ... | on

So in this example, I would want to completely remove Jeff Atwood's record.

share|improve this question
add comment

3 Answers

up vote 6 down vote accepted
  1. Select a cell in Column M.
  2. Select all cells in the spreadsheet with Ctrl-A.
  3. Open the Data->Filter->Standard Filter menu.
  4. Set the first row of the filter so that Field Name is "Column M", Condition is "Not Equals" and Value is "on".
  5. Click OK to apply the filter to the spreadsheet.
  6. Select all rows in the spreadsheet with Ctrl-A. To unselect any column header cells, hold Ctrl and click the appropriate row.
  7. Right-click on any of the row numbers and select Delete Rows.
  8. Select one of the column header cells, or any non-blank cell.
  9. Use the Data->Filter->Remove Filter menu item to restore the remaining rows.
share|improve this answer
add comment

My quick and dirty solution would be to create another column with numbers 1..n. Then sort on column M. Select and delete the rows that are not "on". Then restore the original order by sorting on that new column with numbers 1..n.

share|improve this answer
add comment

When you apply a filter, you only show/hide the desired rows. Try this: FIRST - HAVE A BACKUP...hehehe After apply a filter, you need select the rows, right button of the mouse and select: Exclude, Remove, Delete (I dont know which option will show.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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