Is there a way to hide a row or column in excel without using VBA?

Question

I know of several approaches using a macro (VBA) to show/hide columns and rows in Excel, but I cannot figure out or find a way to do this using either a formula or conditional formatting. Of particular interest is Excel 2007 - but I'd be curious to know if someone has managed to do it in any version of Excel.

For those who want background, I have a spread of data with dates across the top and labels down the first column. I would like to specify a date window (on another sheet) as two cells with drop down dates (months) which would then show/hide the appropriate columns on the data sheet.

Answer 1

You probably need a third sheet (or use the second sheet where you're speciyfing the dates), and use HLOOKUPS or MATCH functions.

1) Use a function to dynamically populate the column headings you need, based on your date range. So in B1 you'd put =StartDate

2) cell C1 would be =IF(B1+1<=EndDate,B1+1,"") and copy across a suitable number of columns, based on your assessment of how long a period people are likely to look for

3) Cells B2 down and right would then use your preference of either an HLOOKUP() or OFFSET(MATCH()) combination, in conjunction with an IF to determine whether you want it. e.g. B2 would be =IF(B$1<>"",HLOOKUP(B$1,DataRange,row(B2),false),"") and copy down and across as needed.

However the suggestion above about transposing the data and then using a PivotTable, with the aid of a helper column alongside your data (i.e. IF(AND(date>=StartDate,date<=EndDate),"Show","Hide") and then filter on the "Show" in the page field.

Answer 2

This isn't an answer to your question directly (I strongly doubt there is one), but if you organised your data using a data table, making data go downwards rather than horizontally like a simple OLAP cube, you could probably add a criteria column that checks if the data points are between your two thresholds, and then organise your view in a pivot table.

Combining lists and tables like this is really powerful if you don't like VBA, but VBA really is the solution for this sort of stuff unfortunately.