Stop Excel adding fill colour to cells when I type data into them

Thanks Sneha,

I actually discovered what setting causes it - there is setting under Options > Advanced called Extend data range formats and formulas.

(Microsoft's documentation for this feature says: select to automatically format new items added to the end of a list to match the format of the rest of the list. Formulas that are repeated in every row are also copied. To be extended, formats and formulas must appear in at least three of the five last rows preceding the new row.)

If you uncheck that (it's checked by default), Excel stops trying to format every cell that you add consistent data to the same way that the cells above it are.

Thanks for your help. One final thing though - this is the second time I've asked a question here in the forums and then discovered the answer myself after further research or experimentation. It is a shame you can't mark you own response as 'the answer' so that when people with the same issue visit the page they know that the answer has actually been found.

Hi,

I'm Sneha and I'd be happy to help you out with your question. Sorry for the inconvenience caused.

Conditional formatting can apply formatting to cells based on certain conditions, which may be the cause of the issue you're seeing.

To check if conditional formatting is the problem, select a cell that has the unwanted fill color, then go to the Home tab and click on Conditional Formatting. If you see any rules listed, that means you have conditional formatting applied to that cell.

To remove the unwanted formatting, click on the rule that is causing the issue and then click Delete Rule. You can also select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.

If conditional formatting isn't the issue, you can try turning off the "Fill handle and cell drag-and-drop" option. To do this, go to the File tab, click on Options, then click on Advanced. Scroll down to the "Editing options" section and uncheck the box next to "Enable fill handle and cell drag-and-drop". Click OK to save your changes.

Here are the steps to remove or modify conditional formatting rules in Excel:

1. Select the cells that have the unwanted fill color.
2. On the Home tab of the ribbon, click on the Conditional Formatting button and select Manage Rules.
3. In the Conditional Formatting Rules Manager dialog box, select the rule that is causing the unwanted fill color and click on the Edit Rule button.
4. In the Edit Formatting Rule dialog box, modify the rule to remove the fill color or change it to the desired color. Alternatively, you can delete the rule altogether by clicking on the Delete Rule button.
5. Click OK to close the Edit Formatting Rule dialog box, and then click OK again to close the Conditional Formatting Rules Manager dialog box.

If you want to change the background color of cells based on certain conditions, you can use conditional formatting to achieve this. Here are the steps to set up conditional formatting to change the background color of cells based on their values:

1. Select the cells that you want to apply the conditional formatting to.
2. On the Home tab of the ribbon, click on the Conditional Formatting button and select New Rule.
3. In the New Formatting Rule dialog box, select the type of rule that you want to create. For example, to highlight cells that are greater than a certain value, select "Format only cells that contain".
4. Set the conditions for the rule. For example, if you want to highlight cells that are greater than 10, select "greater than" and enter 10 in the value box.
5. Select the formatting options that you want to apply to the cells. For example, to change the background color of the cells, select the Fill tab and choose the desired color.
6. Click OK to close the New Formatting Rule dialog box.

For more Information, please refer to following resource "EXCEL change cell fill color automatic" -> https://techcommunity.microsoft.com/t5/excel/excel-change-cell-fill-color-automatic/td-p/3193995

If you have any other questions or need assistance with anything, please don't hesitate to let me know. It will be my pleasure to Assist you.

Best Regards, Sneha

Just a note that this feature is under 'Excel Preference - Edit' on the mac version.