Auto sort by clicking on a column heading

cpaavola

Board Regular
Joined
May 13, 2009
Messages
90
Hey all, this is a doozy.

If I have a list of information that I want to allow other users (with little to no Excel experience) quickly sort data by clicking on the head of a column, how would I do that?

For instance, here is a snippet of what is actually a huge list of employees that some key stake holders need to see. I'd like if they could click on the column heading and sort by that column... is it possible WITHOUT going to Data > Sort?

EmployeeUnits soldBonus SalesPackage SalesNew customers
A14533224
B14341264
C14129146
D13842304
E13333184
F12929224
G12640224
H12335243
I11648224
J11444203

<tbody>
</tbody>
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Click the cell Employee, go to your ribbon and hit filter (in the data tab) - should do it automatically.
 
Upvote 0
Here's a macro you can put into the sheet that your data is on. Macros must be enabled to use this and if you are using Excel 2007 or later version, the workbook must be saved with a .xlsm or .xlsb extension. To install the macro: open the workbook, right-click the sheet tab and choose 'View code', then paste the code below into the white space in the VB editor that opens. Close the editor and save the file (with the correct file extension).
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.CurrentRegion.Count = 1 Then Exit Sub
If Target.Row > Target.CurrentRegion.Rows(1).Row Then Exit Sub
With Target.CurrentRegion
    .Sort key1:=Target, order1:=xlDescending, Header:=xlYes
End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,211,772
Messages
6,103,876
Members
447,882
Latest member
LORENA

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top