0

How can I comment out parts of a formula in a Google Drive Spreadsheet?

In PHP you can comment a line of code using a # what is the syntax to do the same in a Google Spreadsheet?

I want to use this to temporarily disable a part of a formula rather than write a note / comment.

For instance I have this formula:

=QUERY({
'ALPHA'!A6:X70;
'BETA'!A6:X70;
'CHARLIE'!A6:X69
},"select * where Col1 <>''
order by Col24 DESC")

I would like to be able to comment out the part:

 order by Col24 DESC
  • You can add ' before the formula to transform it into a string (I.E. '=sum(A1:A2) and it'll display =sum(A1:A2) but I'm pretty sure it's impossible to comment inside a formula. Related – pjmg Oct 17 at 9:37
1

Use the concatenation operator & and IF

Example

=QUERY({
'ALPHA'!A6:X70;
'BETA'!A6:X70;
'CHARLIE'!A6:X69
},"select * where Col1 <>''"
&IF(FALSE," order by Col24 DESC",))

Explanation

The formula syntax doesn't include something similar to the PHP # comment, JavaScript // comment, /* */ multiple line comment or HTML <!-- -->, but you could use functions like IF. Another alternative is IFERROR. If the formula returns a number, N could be another alternative, as it returns 0 when it's argument isn't a number.

In this case I suggest the use of & and IF because the QUERY's second argument should be a text value. & concatenates the two operands and returns a text value; by using IF we could set a condition and the value to be returned if the condition is true, in this case order by Col24 DESC (please note the leading space), and another value if the condition is false, in this case I suggest to leave the second argument blank, to return a blank.

You could go even further by using a cell reference for the condition to toggle the inclusion/exclusion of the order clause.

Pro-tip: Use Google Apps Script to document and toggle formulas. A cell note or comment could be used to mark those formulas that have being documented this way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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