I have a spreadsheet in which each cell contains text or is blank. I want to apply conditional formatting on it so that iff a cell's text matches that of the cell immediately above it, then the cell appears to be blank (or gets merged with the immediately above cell). However, each cell is color coded according to the text which it contains, so I cannot actually clear it - it just needs to look that way. (I know that I can format it with ";;;" in order to appear blank, and that may be part of it)

Complicating issues:

  • I do not know in advance what the text in each cell will be. I want all of the formatting to be applied automatically once the cells are filled. Someone else is going to fill in the cells in an unknown way; I want them to be able to do this without doing anything else; there will be no copying the cells or anything. (This person is not technologically capable, so I need to be able to make all of this stuff happen automatically in advance. They can type words, but that is it.)
  • I do not want any dependence on any cell except the one immediately above the cell in question.
  • Some columns may have text which repeats wrt the cells, but with
    breaks (different texts) inbetween; I want to blank out a cell only
    when there is a repeat value between adjacent cells when reading down the column - and the blanking out should apply only to the
    later terms in a series of repeats, not the first one. For example,
    if the entries in a column are of the form (A, B, A, A), then they
    should appear of form (A, B, A, ) according to displayed text (but
    the coloring should have all the "A"s one color (including the cell
    in the fourth row/entry) and the "B" a different color).

Additionally, I want to end up counting how many apparently non-empty entries there are in each column after all of this.

  • You can't make a cell truly blank using conditional formatting. That would mean the contents of the cell would have to be removed. You can use conditional formatting to change the text color to be the same as the background color. Then it will appear to be blank. It sounds like you already have a formatting rule to set the background color of the cells. (?) If so, you would need to add a second rule to specify the text color. If you want to go down this path, comment back here and I'll try to put together a solution. – Bandersnatch Jan 28 '18 at 16:58
  • I do not want the cell to be truly blank, just to appear blank. And I think that I can do that portion of it. The other mechanics using the conditional formatting method (or others) elude me, however. – user173897 Jan 30 '18 at 3:52

I figured out most of what I want.

Using Conditional Formatting:

  1. Select rule type "Format only cells that contain".

  2. The first parameter is "Cell Value".

  3. The second parameter is "equal to".

  4. The third parameter is "=[cell]", where the cell immediately above the current (to-be-formatted) cell is selected. Make sure to remove the absolute reference "$" symbols.

  5. Formatting is as follows: Under "Number", select "Custom". Then type ";;;" (over the word "General"); this makes the cell appear blank (but it actually has a value, so all other formatting based on that value, such as background color, nonetheless applies).

  6. Done. Apply/"ok".

Apply this rule (in addition to others) to any cell which you want to satisfy the aforementioned goals. Note, though, that when the cell value updates, you may need to scroll away from it and then back to it in order for the formatting to update too.

I am not yet sure how to count all apparently non-empty cells in a column.

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.