Did You Know :: How to Hide Rows With No Data

Posted: November 11, 2014 by MaxFraudy in Excel
Tags: , , , , ,

UntitledWe have earlier discussed how to hide errors in a worksheet in this post. Today I’m going address another similar problem that is very commonly faced. There are various ways to tackle it and there is no right or wrong about it. I’m sharing what I prefer to use (because I find it very simple and easy to implement).

Suppose I have a data table in my dashboard that is referencing to another range. My table has 10 rows (A2:D11) that reference range Z2:AC11. As long as there is data in Z2:AC11, everything is fine.

TableNow suppose that I have data only till row 4. In this case, my table shows 0’s which looks ugly and I’d rather prefer those cells to be blank. How do I solve this problem?

table2

The answer is a combination of custom format and conditional formatting. I select my range A2:D11 and then add a conditional formatting rule to it (by going to Home tab -> Styles group -> Conditional Formatting -> New Rule -> Use a formula to determine which cells to format). I use this formula:

=$A2=0

New Rule

Then, I click on Format -> Number tab -> Custom and use this code:

#,###;-#,###;;@

Now, click OK and you’re done.

How this works is that the formula checks if there is 0 in column A. Whenever it encounters a 0 in column A, the conditional formatting applies the custom format to the entire row. If you look at the custom format code closely, you’ll realize that what I’m essentially doing is that I’m forcing Excel not to display 0’s.

As simple as that. There may be better ways to handle this. If you have one, I’m eager to know.

Advertisements

Share your thoughts/feedback

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s