Jump to content


filtering by font color

filter font color

  • Please log in to reply
2 replies to this topic

#1 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 14 March 2016 - 12:58 PM

I have a large spreadsheet of program participants with non-completers marked in red font.  I wanted use the font color filter to remove them, but to my horror the filter also removed one participant (black/automatic font).  This could have been catastrophic for reporting; fortunately I was only working with a small group (filtered by text in another column) and knew how many there should be.  The file I'm attaching is the last name column copied and pasted with names replaced.  "???" is the one in black font that got filtered out with the red.  What happened, and how do I make sure it doesn't happen again?  I place a lot of trust in Excel and don't want to have to start quadruple-checking its work like I have to mine!


Thank you for your help!

Attached Files

#2 Jonathan


    Forums Administrator

  • Administrators
  • 887 posts

Posted 14 March 2016 - 11:04 PM

Hi Jenny,


The reason this is happening is that most of your cells have been explicitly set to 'Black', while the ??? cell is set to Automatic (which also happens to be black). Excel treats this as a separate format because it's possible for a theme to apply a different color to any 'Automatic' cells.


If you look in the color filter menu, you can see that Automatic is offered as one of the three options:




You can resolve this in this case by setting the ??? cell to black instead of Automatic, but you might want to consider an alternative approach to prevent this from happening again. One option might be to use a color other than black so that there is a clear distinction between colored cells and 'automatic' cells, but conditional formatting might offer a better solution, assuming that the coloring can be logically applied based on your data.


You can see more about themes and colors in the video lesson: Essential Skills Lesson 4-8 Understand themes.

For instructions on how to apply conditional formatting, see the video tutorial: Essential Skills Lesson 4-15 Use simple conditional formatting.


I hope this explains what's been going wrong and helps you to find a good solution. Please feel free to reply if you have any further questions about this.

Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.

#3 Jenny M

Jenny M

    Advanced Member

  • Members
  • PipPipPip
  • 32 posts

Posted 15 March 2016 - 01:33 PM

Oh my gosh; this is such a relief.  I'm so glad it was something simple!  I didn't realize black and automatic were different.  Easily fixed.  Thank you SO much!  Funding could hinge on my not misunderstanding Excel ...

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users