How to keep formatting when sorting data in Excel?
In Excel, the cell formatting will be changed when you sort data. But in some cases, you may want to sort the data but keep the formatting in the original place as below screenshots shown. Now I will introduce a way to keep formatting when sorting data in Excel.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
With the ordinary way to format cells, these cells cannot keep the formatting when sorting, but if you format the cells with Conditional Formatting function, it will work.
1. Select the cell you will format, and click Home > Conditional Formatting > New Rule. See screenshot:
2. In the New Formatting Rule dialog, select Use a formula to determine which cells to format from the Select a Rule Type list, and then select the cell (only one cell) you select in the first step from the Format values where this formula is true box. See screenshot:
3. Click Format to go to Format Cells dialog, then you can specify the cell formatting you need.
4. Click OK > OK to close dialogs. Then the cell will keep its formatting when sorting.
Tip: You only can format one cell once time.
5. Select the data and sort them with clicking the Data > Sort. Then you will see the cell formatting is kept in the original position when sorting.