How can I keep excel from automatically changing the text color

Question

I have a spreadsheet I use to check files off from. I change the text color to red if the file is incomplete, and highlight with yellow once finished. If I haven't seen the file yet, the cell remains unfilled with black text. However, recently when I open the file or change tabs and then go back, ALL of the cells have red text. Hitting Ctrl+Z typically works to get it close to what it had been before, but other cells will turn back to black that had been red and/or highlighted. Any suggestions to prevent this from happening would be great.

Solution

Try File>Options>Advanced> and uncheck "Extend data range formats and formulas". Save/Close, then reopen and see if the problem persists.

NOTE: This will prevent certain formulas from updating if you extend the range of affected cells. For example, If you have in cell A2 =SUM(A1:B1) and you add data to cell C1, with Extend data range formats and formulas checked your sum formula will automatically update to =SUM(A1:C1). This will not happen if you uncheck the box.

This will not affect any formulas you have "auto filling" in a table however.