Hide cell from being computed in the formula

Posted on


I have two cells in the same column adjacent to each other (Q7, Q8). They are used in multiple formulas as range.
1. SUM(Q1:Q10)
2. SUM(E6:N13)-SUM(P6:AJ13)

How do i skip those two cells from being computed in the formula without making any changes to the formula. I want the value in the cell to be displayed but should not be used in computations.


Since you are using the SUM() function, there is one other option. SUM() ignores text values, so you could enter the values in Q7 and Q8 as text. Format the cells as Text and enter the numbers with a leading single quote character. In the screenshot below, Excel shows the green warning triangle for numbers stored as text, but the SUM() function only calculates the real numbers.

enter image description here

That approach will also work if Q7 and Q8 contain formulas that return numbers. You simply wrap a TEXT() formula around the existing formula to format the result as text


Select the desired number format.

You can’t omit these cells from being included in the formula without making changes in the formula.

You can’t have your cake and eat it.

I suggest you change the formula to exclude the cell explicitly, like

  1. SUM(Q1:Q10)-sum(q7,q8)


  1. SUM(E6:N13)-SUM(P6:AJ13)-sum(q7,q8)

If you know that you might want to exclude certain items from the formula, you can prepare for this as follows:

  • in R1 through R10 enter 1
  • use the formula: =SUMPRODUCT((R1:R10)*(Q1:Q10))

enter image description here

Now if you want to exclude Q7 and Q8, just set R7 and R8 to zero.

Leave a Reply

Your email address will not be published. Required fields are marked *