Isolate Rows that Contain Certain Values in One Column (Excel)

I need help with what I think is a simple Excel formula or function. I have two columns. As an easy example let's say the first column is numbers and the second column is colors:

| 1 | Red  |
| 2 | Blue |
| 3 | Red  |
| 4 | Red  |
| 5 | Green|
| 6 | Brown|

Let's say I just wanted the rows with red in the second column and have everything else on the sheet deleted. How would I do that? To be clear, I want the values in column one to remain as well. This is how I would like it to look:

| 1 | Red   |
| 3 | Red   |
| 4 | Red   |

It seems like it should be simple but I can't seem to find any way to do that. There are similar question on the forum but they are more complex than what I'm asking and require super long formulas. If that's what is required, I suppose that's what I'll do but just wanted to see if there was an easy way to do it as well. Thanks for the help!


You can use autofilter to filter the second column. Then select and copy the visible cells only, remove the filter, delete everything and paste what's in the clipboard.

Or do you need a dynamic solution? Then you could build a pivot table on a separate sheet. Put the desired color in the page filter and refresh the pivot table when the source data has changed.

Need Your Help

Cache base url #{request.contextPath} in a variable to use multiple times on page & included pages

jsf jsf-2

I need to cache #{request.contextPath} into a variable so that it could used multiple times within a page. Previously while I was working with Mojarra 2.1.3, I could use ui:param for this purpose &...