Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have an excel sheet with few hundred columns, and I want only about 30 of them, each has a column name, is there way by which I can select/filter columns just the way we do for the rows? I would like to do it without macros, as I have never used them before.

I am sorry if it is a very basic question, but I tried to search a lot and all I got was how to select multiple columns (ctrl+click on columns :) ) and filter columns to get specific rows.

e.g. Out of

col1 col2 col3 col4
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...
... ... ... ... ...

I want only

col1 col3
... ...
... ...
... ...
... ...
share|improve this question

migrated from stackoverflow.com Sep 9 '11 at 3:55

This question came from our site for professional and enthusiast programmers.

    
What do you mean "select/filter columns just the way we do for the rows"? Do you mean Data Filtering? So you want to have data filtering not with column headers, but row headers? – Richard Morgan Sep 8 '11 at 16:25

If the columns you want to hide are the same columns every time, you might use Custom Views (View - Custom Views in Excel 2003). Create a view called "normal" with all the columns showing. Then hide the columns you want and create another view called "hidden". Now you can switch between normal and hidden views easily.

If you want to hide columns dynamically, I'm afraid you're stuck with a macro.

For more on custom views, see http://www.dailydoseofexcel.com/archives/2004/09/21/quickly-hiding-columns/

share|improve this answer

Assuming you know which column labels you need, you can use the Name box to quickly select them.

Suppose you want to select columns B, E and H. Enter B:B,E:E,H:H into the Name box, like so (they don't have to be uppercase):

enter image description here

Press Enter, and all the columns you typed will be selected:

enter image description here

You may also record the steps above into a short macro.


Named Ranges + Name Box

You can also make this a bit easier by assigning named ranges to your columns, and then using the Name box to select them when you need to. If your datasheet has unique headers, you can do this quickly:

  1. Highlight your data table.
  2. Press Ctrl + Shift + F3
  3. Check Top Row (only).

To select columns:

  1. Type their names/headers (separated by commas) into the Name box.
  2. Press Enter.

Example:

I created named ranges for the following data sheet using the method I described above. To select the Shape and Opacity columns, I just type shape,opacity into the Name box.

enter image description here

After I press Enter then Ctrl + Space (to make sure entire columns are selected), I get this:

enter image description here

share|improve this answer

Select Non-adjacent Cells in Excel. © Ted French

Ted French Spreadsheets Expert By selecting multiple cells in Excel you can delete data, apply formatting such as borders or shading, or apply other options to large areas of a worksheet all at one time.

When this occurs, it is possible to select non-adjacent cells. Although this can be done solely with the keyboard, it is more easily done by using the keyboard and the mouse. Selecting Non-Adjacent Cells in Excel

Ads Excel Consolidation bwcommunicator.com New patented technology - no more cutting and pasting or emailing Online MS Excel Classes www.pluralsight.com/Free-Trial Advanced & Beginner Classes. Free 10 Day Trial. Sign Up Now! Excel MS Office Excel Lead Tracking Spreadsheet Excel 2010 Tutorial Download Microsoft Excel Click on the first cell you want to select with the mouse pointer to make it the active cell. Press and hold down the Ctrl key on the keyboard. Click on the rest of the cells you want to select Without releasing the Ctrl key. Once all the desired cells are selected, release the Ctrl key. Do Not click anywhere else with the mouse pointer once you release the Ctrl key or you will clear the highlight from the selected cells. If you release the Ctrl key too soon and wish to highlight more cells, simply press and hold down the Ctrl key again and then click on the additional cell(s).

share|improve this answer
1  
Do you have a link/reference about this? Sounds like Ted French's, and looks like a copyright symbol, so I'm not sure if it could be reproduced here – Xen2050 Feb 18 '15 at 18:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.