Losing formulas after refreshing workbook

%3CLINGO-SUB%20id%3D%22lingo-sub-1603778%22%20slang%3D%22en-US%22%3ERe%3A%20Losing%20formulas%20after%20refreshing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1603778%22%20slang%3D%22en-US%22%3EYou%20cannot%20enter%20formulas%20in%20a%20column%20which%20is%20returned%20by%20the%20query%2C%20only%20in%20columns%20that%20you%20manually%20add%20to%20the%20table.%20But%20be%20aware%20that%20sometimes%20Excel%20fails%20to%20autofill%20all%20rows%20with%20your%20formulas%20after%20a%20refresh.%20%3CBR%20%2F%3EIt%20is%20better%20to%20write%20those%20formulas%20in%20the%20query%20itself%20rather%20than%20using%20Excel%20formulas%2C%20by%20adding%20a%20custom%20column%20in%20the%20Query.%20The%20formula%20syntax%20is%20different%2C%20but%20at%20least%20you'll%20be%20sure%20all%20rows%20get%20the%20calculated%20result.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1604439%22%20slang%3D%22en-US%22%3ERe%3A%20Losing%20formulas%20after%20refreshing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1604439%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3BThanks%20for%20your%20reply.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20that%20too%20by%20creating%20a%20new%20column%20and%20adding%20the%20formulas%20there%2C%20it%20worked%2C%20but%20after%20auto%20refresh%20it%20disappears%20as%20well.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1602909%22%20slang%3D%22en-US%22%3ELosing%20formulas%20after%20refreshing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1602909%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3EI%20have%20a%20workbook%20that%20is%20connected%20to%20an%20external%20database%20(sql%20server).%20When%20I%20type%20in%20formulas%20(nested%20ifs)%2C%20after%20refresh%2C%20I%20loose%20everything.%20I%20have%20tried%20changing%20the%20options%20under%20properties%20but%20nothing%20seems%20to%20be%20working.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1602909%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1607768%22%20slang%3D%22en-US%22%3ERe%3A%20Losing%20formulas%20after%20refreshing%20workbook%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1607768%22%20slang%3D%22en-US%22%3EDid%20you%20make%20sure%20all%20cells%20of%20the%20column%20contain%20the%20same%20formula%20(select%20all%20cells%20in%20column%2C%20edit%20first%20formula%20and%20press%20control%2BEnter%20to%20enter%20in%20all%20cells%20of%20the%20column)%3F%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello,
I have a workbook that is connected to an external database (sql server). When I type in formulas (nested ifs), after refresh, I loose everything. I have tried changing the options under properties but nothing seems to be working.

 

Thanks

3 Replies
Highlighted
You cannot enter formulas in a column which is returned by the query, only in columns that you manually add to the table. But be aware that sometimes Excel fails to autofill all rows with your formulas after a refresh.
It is better to write those formulas in the query itself rather than using Excel formulas, by adding a custom column in the Query. The formula syntax is different, but at least you'll be sure all rows get the calculated result.
Highlighted

@Jan Karel Pieterse Thanks for your reply. 

I tried that too by creating a new column and adding the formulas there, it worked, but after auto refresh it disappears as well. 

Highlighted
Did you make sure all cells of the column contain the same formula (select all cells in column, edit first formula and press control+Enter to enter in all cells of the column)?