11

I have a Google Spreadsheet that I enter in new values everyday and want to have the equation run down the entire column so I don't have to drag the previous day's box down to copy the info.

It would work fine to drag the box all the way to the bottom of the sheet, but then the cells get filled with a 0.

How can I put the equation in the cells without having them give me a value right away? i.e. only have them show a value when the rest of that row has info in it.

5 Answers 5

15

From the menu:

  1. Format > Number > More Formats > Custom Number Format
  2. Type in #,##0_);"("#,##0")";"-"_)
3
  • 3
    The minimum: #;-#; Yields the number unchanged if positive, the number unchanged if negative (but prefixed with a hypen), and an empty string if blank.
    – may
    Feb 26, 2016 at 18:38
  • This won't help if you're trying to not have those cells show up in a graph though; use an if/ifblank as described elsewhere.
    – may
    Feb 26, 2016 at 18:47
  • 2
    Cool! Example with 2 decimals: #,##0.00_);(#,##0.00);"-"_)
    – tokland
    Nov 25, 2016 at 13:21
7

I would wrap your formula in an if statement that results in "" if the row is blank. Then you can copy all the way down. Like this:

=if(A1="","",your_formula_here)
1
  • I've been told this si the way to go if you want to exclude the zero results and not just hide them - if you were taking an average or something. Mar 10, 2019 at 16:25
3

You don't need to fill a formula to the whole column. Use array formula instead and remove the need to drag all the way down the sheet

For example if you want cells in column D contain the product of cells in B and C, and column E contains sum of B and C, instead of putting D1 = B1*C1 and E1 = B1 + C1 and drag down you put this in D1 and E1 respectively

=ARRAYFORMULA(B:B*C:C)
=ARRAYFORMULA(B:B + C:C)

Then the formula will be automatically applied to the whole column without dragging anything. X:X refers to column X. If you want to include only from row 3 to the end use X3:X

That will apply the formula to the whole column and hence show 0s on the rows that has no data. So now to not show anything in the empty rows, i.e calculate only rows that has data in column B (except the header row) and hide the 0s below like you want, use this

=ARRAYFORMULA(IF(B2:B<>0, B2:B + C2:C, ""))

In offline apps like MS Office and LibreOffice to use array formula you'll need to press Ctrl+Shift+Enter instead of ARRAYFORMULA function


If you want to hide 0s inside the table then you can use a conditional formatting

  • Select the cells you want to remove 0s
  • Select menu Format > Conditional formatting > add the condition "is equal to" and enter 0 in the textbox
  • In formatting style change the text color to white or none
  • Press Done

Now every cell that is equal to 0 will be blank

2

here's a useful reference:

Review guidelines for customizing a number format

It's a link to support.office.com's guidelines for Excel number formatting - broken down into sections on how to include text, decimals, spaces, colors, currency types, date and time formats, etc...

0
1

If your result depends on more than one cell, you can prevent anything from appearing until all necessary reference cells are populated:

=IF(OR(ISBLANK(A1),ISBLANK(B1),ISBLANK(C1)),,your_formula_here(A1,B1,C1))

If you have some optional cells (e.g. C1 here), use AND():

=IF(AND(OR(ISBLANK(A1),ISBLANK(B1)),ISBLANK(C1)),,your_formula_here(A1,B1,C1))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.