How to Sort Cells By Color in Excel

    Nisha GurungBy Excel 4 Mins Read

    If you have color-coded cells in Excel, you may wish to organize the data based on those colors.

    For Instance, keep all the Green cells with “Completed” status at the top. Or, a cell filled with Yellow with “Average Numbers” in the middle. 

    In addition to making your sheet aesthetically appealing, sorting cells by color will also help you in creating data visualization like a heat map.

    What I like most about Excel’s Sort feature is that it allows you to make a custom Color order.

    Now, let’s find out how you can sort cells by color in Excel.

    From Context Menu

    If you just need to sort your data based on one color, do it from the Context Menu. It’s easier as you can also see the items in that cell. 

    Simply, right-click on the Cell with color. Head to Sort. Then, choose any one given option.

    • Put Selected Color On Top: Organize values based on filled cells.
    • Put Selected Font Color On Top: Reorder values based on the Font Color.
    • Put Selected Formatting Icon On Top: Rearrange the column based on the Conditional Formatting Icon. 

    Suppose, if I chose Put Select Color On Top, Excel keeps the blue color at first. Similarly, the Yellow color that was at the beginning will move down.

    Using Filter

    From my experience, using a context menu is only simpler when you have smaller data. For a huge dataset, it can be a hassle to locate the specific shaded cell in thousands of columns. 

    During such a case, I prefer the Sort by Color option in the Filter tool. It is another quickest way to reorder the column by the cell or font color. 

    Firstly, enter these shortcut keys to turn on the Filter.

    Shortcut: Ctrl + Shift + L

    Now, click on the Filter button. Hover your cursor over Sort by Color and choose a Color. Remember, the one you pick will be at the top. 

    Using Sort Menu

    In the both above methods, we can put only the chosen shade at the beginning. But, if you have multiple colors and need to define your color order, the SORT menu is the best. 

    For Instance, you could put Green at first, Yellow on second, Red on third, and so on.

    The best part is you could also rearrange the rows based on the color. 

    Sort By Cell Color

    Example1: Sort Column  

    Suppose I have lists of Company Name and Country. The Countries are highlighted with 10 different colors. Here, I will set my own color order to sort by.

    1. Select the Data Range. 
    2. From the Data Tab, click on Sort Button.
      From the Data Tab, click on Sort
    3. On the Sort dialogue, choose Column Header in the Sort by.
      choose Column Header in the Sort by
    4. On Sort On, pick Cell Color.
      Pick Cell Color
    5. Select the Color and choose Order. Here, I picked On Top.
      Select the Colour and choose Order
    6. If you have multiple Colors, hit on Add Level. Set the Then by, Sort On, and Order. Once you’re done, hit OK.
      Add Level and specify Sort Colour, Order
    7. You’ll have sorted colors as specified.
      Sorted Column based on Cell Color

    Example 2: Sort Row

    1. Select your data.
    2. Head to the Data tab and click on the Sort.
      Head to the Data tab and click on the Sort button
    3. Click Options. On the Sort Options dialogue, select Sort left to right. Click OK.
      Options- select Sort left to right
    4. Expand the drop-down for Sort by and pick Row.
      Expand the drop-down for Sort by and pick Row
    5. For Sort On, choose Cell Color. On Order, pick Color and select On Left or On Right.
      For Sort On, choose Cell Color
    6. Just like above, click Add Level. Set your Then by, Sort On, Order, etc.
      click Add Level. Set your Then by, Sort On, Order
    7. Finally, hit OK. Excel Sorts rows as specified.
      Sorted Rows based on Cell Colour

    Sort By Font Color

    Sometimes, instead of the Cell color, you may have a different font color to separate the values. To rearrange the items by the Font Color, here’s how to do it.

    As an example, I have mentioned how to sort for columns. If you have highlighted a row, take a reference from Example 2 above. Make sure you change the Sort On to Font Color. 

    1. Firstly, select your data.
    2. Press Alt + A + SS Ribbon Shortcut to open Sort. 
    3. On Sort by, choose a Column Header.
      On Sort by, choose a Column Header
    4. Pick the Font Color option in the Sort On menu. Select the Color and choose the Order.
      Pick Font Color, Color, Order
    5. Click OK.

    Sort By Conditional Formatting Icon

    Next, you can also sort the cells based on the Conditional Formatting icons. It could be any icon like directional, shapes, indicators, ratings, etc. 

    1. Select Cell ranges.
    2. From the Data tab, click on the Sort button. 
    3. On Sort by, pick Column Title.
      On Sort by, pick Column Title
    4. Then, select the Conditional Formatting Icon in Sort On. Pick the Icon and choose your Position.
      select the Conditional Formatting Icon in Sort On
    5. Once you’ve added other levels, click OK.
      Once you add another level, hit OK
    6. Here’s the sorted data.
      Sorted Columns based on Conditional Formatting icon

    As a Business Student, Nisha first used Excel to outline data trends for her college projects. Later, she started exploring basic Excel functions when she realized it is a powerful tool for all businesses today. Nisha believes she can relate to many of the issues users face when starting their Excel Journey. She uses this knowledge and experience to curate contents that are fit for all types of Excel users. Apart from writing, she enjoys reading books and traveling.

    Related Posts

    Add A Comment