Conditional Formatting in Sharepoint Excel

Question

I have an excel file in Sharepoint, and need to set conditional formatting such that if any value is entered into a cell in one of two particular columns, the entire row for that cell receives a format. A large team of people use the document, and put their initials in the column to claim responsibility for the matter in the row. They requested I set the document so that the 'claimed' rows receive a fill color. I am unable to set this up in Sharepoint, but am able to set it up if I download the file to Excel desktop and re-upload. I use the formula =NOT(ISBLANK($A1)), applied to the whole set of cells I anticipate to have values, with A being the column where team members enter initials. And another rule, same formula, for another column where a different set of team members may enter initials to claim a matter. Another member of my team added conditional formatting to highlight duplicates in two other columns. There is now an issue where sometimes, cells in those columns are not receiving the color fill format when the row has been claimed. It does not happen to every row, and does not happen exclusively to rows where cells in those columns would be highlighted as duplicates. I have downloaded it to Excel desktop, and in rule management set the row fill format to supersede the duplicate-in-column format. A few days after re-uploading, issue is back. Rule management in Sharepoint shows a long list of rules, applied to seemingly random sets of cells, rather than the rules I have set.

Answer 1

Hi LWDuncan,

Sorry for the inconvenience it may have caused.

For documents stored in SharePoint, you can open the document directly through the Office desktop client without downloading it.

And in fact, SharePoint theoretically will not automatically modify the data in the user's documents. Since you have stored the document in SharePoint, you can view the historical version of the document from SharePoint Online to check whether the document has no other edit records since your last edit. See View previous versions of Office files.

And we would like to collect some more information to further check it:

1. Have you shared this workbook with others?
2. Does the same problem also occur on other workbooks?

Appreciate your understanding.

Best regards,

Madoc

Answer 2

Hi LWDuncan,

Have you checked the above reply? Feel free to post back if you need further assistance.

Best regards,

Madoc

Answer 3

Hello, 

Thank you for the reply. The issue is not with downloading vs. opening in app, but with the conditional formatting. I will open in app rather than downloading the doc in the future! To answer the questions - yes, this workbook is shared with a large team of people (around 40) who all use it and make edits to the cells themselves, though other members have not been making edits to the conditional formatting, beyond the one time I mentioned above, where someone set the duplicate conditional formatting. I am not sure if the problem occurs on other workbooks, this is the only workbook that we need to use this type formatting on. 

Best,

Leah

Answer 4

Hi Leah,

Thanks for your response.

Because we may not be able to understand the specific cause of the problem by the description, per the situation, the files may help us better understand and try to fix the issue. Could you prepare and upload sample files are showed correctly and incorrectly that with no confidential/ sensitive data to OneDrive, then share the link here? I'd like to have a test from my side.

Besides, may I know have you checked previous versions of the file? Are the rules of the workbook stored in SharePoint changed by other members?

Best regards,

Madoc

Answer 5

Hi Leah,

Regarding the issue, feel free to post back if you need further assistance.

Best regards,

Madoc