Data Validation and Conditional Formatting in Shared Files

Hi Siobhan

Thanks for your question. Yes, when you are using a Shared Workbook Excel restricts many of the functionalities such as pivot tables and as you say Conditional Formatting. What you can do is temporarily unshare the file by selecting:

Review, Share Workbook
Untick the option 'Allow changes by more than one user...'
Save the file again

Conditional formatting will be available now for you to set up your conditional formats.

When finished then select Review, Share Workbook again and this time tick the option to allow changes by more than one user.

Sharing a workbook allows data entry and formatting primarily and not major design changes. To do that you have to unshared the workbook first. You may want to let the other users know not to work on workbook while you are doing this.

Regards
Doug
Best STL