Column-based table store the values of each column together. Two properties make them suitable for fast query against a column: data locality and compressible data.
Since the column values are stored together, more of them can be crammed into a data page. Querying the data of a column can process much more data per page load than row-based, whereas a row-based table's data page has all other columns.
The values of a column can be stored in a sorted order, which is highly compressible, enabling cramming even more data into a data page. You can get much more data with each data page loading. E.g. The column Name is stored as sorted along with the associated row id.
Name, (row id)
--------------
Joan, (101)
Joanna, (307)
John, (15)
John, (32)
John, (6)
Johnson, (31)
Johnson, (44)
The duplicate names can be stored as one:
Name, (row id)
--------------
Joan, (101)
Joanna, (307)
John, (15,32,6)
Johnson, (31,44)
Prefix encoding can further compress the sorted data:
Name, (row id)
--------------
Joan, (101)
4+na, (307)
2+hn, (15,32,6)
4+son, (31,44)
Now imagine the query: select count(Name) from Table. Scanning the Name column values only touches 4 records right next to each other in a data page and adds up each record's reference row ids.
Select Name from Table where Name like "John%" would do a binary search down the sorted names, load two records 2+hn and 4+son, and expand them into 5 names.