Home

Prevent cell formulas which evaluate to blank “” from displaying as 0 when copy/pasted

%3CLINGO-SUB%20id%3D%22lingo-sub-783344%22%20slang%3D%22en-US%22%3EPrevent%20cell%20formulas%20which%20evaluate%20to%20blank%20%E2%80%9C%E2%80%9D%20from%20displaying%20as%200%20when%20copy%2Fpasted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783344%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Experts%3C%2FP%3E%3CP%3Ei%20have%20several%20cell%20formulas%20which%20use%20if%20functions%20and%20return%20%E2%80%9C%E2%80%9D.%20The%20problem%20is%2C%20when%20I%20copy%20the%20data%20and%20paste%20as%20values%20into%20a%20new%20workbook%2C%20the%20blank%20cells%20appear%20as%200.%20Ideally%2C%20these%20would%20just%20appear%20blank%2Fempty.%20Is%20there%20a%20way%20to%20prevent%20this%3F%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20it%20also%20occurs%20with%20cell%20references.%20For%20example%3A%3C%2FP%3E%3CP%3Esheet1%20has%20name%20in%20column%20A%2C%20address%20line%201%20in%20column%20B%2C%20address%20line%202%20in%20column%20C%3C%2FP%3E%3CP%3Esheet%202%20has%20cell%20references%20to%20display%20the%20data%20from%20sheet%201%20in%20different%20columns%3C%2FP%3E%3CP%3Esuch%20as%20%3DSheet1!A2%2C%20%3DSheet1!B2%2C%20etc.%3C%2FP%3E%3CP%3Eif%20address%20line%202%20is%20blank%2C%20it%20will%20display%200%20when%20copied%20into%20a%20new%20workbook.%3C%2FP%3E%3CP%3Eis%20there%20a%20way%20around%20this%3F%20I%20don%E2%80%99t%20want%20to%20simply%20hide%20the%20zeros.%20I%E2%80%99d%20like%20blank%20cells%20to%20remain%20blank.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-783344%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-783530%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20cell%20formulas%20which%20evaluate%20to%20blank%20%E2%80%9C%E2%80%9D%20from%20displaying%20as%200%20when%20copy%2Fpasted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-783530%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F385538%22%20target%3D%22_blank%22%3E%40Jim75204%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWrap%20your%20formula%20with%20IF%2C%20like%20this%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DIF(Sheet1!A2%3D%22%22%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%22%22%2C%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3ESheet1!A2)%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-821643%22%20slang%3D%22en-US%22%3ERe%3A%20Prevent%20cell%20formulas%20which%20evaluate%20to%20blank%20%E2%80%9C%E2%80%9D%20from%20displaying%20as%200%20when%20copy%2Fpasted%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-821643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Jim75204
New Contributor

Hi Experts

i have several cell formulas which use if functions and return “”. The problem is, when I copy the data and paste as values into a new workbook, the blank cells appear as 0. Ideally, these would just appear blank/empty. Is there a way to prevent this?  

 

It it also occurs with cell references. For example:

sheet1 has name in column A, address line 1 in column B, address line 2 in column C

sheet 2 has cell references to display the data from sheet 1 in different columns

such as =Sheet1!A2, =Sheet1!B2, etc.

if address line 2 is blank, it will display 0 when copied into a new workbook.

is there a way around this? I don’t want to simply hide the zeros. I’d like blank cells to remain blank.

2 Replies

@Jim75204 

Wrap your formula with IF, like this: 

=IF(Sheet1!A2="",

"",

Sheet1!A2)