How to filter cells based on font color?
Someone sent me an Excel 2010 spreadsheet with more than 50,000 records with the request to review the "highlighted in red" records. My current method of scrolling with the assumption I have a keen eye is not working well. Is there a way to filter or find cells based on font color?
The feature to filter for color (font or background) is already implemented since Excel 2007.
Read more about it on: http://msdn.microsoft.com/en-us/library/cc952296.aspx.
Here is a Youtube video for the case you overlooked the filter option.
You tagged your question with Excel 2010, so I assume you have 2010 installed. Only 2003 won’t have this option
This uses VBa.
Add the developer toolbar to the ribbon. In the Controls panel, click Insert and click on the button
Draw a button on your Excel work sheet. Click OK.
In the new Window, type the following so only this code is on the page (nothing else)
Sub Button1_Click() Dim i As Integer i = 1 For Each c In range("A1:A10") 'UPDATE THIS FOR THE CORRECT RANGE If c.Font.ColorIndex = 3 Then Range("M" & i).Formula = "=HYPERLINK(" & c.Address & ", " & c.Address & ")" 'CHOSE EMPTY COLUMN i = i + 1 End If Next End Sub
In this example, I’ve chosen a free column (M) in this case. If M isn’t free, then do change the M to a column which is free. You will also need to state the correct index range.
So, every time it finds a cell with the red font (with index of 3) it will write the value to the M column. So with your new list, you can navigate to the cell in question easily.
Big thank you to @mehow who suggested making the results clickable.
This uses a sort by cell color strategy. Consider that the font color=the cell color in the advanced sort feature in Excel 2016 and possibly Excel 2013.
I experiences a similar situation. I pasted my data into Excel and then sorted by cell color which also is equivalent to the font color. My table included five columns of information with the first column as the list of names. Columns two through five consisted of content. Within those cells, random cells consisted of text of varying text colors but the text colors are significant. My query was to extract those records that included the colored text.
Therefore, my sort strategy began with the first level sorted by column 2 with a cell color of “black” -for example. This sorted the table by the first column and every record with a cell that consisted of black text. The second level was sorted by column 3 with the primary sort based on the color black. I continued the levels only sorting those columns with colored black cells/text. The end result was a table of information with all the records sorted by font color and the rows with no color at the bottom of the table.
From there I extracted the cells only with black text. This is not automatic but it is someone of simplified common sense approach.
Our Awesome Tools
- Check your IP Address precisely
- Online JSON Formatter with Syntax Highlight
- Online CSS Minifier Compressor