Is there a way by which I can select particular columns from an excel sheet?

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
... ...
... ...
... ...
... ...

3 Answers

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

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):

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

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.

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

I had the similar question and thought out a solution. My situation is there are 500+ of columns and I want to keep only ~ 20 of them.

1. In source sheet, select all cells (Ctrl + A) and copy ( Ctrl+C)
2. In a new sheet, Home Panel -> Clipboard section -> Paste -> Click the "Transpose (T)"or Paste Special (Ctrl+Alt+V) and select transpose. The data table is rotated by 90 degree so that the first row (variable names) becomes the first column.
3. Add a blank row as the first row.
4. Select the first row; Under Data Panel -> Sort & Filter section, click "Filter"
5. The "A1" cell will have a draw-down button allowing you to choose the variables you want to keep.
6. Copy the remaining data and "Transpose" paste them to the final sheet.