I want conditional formatting to update/refresh automatically

Copper Contributor

I keep a daily menu for my family on an excel spreadsheet that's open on my laptop at all times. I set up  conditional formatting to highlight the current day's row of dishes and special notes. However, it does not automatically move to the next day's row unless I close the file and then reopen the file.

How do I get it to automatically highlight the next day at the stroke of midnight or else click something to get it to refresh when I look at the page?

1 Reply


You are probably using TODAY() to get the current date. TODAY() is a volatile function. It means that it recalculates whenever the spreadsheet recalculates. Reopening the file triggers the recalculation so the TODAY() function and conditional formatting updates.


You could also use a keyboard shortcut for manual recalculation - F9 or Fn + F9. 

If you want it to be done 100% automatically then it is possible with help of macros. 


I was not sure how your layout looks like, so I just drafted my version and added a button that lets you recalculate the whole workbook with a click. See the file attached.