'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Sort Excel Items by Font Color and Move to Bottom
Question details
The user wants to sort an Excel checklist by moving items with a specific font color, representing checked items or dates, to the bottom of the list. They experienced an issue where sorting by font color failed, despite cell color sorting working properly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reorganizing a checklist or task list by pushing completed items (indicated by a colored font) to the end of the spreadsheet.
- Observed behavior
- Sorting by font color did not work as expected because the target cells were blank and only contained red font formatting without actual text or data.
Verify that the colored cells you are attempting to sort actually contain text or data values. Excel cannot sort blank cells based on font color formatting alone.
Sort Items by Font Color Using Custom Sort
Use the built-in Custom Sort feature to reorder your rows based on text color, pushing specific colored items to the bottom of your dataset.
This method applies a custom sorting rule to your selected column. It is highly effective for checklists where specific font colors indicate completed tasks or highlighted dates.
Highlight the entire data range you want to sort, making sure to include both the checkmark column and the list items column to keep your rows aligned.
Navigate to the 'Home' tab on the top ribbon, click on the 'Sort & Filter' button, and select 'Custom Sort' from the drop-down menu.
In the Custom Sort dialog box, locate the 'Sort by' dropdown and choose the specific column containing the colored text.
Change the 'Sort On' dropdown to 'Font Color'. Next, select the specific font color you want to move and set the order dropdown to 'On Bottom', then click 'OK' to apply the sort.
Easily Sort by Font and Cell Color with WPS Spreadsheet
WPS Spreadsheet provides a highly compatible and intuitive platform to manage, filter, and sort your data using cell colors, font colors, or custom icons, fully supporting your existing Excel files.
- 1. Open your File in WPS: Launch WPS Spreadsheet and open your existing checklist or .xlsx file.
- 2. Access Custom Sort: Highlight your data, navigate to the 'Home' tab, click 'Sort', and choose 'Custom Sort'.
- 3. Apply Font Color Sort: Select your target column, set 'Sort On' to 'Font Color', pick your desired color, choose 'Bottom', and click 'OK' to reorder your list instantly.
Frequently Asked Questions
Why isn't my Excel data sorting by font color?
This usually happens when the cells are completely blank. Even if a specific font color is applied to the cell formatting, Excel requires actual text, numbers, or data within the cell to recognize and sort by font color.
Can I sort by both cell color and font color at the same time?
Yes. In the Custom Sort dialog box, you can click 'Add Level' to create multiple sorting rules. This allows you to sort your list by cell color first, and then add a secondary sorting level based on font color.
How do I bring specific font colors to the top instead of the bottom?
When setting up your rule in the Custom Sort menu, simply change the order dropdown from 'On Bottom' to 'On Top' for the selected font color. Click OK to instantly move those items to the top of your list.
