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.
Keep formatting when sorting
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.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
