If Excel is not keeping formatting, first check whether the change comes from conditional formatting, a table style, pasted content, or the file type you are saving. Then apply the format to the affected cells again and save the workbook as an Excel workbook (.xlsx); CSV and other text formats do not preserve cell formatting.
Start by identifying what changes
Notice when the formatting disappears and how many cells are affected. A single cell changing after you type or paste suggests a local rule or paste option. A whole column changing when new data is added may point to an Excel table style or a fill operation. Formatting that looks correct until you close and reopen the file often points to the save format or to a different copy of the workbook being opened.
Before making broad changes, save a separate copy of the workbook. Then try a small, reversible test: format one affected cell, save, close, and reopen the copy. This helps distinguish an issue with the workbook from one particular action, such as importing data or pasting from another program.
- Only certain values look different: inspect conditional formatting and number formats.
- New rows inherit an unexpected appearance: check the table style and the way the row was added.
- Formatting vanishes after saving: check the file extension and save format.
- You cannot change formatting at all: check whether the sheet is protected or the cells are locked.
How Excel decides what a cell looks like
A cell’s appearance is not always controlled by the font, fill, borders, and number format you set directly. Excel can also apply a named cell style, a table style, or a conditional-formatting rule. When more than one source affects the same cell, the result you see may differ from the direct formatting you expected.
Conditional formatting is a common source of confusion because it changes appearance according to a rule, such as highlighting values below zero. The underlying fill color may be red even though the cell’s ordinary fill is set to another color. A table style can similarly apply banded rows or column emphasis. Those features are not necessarily deleting the formatting you applied; they may be displaying another layer of formatting on top of it.
Number formats also affect what appears in a cell. For example, the value 0.25 can display as 25% when the cell uses a percentage format, or as 0.25 under General. Changing the format changes the display, not usually the stored value. If the cell contains text that looks like a date or number, applying a number format may not convert that text into a numeric value.
Fix formatting that changes after typing or pasting
When you paste data, Excel may bring over source formatting along with the values. The paste options determine whether the destination keeps its appearance or adopts the source’s. After pasting, use the Paste Options button that appears near the pasted range and choose an option such as Values when you want the data without the original formatting. You can also use the Home tab’s Paste menu and choose Paste Special options appropriate to the task.
If the cells already have the desired appearance, another approach is to paste values into the destination rather than use a regular paste. This is useful when copying from a website or another workbook that has different fonts, fills, and number formats. Check dates and numbers afterward: pasted values can arrive as text, and removing source formatting does not guarantee that the data types are correct.
Typing into a cell should not normally erase its direct formatting. If it seems to, check whether the cell is in a table, whether a conditional rule changes its appearance based on the new value, or whether an automatic fill action applied a format. When using the fill handle to extend a sequence or copy cells, inspect the Auto Fill Options button and select the option that matches your intent. To remove direct formatting while keeping the cell contents, select the cells and choose Home > Editing > Clear > Clear Formats. This clears formatting such as fonts, fills, borders, and number formats, but it does not remove values or formulas. Conditional-formatting rules are managed separately.
Check conditional formatting and table styles
To find rules affecting a cell, select it and choose Home > Conditional Formatting > Manage Rules. In the rules manager, inspect the “Show formatting rules for” selection; choose the current worksheet if you need to see rules beyond the selected cells. Look for rules whose “Applies to” range includes the affected cells, and check their conditions, formatting, and order. A rule can apply to a larger range than its creator intended.
Edit or delete only the rule that is causing the unwanted appearance. Deleting every rule can remove useful alerts, such as overdue dates or values outside an allowed range. If a format changes when a value changes, temporarily changing that value can help establish whether a rule is responsible. Restore the original value after the check.
For a formatted table, click inside it and look for the Table Design tab. Its Table Styles options control the table’s overall appearance, including features such as banded rows. A table may extend its style to newly added rows by design. You can choose another style or adjust table-style options; if you need an ordinary range instead, use Table Design > Convert to Range and confirm. Converting to a range removes table functionality, so do it only if you no longer need features such as structured references and automatic table expansion.
Make sure the workbook format supports formatting
Excel workbooks such as .xlsx and .xlsm can store cell formatting. CSV files cannot: they store plain text values separated by delimiters, not fonts, fills, borders, multiple worksheets, or Excel rules. When you save a workbook as CSV, Excel warns that some features may be lost. If you reopen that CSV, the formatting will not be there to restore; keep an .xlsx copy as the formatted source and export a separate CSV when a system specifically requires one.
Older or specialized formats may also have feature limitations. If the workbook must be shared in a particular format, save a copy in that format and review Excel’s compatibility warning rather than assuming every feature will carry over. For ordinary ongoing work in Microsoft 365, use Excel Workbook (.xlsx) unless the file needs macros, in which case an Excel Macro-Enabled Workbook (.xlsm) may be appropriate.
Also confirm that you are reopening the same file you saved. Cloud folders, email attachments, and similarly named copies can make it easy to open an earlier version. Check the file name and location in Excel’s title bar or use File > Info to inspect workbook details. If the workbook is stored in a shared location, another person’s changes or a sync conflict may mean you are viewing a different saved version.
Protection, styles, and workbook-level problems
A protected worksheet can prevent formatting changes if the protection settings do not allow formatting cells. Check Review > Unprotect Sheet; if the sheet is protected with a password, you will need the password or help from the workbook owner. If you manage the workbook, review the protection options before protecting it again so users can perform the formatting tasks they need. Protection is different from a formatting glitch: it restricts actions rather than silently choosing a new style.
If Excel refuses to apply formats or reports too many different cell formats, the workbook may contain an excessive number of unique formats. Excel documents a limit of 65,490 unique cell formats. Repeatedly copying whole sheets or data from many workbooks can introduce redundant styles. Avoid copying entire sheets when you need only data; paste the needed values or formats into a clean workbook, and remove unused styles only with a reliable method that preserves the workbook’s intended appearance.
If the problem follows one workbook but not a clean test workbook, save a copy and try opening and repairing that copy: choose File > Open > Browse, select the file, open the arrow beside the Open button, and choose Open and Repair. This is a recovery option, not a guaranteed formatting fix. Keep the original unchanged, and compare the repaired copy carefully. If the issue occurs across many workbooks, install available Microsoft 365 updates and check whether an add-in or shared template is influencing the workflow before assuming the workbook itself is damaged.
A practical troubleshooting sequence
Work from the least disruptive checks toward broader repairs. This order helps preserve useful rules and styles while narrowing down the cause:
- Save a separate copy, then note the exact cells and the action that changes them.
- Check the file extension. If it is .csv or another text format, return to the formatted .xlsx or .xlsm source.
- Inspect conditional-formatting rules and, if relevant, the table style.
- Test a paste-values operation or the appropriate Auto Fill option instead of repeating the action that introduced the change.
- Check sheet protection and whether the affected cells accept formatting changes.
- Test the workbook after saving and reopening. If only this file has the problem, try recovery on a copy; if many files do, investigate updates and add-ins.
On Excel for the web, the ribbon labels and available options can differ slightly from desktop Excel, and some advanced workbook-management features may be limited. If a desktop-only command is unavailable in the browser, open the workbook in the Excel desktop app. On Mac, the ribbon concepts are generally similar, but some dialogs and shortcuts differ from Windows; use the corresponding command in the Mac ribbon rather than relying on a Windows-only keyboard shortcut.
Frequently Asked Questions
Why does Excel change my formatting when I paste?
Regular paste can bring the source cells’ formatting with their contents. Use a Paste Options choice such as Values when you want to retain the destination’s appearance, then verify that dates and numbers pasted as the intended data types.
Why does my formatting disappear when I save as CSV?
CSV stores plain text data, not Excel formatting or worksheet features. Keep a formatted .xlsx or .xlsm workbook and create a separate CSV export for systems that require that format.
Why does a cell color change when I enter a different value?
A conditional-formatting rule may be responding to the new value. Select the cell and inspect Home > Conditional Formatting > Manage Rules, including the rules’ conditions and Applies to ranges.
How do I remove formatting without deleting data?
Select the cells and choose Home > Editing > Clear > Clear Formats. Values and formulas remain, but direct formatting is cleared; manage conditional-formatting rules separately if their appearance persists.
Can a protected sheet stop me from formatting cells?
Yes. Sheet protection can disallow formatting changes. Check Review > Unprotect Sheet; a password-protected sheet requires authorization from its owner or the password set for it.
Related
- Excel Not Responding? Causes, Fixes, and Recovery Steps
- Excel Slow? Causes, Fixes, and Ways to Speed It Up
- Cash Flow Excel Sheet: Build a Reliable Monthly Forecast
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Make Columns in Word Without a Table: A GuideTo make columns in Word without a table, select the text you want to split, then choose Layout…
- 2-Dimensional Data Table in Excel: 10 Practical UsesIn Excel for Microsoft 365 on Windows, create a two-variable data table by putting a formula link in…
- Excel Not Responding? Recover Files and Find Temp DataIf Excel is not responding, first give it a minute and avoid repeatedly clicking or ending the task;…
- How to Set Data Validation in Excel for Reliable EntriesTo set data validation in Excel, select the cells you want to control, then choose Data > Data…
- Conditional Formatting for Negative Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home >…
- Remove Duplicates in Excel: Keyboard Shortcut and StepsIn Excel for Microsoft 365 on Windows, select a cell in your data and press Alt+A, then M…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.