Sometimes, when dealing with Excel, we can get some inexplicable errors that we have to deal with. Some of these can be really frustrating, especially if they are blockers for further work.
In the example below, we will show what to do when you stumble upon the error of Excel cell format not changing. We will inspect the causes and give possible solutions.
Excel Cells Format Not Changing
If we end up in a situation where we have problems changing the format of a cell in Excel, or the format is not updating as we are expecting, we can inspect a couple of reasons. Some of the steps that we can take to troubleshoot this issue are as follows:
- Check the Cell Type
We need to make sure that the cell that we want to format is not merged with another cell and that it is a regular cell, rather than a header or label cell. Merged cells can sometimes cause formatting issues. The unexpected formatting behavior of merged cells can happen because they create a single cell that spans multiple columns and rows. Formatting might not apply uniformly across the merged cell, and we might need to adjust formatting settings or consider using non-merged cells to achieve the desired appearance.
- Check for Conditional Formatting
If we have conditional formatting applied to the cell or its nearby cells, this might override our manual formatting changes. We should clear all of the conditional formatting rules to see if this will resolve the issue.
If we have a list of random numbers from 50 to 100 in our range A2:A11, and we apply the conditional format rules by selecting the range, and then going to Home >> Styles >> Conditional Formatting >> Highlight Cells Rules >> Less Than:
And choose 75 as our threshold value, with a red color background for all of these values, we will get the following results:
If we try to apply the manual formatting now, for example, fill the background of every cell in a range with green color, we would not be able to override the cells that are already colored:
We would need to clear our Conditional Formatting by going to Home >> Styles >> Conditional Formatting >> Clear Rules and choose one of the two options (clear rules for all the cells or selected range):
When we click one of the options, all of our cells will be colored in green:
- Clear Cell Formats
In some instances, formatting conflicts can happen due to residual formatting from copying and pasting or some other action. If this happens, the best option is to clear all cell formatting and then apply the desired format again.
To do this, we would select the cell (in our example, we could say cell C2), then go to the Home tab >> Editing >> Clear >> Clear Formats:
- Override Number Formats
If we are dealing with number formats, such as currency, time, or date, Excel might auto-detect the format based on the content that you enter. We can override this by explicitly setting the number format.
We would do that in our range A3:A11, right-click and choose Format Cells:
On the window that appears, we will go to the Number tab and choose the desired format from the list:
- Format Painter
If we have a certain cell with the correct formatting, we can use the Format Painter tool to copy the formatting to the rest of the cells that are problematic.
For our example, we will select the cell A2, then choose the Format Painter, and apply it to the rest of the cells (range A3:A11):
When we do this, our range will correspond to cell A2 in terms of formatting:
- Re-entering Content
Sometimes, re-entering the content of the cell can help refresh the formatting.
- Workbook or Application Issue
If we have a certain issue that persists across multiple cells and even different workbooks, we might have an issue with our Excel and its installation. If this persists, we can restart our Excel or our computer, or repair the Excel installation if necessary.
- Cell Protection
If our cells are protected, this can prevent us from applying certain formatting changes. To inspect if the cells or worksheet are protected. If so, we might consider removing the protection to apply the formatting.
If the cells are locked, the screen that we would see would be:
To confirm, we would go to the Review tab and check if the cells are protected or not. If we have the “Unprotect Sheet” option, then it is clear that they are:
- Excel Version Compatibility
If we are sharing our files with other people while using different Excel versions, some formatting figures might not work in the same way across these different versions.
- Corrupted Workbook
In some rare cases, the Excel workbook itself might be corrupted. If this happens, it would be advisable to create a new workbook and then copy the content to see if the issue persists.