Excel/Horizontal Filter


Hi Tom

Is there any facility in Excel to do what I can best describe as a horizontal filter?  I have a spreadsheet with lots of columns (about 250 columns), each column representing a date of the year, ie 4th Nov, 5th Nov, 6th Nov etc etc.  My problem is that I want to delete all the columns which contain dates representing saturdays and sundays.  I have the week days corresponding to each date in row 4, and the date itself in row 5.  My thoughts were if I could somehow filter the columns sat and sun out, I could delete them in one go, rather than scrolling through all the columns to find the appropriate ones and then deleting them.

Hope this makes sense!



no horizontal filter.

I would insert a new row 1.  Say your first date starts in d6 (old d5).  In d1 I would put


then drag this across all the columns in row 1

select row 1.  Hit F5 to get the goto dialog.  Click on the special button.  then click formulas and unclick everything below that except "errors" and click OK.

No all the cells in the weekend columns will be selected.  Go to the home tab and then to the cells section and choose the delete Dropdown - select entire column.   That should do what you want.  

You can now delete the inserted row 1.

(you can use any row to do this if you already have one that is blank , then you can use that).

Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Tom Ogilvy


Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]


Extensive experience.

Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.