How can I create a table like this in Excel?

liamcov

I want to create a table like this in an Excel document:

http://en.wikipedia.org/wiki/List_of_tube_stations#Stations

But can't find how to do it. Want it so it can sort long lists in alphabetical or numerical order when you click the headings, not sure if i've made that very clear?

Owain_Moneysaver

Depends on the version of excel you have. You need to make a sort button using some macro code.

Discussion here.

sickofusernames

You mean you want to take data from a website and import into Excel? If not then it's easy to do...

Mk14:37

Do you mean that you want to have 'sort' arrows at the top of each column?

Highlight the top row, click Data > Auto Filter.

LucianH

Open up Visual Basic and add the following code to the Worksheet in question (will be listed under "Microsoft Excel Objects").

This macro runs when you double click on the worksheet. It checks if you've clicked on the first row. If you don't then the macro exits without doing anything.

If you do double click on the first row (and it's not blank) then the macro sets the sort area to all the data in the table and then sorts on the column you've selected.


Option Explicit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim SortRange As Range

If Target.Row <> 1 Then

Cancel = False

ElseIf Target.Cells(1, 1) = "" Then

Cancel = False

Else

Set SortRange = Target.CurrentRegion

SortRange.Sort Key1:=Target, Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Cancel = True

End If

End Sub