Excel functions · Tips and tricks

Missing Pieces

The two criticisms we hear most about XLstatements (aside from it being too inexpensive) are the lack of (FRx or MR-like) reusable “building blocks”and the inability to hide rows with no data. I’ve tackled at least part of the first cavil in a previous post (and plan to address more in a future post), but today I will address the second issue.

We wanted to include a way to automagically hide rows with “all zeros,” and could have easily done so if the rows actually had just zeros. The problem is that some columns of these rows will have values and any rows that are blank are probably there for a reason, e.g., to break up a report. The rows one wants hidden are not all zeros: they have account ranges, descriptions, etc. Because we designed XLstatements to work as part of Excel, and not as a different program integrated with Excel (like some other reporting applications), there’s no guaranteed way for our program to know which columns should be defined as “all zero.” You can do this yourself, though, by adding a filter to hide those rows.

balsht

Column A defines what accounts make up the rows’ data, and might well be hidden on the published worksheet. Column B is the description for each row. The “all zeros” rows will probably have data in those columns, and so are not actually “all zeros” — its the remaining columns that we mean. In this example that would be rows 19, 20, 22, 23, etc. Even though rows 31 and 32 also have no data, we don’t want them to be left off. So to define what we consider an “all zero” row, it’s one where the values in columns C, D, and E are zeros but not when they are blank. We want to know which rows to display, and there are many ways to define this as an Excel formula, some of them more compact but abstruse, but here’s one way that is relatively straightforward:

=COUNTIF(C8:E8,"<>0")<>0

The COUNTIF function checks every value in a range against a criterion, “<>0” (not equal to zero) in this case, and adds 1 for each value meeting that test. Boolean algebra in Excel formulas treats zeros as FALSE and non-zeros as TRUE (although TRUE itself evaluates to one). This formula will evaluate to TRUE if any of the relevant columns has a value other than zero, and FALSE if they all evaluate to zero. Perhaps more understandable is this formula, which is equivalent but longer:

=IF(COUNTIF(C8:E8,"<>0")=0,FALSE,TRUE)

Here I used an IF function rather than the <> with boolean algebra (where zeros are false and nonzeros are true) to evaluate the result of the COUNTIF.

Let’s enter that formula in cell H8 and copy it down our report:

nozero

See how that formula evaluates to TRUE for our non-zero rows and FALSE for the “all zero” rows — just what we need to identify the rows to display. Note that I added a header of “NonZero    ” to the column (yes, I added a few trailing spaces so the text won’t get hidden by the filter drop-down arrow we’re about to add) .

Select that range including the header cell (I use Shift,End,↓) and click on Filter:

addfilter

Use the drop-down that the filter created and uncheck FALSE:

choosenonzeros

Et voilà! We now have the “all zeros” rows hidden:

filtered

Note that a filter will not automatically adjust when values change (e.g., the workbook recalculates when you change the period). This means you will need to use its drop-down to refilter whenever the data is refreshed. For this reason, it’s not a good idea to hide the filter column, but you can place it outside of the print area.

Leave a Reply

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s