Spreadsheet sharing confusing formatting

Question

Hi,

I have an excel sheet I use for work. It's just a table with conditional formatting, mainly. I want to allow colleagues to work from it simultaneously, and have found the only way of doing this is by using the online version of Excel(?) rather than the desktop app, because it just locks the sheet if I open it in the app.

The issue is, when we moved to the online version, strange things started to happen with the formatting. Consistently the table is failing to spill automatically, and '=and' functions are not working in conditional formatting.

Has anyone else come across this? Is there anything I can do to resolve it, other than avoiding '=and' functions and manually expanding the fields where it fails to spill?

Answer 1

Hi Caelanitsme!

Greetings from the Microsoft Community!

We have not been able to get a hold of you and we will have to proceed with the archiving of this thread.

We know that your inquiry might not be resolved yet, but you are very important to us and that is why we want to invite you to please reach us again if any questions or issues arise. You can easily do so by posting a new question/query here in the Community.

If any additional assistance is needed, we will be happy to jump in and take a look.

Microsoft aims to give you the support you need and help you get the most out of your Microsoft devices, software, and services, so we want to share the following support resources where you can get helpful troubleshooting articles, tips, solutions, videos and more.

• Submit/create new thread in Microsoft Community using this link for new queries: https://answers.microsoft.com/en-us/newthread
• Office Self-Help Support site via www.Office.com/Support
• Learn how to use the new Office on our Microsoft 365 Training site: https://support.microsoft.com/en-us/Training
• Get the latest, know the newest and most improved Office features in Microsoft 365 @ https://support.microsoft.com/en-us/office/what-s-new-in-microsoft-365-95c8d81d-08ba-42c1-914f-bca4603e1426
• For users with active M365 subscription, you can get real-time ‘Phone or Chat Support’ with Microsoft Global English Support at

http://Support.Microsoft.com/en-us/contactus

Thanks again for choosing Microsoft! We’re here when you need us.

Sincerely,

Kathy A.

Consumer Office Forum Moderator

Microsoft Community

Answer 2

Hi Caelanitsme!

We would like to follow-up if you are still having issues with Excel document sharing of documents.

We want you to know that it is important for us it help you get your query answered/resolved.

We will wait for your response in the next 24 hrs.

Please answer back to this thread if you are still having the same problem and need further support.

Also, please allow 24-48 hours for us to get back to you.

Sincerely,

Kathy A.

Consumer M365 Forum Moderator

Microsoft Community

Answer 3

Thanks for the update and sorry for the confusion, Caelanitsme.

Please do update us about the test that you perform. If you haven't perform it yet, you may try to follow the steps below.

In this kind of scenario, the best thing you can do here is to create test Workbook in Excel Online. Copy the whole content of a specific Sheet or specific table with conditional formatting and paste it over to the test Workbook. You may add the "=and" function on the Test Workbook itself.

**Note: Please be mindful of the formula specially those that will link to another workbook or sheet as it may give you error.

We are eager to hear your response.

Sincerely,

Kathy A.

Consumer M365 Forum Moderator

Microsoft Community

Answer 4

Hi, thanks for your help.

I am not referring to a spill error. A spill error doesn't appear, it is just that the conditional formatting does not always automatically expand to new rows as they are created. Maybe I'm causing confusion by using the language incorrectly. I have conditional formatting applying to all rows on the table. The problem is that occasionally, when new rows of data are added (and the table automatically accommodates them as we would expect), the rows that the conditional formatting applies to sometimes do not automatically expand with it.

The '=and' issue is a separate issue, where I have conditional formatting with an '=and' clause, it simply is not functioning at all in the online version of excel.

I do not know if it would apply to more than one excel sheet as I am only running 1. I could try creating a second to test if it does or not...

Answer 5

Hi Caelanitsme,

Welcome to Microsoft Office Community Forum. Thank you for sharing your concern with us today and we hope that all is well.

We understand that using Excel Online is much efficient in doing your work well and what hinders you and your colleagues to that is the formatting of the function "=and" in cell that has conditional formatting. In this part, "it just locks the sheet if I open it in the app." We could agree that this might be possible, as this will depend on the permission your Work IT/Admin has set to you or your colleague's account. Let’s us check on this together and sort this out. Rest assured, we will do anything in our capability to assist you with your concern.

• May we know if we have the same thing in mind? In this part, "Consistently the table is failing to spill automatically, and '=and' functions are not working in conditional formatting." Are you referring to Spill Error? If you are, please check this support article and let us know if this helps. #SPILL! error - Table formula.
• Is this happening to a specific file only or to all Excel documents that has table in conditional formatting and formula "=and" function?

We look forward to your response. If you have other clarifications about this matter, you can get back to us by replying to this post.

Sincerely,

Kathy A.

Consumer M365 Forum Moderator

Microsoft Community