I have an Excel spreadsheet with 10 columns.

The cells within the first 2 columns contain text and the remaining 8 cells contain 'blank', 0 or 1.

How can I hide all rows where the 8 columns do not contain any blank cells?

  • Is AutoFilter an option for you? There you can say show only "not blank" values. Guess its only called Filter in >=Office 2007 – Langhard Sep 19 '12 at 8:58
  • I believe (Auto)filter is not an option for this case, since rows need to be filtered using criteria based on more than one column here. – kurp Sep 19 '12 at 9:08

Here is an easy work-around:

  1. Go to the empty column K.
  2. Input the following function: =COUNTIF(C2:K2;"")and copy for entire column.
  3. Blank cells will be counted.
  4. "8" means all columns are empty for a particular row.
  5. Use filter on K column to hide all "8" (to hide empty rows) or mark all numbers except "8" (to display empty rows only).
  • Great workaround - thanks! Please note it's a , instead of a ;. i.e. =COUNTIF(C2:K2,"") – Techboy Sep 19 '12 at 9:32
  • 2
    You're welcome! :) Regarding ';' - well, it depends (just another country and another local settings). My Excel accepts semicolon as a arguments' separator only. But thanks for remark, I'll have in mind in future that there may be a difference here. Greetings from Poland! :) – kurp Sep 19 '12 at 9:36

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.