Sort Me Out

February 4, 2009 10:27 AM

I need to create a database in Excel, with clickable column headers (clicking on them will sort by that column). More inside, of course.

I know Access is better for databases, but I'm creating this for a user group with very limited PC skills. Frankly, getting them to look at Excel scares them a little. The idea of Access brings on the deer-in-the-headlights reaction.

Ideally, I'd like each category (column header) to be clickable. By clicking on that header, it sorts by that column. (Like Google Docs does automatically).

Is there a way to do this without diving into VB?

posted by Rubythursday to computers & internet (7 comments total)

Well, in Excel 2007 it's pretty straightforward. Probably 2003 too, but I don't have that in front of me.

Select the cells (headers+data) you want to include, then in the Data tab, click the Filter button. This will put buttons on each of the column headers. When a user clicks the button, they get a short menu, the first option of which is "Sort A to Z", the second option is "Sort Z to A". There are also some additional options below, but they shouldn't be too confusing.

Hope that helps.
posted by CruiseSavvy at 10:52 AM on February 4

Maybe you should use Access, then? I could see someone getting easily frustrated/alarmed by accidentally clicking a number or one of the formula whatsits and turning their nice cell of text into number signs and question marks and sends up an error box. Using Access would limit the amount of things that could go wrong since it's built to do the one thing. There's a reason the Microsoft Office suite is divided into it's zillion different programs. Sorry, can't help much if you do decide to stick with Excel.
posted by phunniemee at 10:59 AM on February 4

Excel is not for creating databases. End of story.

You could try adding a Data->Filter to your existing sheet, this might ease the sorting of a large spreadsheet. But as mentioned by phunniemee, not having it in a proper database leaves your data subject to your admittedly inexperienced user base, which sounds like a disaster.

The Data->Filter will put drop down filters at the top of every column.
posted by teabag at 12:08 PM on February 4

You want to convert your range to a table. That's what puts little arrows (Sort asc or sort desc) on your column headers.

In 2007:

Make sure one of the cells in your range of data is the active cell. In the Home tab, find the icn that says "Format as Table". Choose a table style. Verify the range Excel preloads in the text box is correct, and the checkbox for headers is clicked. Done.
posted by SuperSquirrel at 12:40 PM on February 4

I agree with phunniemee. Access can be extremely user friendly, especially if you are using Forms. My end-users went from extremely frustration with Excel to using Access for their day-to-day operation with extremely ease.

That said, if you want to stick with Excel, Filter button is your best option.
posted by vocpanda at 12:42 PM on February 4

"icn" = "icon". Sorry.
posted by SuperSquirrel at 12:43 PM on February 4

I know you said you don't want to get in to VB, but I make files with buttons that sort columns using just a tiny bit of VB. In case you are avoiding it because you think it will be too complicated, here's the VB that sorts my columns:

Rows("1:2000").Select
Range("A2000").Activate
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("C2").Select

In the ~seven years I've been distributing these files, I don't think I've ever had a sorting complaint. We even put multiple buttons on a single file. Of course, users have to enable macros when they open the file. That alone might prevent you from using this solution.
posted by soelo at 12:57 PM on February 5