Excel Conditional Hiding Without VBA
By Gary Byrne
Excel macros can do just about anything. But what about those times when you can’t use macros?
http://www.eggheadcafe.com/fileupload/411971851_Excel_Conditional_Hiding.zip
Suppose you have a range of cells that contain data or formula results, and some of those cells are blank, zero, or null. You’ll probably want to automatically parse the results, and show only the non-blank ones. This can easily be done with a macro that filters the list, sorts it, or just deletes the empty rows.
But, what if you are using the Excel file as a back-end calc engine for a .Net application, and connecting to it with an ActiveX control such as SpreadsheetGear, which does not support VBA? Or, maybe you want to avoid using macros because of the sometimes annoying virus scans that they prompt.
In this case, some creative formulas are necessary…
The attached file offers a solution. The yellow cells in column E are the range of raw values, the blue cells in column F are the parsed results, and columns B through D contain the formulas that accomplish the task. Try deleting some of the items in column E, or add new ones, and watch what happens in column F.
| |
B |
C |
D |
E |
F |
| 2 |
Capture
the Row # |
if cell in
Column E is not
blank, return Row # |
Find Next
Nonblank row |
Raw
Data with Blanks |
End
Result |
| 3 |
3 |
3 |
3 |
A |
A |
| 4 |
4 |
4 |
4 |
B |
B |
| 5 |
5 |
0 |
6 |
|
D |
| 6 |
6 |
6 |
9 |
D |
G |
| 7 |
7 |
0 |
10 |
|
H |
| 8 |
8 |
0 |
12 |
|
J |
| 9 |
9 |
9 |
14 |
G |
L |
| 10 |
10 |
10 |
#N/A |
H |
|
| 11 |
11 |
0 |
#N/A |
|
|
| 12 |
12 |
12 |
#N/A |
J |
|
| 13 |
13 |
0 |
#N/A |
|
|
| 14 |
14 |
14 |
#N/A |
L |
|
It sometimes requires more than one set of formulas to create solutions in Excel – in this case, it took four columns’ worth. When dealing with complex problems, you will quickly find that it is a good approach to separate your operations like this, and label them clearly. Even in cases when one long formula would do it, it will help other users to understand what is going on (and avoid scratching your own head when you have to come back to the file after a few weeks). These columns, of course, can always be hidden so that the user only sees the raw data and results.
Note: If you examine the individual formulas, you’ll notice that the formula in D2 appears to have curly braces around it.
{=ROW(INDEX(E2:E13,MATCH(TRUE,LEN(E2:E13)<>0,0),1))}
This is an Array Formula, one of Excel’s most powerful features. You do not type the braces – Excel adds them, to identify that this cell contains an Array Formula. However, you must hold down Ctrl and Shift as you hit Enter when you enter this type of formula in a cell.
Also note that the results are not sorted – perhaps we’ll solve that in a future article.
This is one example of how creative use of formulas can reduce or even eliminate the need for programming.
Popularity (2965 Views)
| Biography - Gary Byrne |
I come from a varied background of financial reporting and data analysis, having worked for many of the top mutual fund and investment companies in Boston before heading south for warmer climes. I've been a spreadsheet buff since the early days of using the DOS version of Lotus 1-2-3 in college (booted up from a nice big 5 1/4" floppy). Through the years I've gained a great deal of expertise with Excel, my weapon of choice, along with it's built-in automation companion VBA. |
Article Discussion: Conditional hiding of blank cells in Excel - without VBA
Gary Byrne posted at Monday, September 21, 2009 9:08 PM
Nice Post!
Andy W replied
to Gary Byrne at Thursday, October 08, 2009 7:33 AM
Hi Gary,
That's some great stuff. Array formulas are a great albeit underused feature in Excel. It's awesome that you are giving this powerful tool some exposure. For some more information on array formulas in Excel check out http://office.microsoft.com/en-us/excel/HA102284581033.aspx?pid=CH100648411033
The Office community on Facebook could really benefit from your expertise. Check it out at http://www.facebook.com/office
Cheers,
Andy
MSFT Office Outreach Team
RE: Nice post!
Gary Byrne replied
to Andy W at Thursday, October 08, 2009 7:33 AM
Thanks Andy! Array formulas are definitely one of my favorites... I discovered them back in 1996 and been using them ever since. I will check out the Office community too.
Gary
RE: Nice Post!
Andy W replied
to Gary Byrne at Thursday, October 08, 2009 7:33 AM
Thanks Gary. Keep up the great posts!
Andy
MSFT Office Outreach Team
example spreadsheet??
I use array formulas as well, and would like to study this example, but where is the link to the attached file?
RE: Article Discussion: Conditional hiding of blank cells in Excel - without VBA
Jonathan VH replied
to Gary Byrne at Thursday, October 08, 2009 7:33 AM
I can't find any link to an "attached file," but I assumed from the title that this article was going to be about data filtering. From your narrative, why not just highlight the range and then choose Home (or Data), Sort & Filter, Filter and then uncheck "Blanks" in the range's dropdown list in Excel 2007? If you want to hide rows with both blanks and zeros, choose Number Filters|Custom Filter and then enter the two criteria "does not equal" with a blank, and "does not equal" with 0.
That would be Data|Filter, AutoFilter and choose "(Nonblanks)" or "(Custom...)" from the dropdown list in earlier versions of Excel.
For more complex filtering, there is also an Advanced Filter (on the Data tab, Sort & Filter group in Exel 2007), where one creates a criteria range like the data and list functions use.
RE: example spreadsheet??
Gary Byrne replied
to Jaap van der Sijp at Thursday, October 08, 2009 7:33 AM
Hi Jaap, I think the file upload must have failed the first time, I am trying again...
RE: Article Discussion: Conditional hiding of blank cells in Excel - without VBA
Gary Byrne replied
to Jonathan VH at Thursday, October 08, 2009 7:33 AM
Hi Jonathan - the file link should be there now in the article.
The main reason for using all these formulas instead of filtering, is for situations when user interaction and/or macros are not possible. I encountered this situation a few years ago: there was a .Net app that used Excel as a back-end engine, where user input from a web page would be fed to an Exce file sitting on a server. The results were then fed back to the web page.
The data interaction was through a control called SpreadsheetGear, which suppresses any VBA. Therefore no macros could be run, and there was no "user" to run an auto filter. So, to prevent blank rows from being displayed in the results, I came up with this. It's basically a way of filtering without using a filter – it could be used to suppress any result I suppose, not just blank cells.
RE: RE: Article Discussion: Conditional hiding of blank cells in Excel - without VBA
Jonathan VH replied
to Gary Byrne at Thursday, October 08, 2009 7:33 AM