Share via

How to Stop Excel Automatically Change Formula to Value Only

Anonymous
2024-04-16T04:34:33+00:00

Need help.

I built power query in my excel. At the same time, I created formulas in Table and normal range to get data from Power Pivot. It works well and get the data I want. After I save the excel and re-open it. I find the formulas in Table and Normal range are gone and changed to plain value without any formula. I need to re-build the formula again. I have no idea where goes wrong.

One thing I remember there is warning box saying the formula content is too rich. I clicked OK and no issue happened before I close the excel. Do you think it may be related to this issue? Do you think it may be the field name in my Power Pivot is too long? or is there some limitation for the formula length? This limitations triggered some protection of excel to automatically remove the formula?

Thanks.

Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2024-04-18T03:22:33+00:00

    Hi Team,

    I think I find the solution and leave the idea here in case someone may have the similar case as mine.

    Firstly, I convert the Table to Normal range.

    Secondly, I convert the date from Format "Date" to "Text" if it has "SUM" function by date. I assume the SUM function cannot recognize whether to include the date value or not if it is Date format. Then to protect the original value, it automatically changes the formula to value only.

    Hope it can help others.

    Thanks.

    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-04-16T06:45:57+00:00

    Could you share a test file without sensitive information to reproduce your issue?

    For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

    Why a sample file is important for troubleshooting. How to do it. - Microsoft Community

    0 comments
  2. Anonymous
    2024-04-16T07:45:26+00:00

    The excel file is too complicated to build a test file. Is there any other way you can help here, like an agent to go through it?

    0 comments
  3. Anonymous
    2024-04-16T10:32:19+00:00

    I‘d suggest you create a support ticket to Microsoft.

    Please contact your admin/IT department create a support ticket via Microsoft 365 Admin Center> Support> New service request. Support team there will have the correct channel and resources to help you investigate more and find what exactly the reason has caused this situation.

    Get support - Microsoft 365 admin | Microsoft Learn

    How can I reproduce your issue? Is there any macro on your workbook?

    You may put your file on Onedrive and check "show changes" to get which action made the changes.

    0 comments