Hide formula result until all relevant cells contain data

  • This is a very simple query to anyone with more experience than me, I am sure!

    I have created a simple spreadsheet which contains a column for a running total. I have entered a formula and used the fill down option so that my boss can just fill in the blanks and the total will appear in the running total column, but this means that the last value for the running total then continues to display down the page even when the other values have not yet been entered. It's a presentation thing as much as anything, but is there any way to hide the values in the running total column (in this case column F) until the cells to which the formula relates (in this case column E) contain data?

    I've tried to be concise - hope this makes sense! I'd be very grateful if anyone can help me with this. Thanks

  • Something like this maybe?
    Paste this in the worksheet object where you would like the columns to be hidden. Specify the range you want to check. For my example, I used E1:E30

  • Richie's formula should work for you nicely.

    Another solution would be to utilize conditional formatting.
    Under Format>Conditional Format you should choose Formula Is then enter =AND(F2=F3,F3>0) click on the Format button and choose white for the font color.


  • Thanks for the advice guys!

    Richie's formula does indeed work - I had a vague memory of having to do something to the font colour too, so it is all coming flooding back!

    Thank you all again!

  • You should also be able to use an if statement in your formula.

    example:put =if(e2="","",f1+e2) in cell f2. Of course your formula for f1 would need to be slightly different as there is no row befor to add to. I used =if(e1="","",sum(e1:e1)) in cell f1

    Col E Col F
    1 1
    2 3

  • Re: Hide formula result until all relevant cells contain data

    Hi there,

    I'm having the exact same problem, I found this thread and I thought my problems were solved but the formula recommended doesn't seem to work for me, it gives an error (I did try changing the cell numbers).

    Basically, I want to make a spreadsheet similar to a bank statement, so one column for credit (C) one for debit (D) and then one for balance (E). Like the OP, I've managed to set it up so that inputting a value in the credit or debit column will change the balance, but I need to have the value in the balance running all the way down the page for that to happen. Ideally, I wanted the balance only to appear when a new value is added to the credit or debit column (always one or the other, never both, clearly).

    I know this is probably a very simple task and I would really appreciate any help at all.


  • Re: Hide formula result until all relevant cells contain data

    Hello Lourenco,

    Welcome to Ozgrid - we're glad to have you here.
    Please take some time to read the Forum Rules.

    Do not post your question in threads started by others - - this is known as thread hijacking.
    Always start a new thread for *YOUR* question. If you find it helpful to clarify your needs you can include a link to this ( or any other ) thread.

    When starting a new thread be sure to give it a search-friendly title that aptly describes your need.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!