How to keep cells blank until data entered in

%3CLINGO-SUB%20id%3D%22lingo-sub-1427310%22%20slang%3D%22en-US%22%3EHow%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427310%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20color%3D%22%23993366%22%3E%3CFONT%20color%3D%22%23000000%22%3EWe%20have%20an%20Excel%20worksheet%20set%20up%20with%20formulas%20in%20place.%26nbsp%3B%20Right%20now%2C%20without%20any%20data%20entered%2C%20all%20the%20columns%20and%20rows%20show%20zero's.%26nbsp%3B%20We%20would%20like%20for%20the%20cells%20to%20remain%20blank%20until%20we%20enter%20data%20into%20them.%26nbsp%3B%20Is%20there%20a%20way%20to%20do%20this%3F%26nbsp%3B%20We're%20using%20Microsoft%20Office%2013.%26nbsp%3B%20I'm%20attaching%20the%20document%20for%20reference.%26nbsp%3B%20%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23000000%22%3EThank%20you!%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1427310%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427377%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F683753%22%20target%3D%22_blank%22%3E%40Allie94%3C%2FA%3E%26nbsp%3B%2C%20this%20article%20could%20help%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdisplay-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.office.com%2Fen-us%2Farticle%2Fdisplay-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427403%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427403%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F683753%22%20target%3D%22_blank%22%3E%40Allie94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOne%20way%20is%20to%20apply%20custom%20number%20format%20like%20this%20to%20the%20cells%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20317px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195419i8E8D1C058B64BD24%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EAnother%20way%2C%20if%20zero%20are%20not%20required%20in%20entire%20sheet%2C%20you%20may%20disable%20this%20setting%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20499px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F195420i4E24F695E71F25D4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427531%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427531%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22lia-spoiler-container%22%3E%3CA%20class%3D%22lia-spoiler-link%22%20href%3D%22%23%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3ESpoiler%3C%2FA%3E%3CNOSCRIPT%3E(Highlight%20to%20read)%3C%2FNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-border%22%3E%3CDIV%20class%3D%22lia-spoiler-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3CNOSCRIPT%3E%3CDIV%20class%3D%22lia-spoiler-noscript-container%22%3E%3CDIV%20class%3D%22lia-spoiler-noscript-content%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FNOSCRIPT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CP%3E%26nbsp%3BThank%20you!!%26nbsp%3B%20I'll%20see%20if%20it%20works!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427637%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427637%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20the%20tips%20do%20help%26nbsp%3Bwith%20the%200's%2C%20but%20once%20I%20enter%20a%20%23%20into%20the%20first%20column%2C%20then%20it%20carries%20that%20%23%20all%20the%20way%20down%20the%20sheet.%26nbsp%3B%20We'd%20like%20to%20have%20the%20rows%20be%20blank%20until%20info%20is%20entered%20into%20each%20row.%26nbsp%3B%20It%20looks%20very%20%22busy%22%20to%20have%20the%20quantity%20going%20all%20the%20way%20down%20the%20page%2C%20and%20seems%20unneccesary.%26nbsp%3B%20If%20you%20know%20of%20any%20tips%20to%20help%20with%20that%2C%20I%20would%20really%20appreciate%20it.%26nbsp%3B%20I'm%20trying%20different%20settings%2C%20but%20not%20coming%20up%20with%20anything%20yet.%26nbsp%3B%20Thank%20you%20so%20much%20for%20the%20other%20tips!%26nbsp%3B%20I%20truly%20appreciate%20you%20taking%20the%20time%20to%20try%20to%20help%20me%20out.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427657%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427657%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F636579%22%20target%3D%22_blank%22%3E%40amit_bhola%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%26nbsp%3B%20I'm%20going%20in%20there%20now.%26nbsp%3B%20Much%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1427847%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20keep%20cells%20blank%20until%20data%20entered%20in%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1427847%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F683753%22%20target%3D%22_blank%22%3E%40Allie94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%20you%20may%20modify%20your%20formulas%20a%20bit%20as%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUM(B14-D14%2BH14)%0Aexpand%20to%0A%3DSUM(B14-D14%2BH14)*D14*H14%0A%0A%3DSUM(C14-E14%2BI14)%0Aexpand%20to%0A%3DSUM(C14-E14%2BI14)*E14*I14%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Efor%20all%20cells.%20With%20that%20formulas%20return%20zero%20(which%20is%20not%20shown)%20if%20not%20calculatable%20values%20have%20no%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

We have an Excel worksheet set up with formulas in place.  Right now, without any data entered, all the columns and rows show zero's.  We would like for the cells to remain blank until we enter data into them.  Is there a way to do this?  We're using Microsoft Office 13.  I'm attaching the document for reference. 

 

Thank you!

6 Replies
Highlighted

@Allie94 

One way is to apply custom number format like this to the cells

image.png

Another way, if zero are not required in entire sheet, you may disable this setting

image.png

Highlighted

@Sergei Baklan 

Spoiler
 

 Thank you!!  I'll see if it works!

Highlighted

@Sergei Baklan 

Well, the tips do help with the 0's, but once I enter a # into the first column, then it carries that # all the way down the sheet.  We'd like to have the rows be blank until info is entered into each row.  It looks very "busy" to have the quantity going all the way down the page, and seems unneccesary.  If you know of any tips to help with that, I would really appreciate it.  I'm trying different settings, but not coming up with anything yet.  Thank you so much for the other tips!  I truly appreciate you taking the time to try to help me out. 

Highlighted

@amit_bhola 

Thank you!  I'm going in there now.  Much appreciated!

Highlighted

@Allie94 

As variant you may modify your formulas a bit as

=SUM(B14-D14+H14)
expand to
=SUM(B14-D14+H14)*D14*H14

=SUM(C14-E14+I14)
expand to
=SUM(C14-E14+I14)*E14*I14

for all cells. With that formulas return zero (which is not shown) if not calculatable values have no values.