Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

(80% Discount Ends Soon!)

How to Sort Data in Excel

I have the following macro that is not working:

ActiveSheet.Unprotect Password:="test"
Selection.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
ActiveSheet.Protect Password:="test"

My problem is that this macro works perfectly fine for me, however, when I
e-mail it to one of our subsidiary companies to use they get a run time error
and it asks them to debug. When they debug, it highlights the line beginning
with: Selection.Sort Key1:=Range("B3"). Any help on fixing this issue would
be greatly appreciated as I am lost. Thanks.

How do you sort a column of numbers with hyphens?
Example #####-###-####.
I want to sort by the 3 middle numbers

I have a data base in Excel on A Mac with one column listing dates.
I need to sort the data into date order but some of the date entries appear
not to be treated as dates.
The format tab indicates that all the entries ARE dates, but if I do a
DATEVALUE() on the range, some convert to numbers but others to "VALUE".
Even if I re-enter the dates manually, they still do not sort porperly.
Anyone able to help please?

How do I sort a range, column or rows, in a simple reverse order? 212, 456,
125, 345, becomes 345, 125, 456, 212.

I need to sort dates before 1900 in proper order -- ones after 1900 are all
fine but any dates like 03/29/1865 will always sort by month and day but
NEVER the year except in the month area. Don't know if I am making sense --
but will be like:
Judy M.

I need to insert numbers out of order in columns, how can I sort them into
correct order in columns.

I would like to know if there is a way to do an alphanumeric sort on some
data. I have item numbers like:

A current sort returns

Any Ideas?
Ray Rohde

I have a sizable worksheet (6,000 rows by 10 columns), which includes
names, addresses, and zip codes. When I try to sort on zip codes, the
worksheet sorts in two sections vertically, in ascending order. I
believe that is because some of the zip codes are formatted as values
and others are formatted as text. Even so, I have tried to format the
zip code column as text and it has not helped. So I'm wondering what I
need to change in the zip code cell formatting to make the proper sort
possible. Many thanks for suggestions.


I have an excel sheet that is not very large. It starts at A1 and goes to Q-112.

Row 1-3 are the Header Rows... The date is always in Column A... I need to sort by column A so that it keep things in chronological order. (Data is not always entered in chronological order)...

The problem is that when I get in cell A4 and click the <Sort> button, it only sorts A5-O112...

I have no idea why row A4 is not sorting or the other two columns P & Q??? Any help would be much appreciated!

Excel 2002- SP3


I have a spreadsheet with first and last names in one cell. I would like to
sort by last names. Is there any way to sort by the second word in a cell or
a quick way to divide the cell by first and last names, without cut and paste?

I have a column of numbers that were imported from a web site and they won't
sort correctly. I have tried reformatting the cells as general numbers but
that doesn't change anything. I discovered that numbers below 100 have a
space or invisible data in front of the number. If I remove it, the number
then shifts to right alignment in the cell and will sort as a number. Is
there any way I can remove these spaces from all numbers at once instead of
going down the column row by row?

I have a range of cells A1:a10, say, which obtain data from another source.
Some of those cells a3, a6:a8, say, under certain conditions, will be blank.
How do I reorder this range such that the cells containing information are
listed together, removing the blanks? I want to do this using a formula,
rather than filter or sort, as the data, and hence the blank cells, will
change, and I want to perform analysis on the cells containing data.


Can someone please help? I've got quite a lot of data that I want to sort
by the persons date of birth, but because I have cells with formula in it
(this works out the persons age) the sort function is changing the formulas
so the formulas no longer work becuase it changes the cell where it is
getting the data from. Does anyone have any ideas how this could be fixed??


I am working on a spreadsheet in Excel (Office XP). I would like to sort the
rows in numerical order but on of the columns is a list of hyerlinks. When I
run the sort the Hyperlink descriptions move them, but the links do not work
properly (they go to the wrong sheets).

I have a spreadsheet with columns A:G and unlimited rows.
Is there any way to automatically sort the whole sheet in alphabetical
order by, let say column G?
The problem is that users of this sheet enter data in the rows and I
need it to be sorted as I mentioned above but many of them don't know
much Excel and can't do the sort from the menu so I was thinking maybe
some kind of macro could do it but I don't know how to write it.
I should mention that I already have macro on that sheet that sets up
printing area, also with ctrl+D it deletes the current row and moves
everything up so if I add another one....
I just don't know how to do it so I need some help, please


I have a large spread sheet that I like to sort alot of different ways using
the sort buttons on the tool bar. I know how to use the Data - Sort function
and selecting header row or not. I want Excel to remember I have a Header Row
when I use the toolbar buttons. It used to remember, but somehow it doesn't
anymore? How can I make this always work?

Below you can find an example of my worksheet:
DC 00 03 20
FE 06 55 20
BD 09 29 40
CT 08 41 70
GT 09 92 80
In this example, you see the sort as I want it (right-to-left and
top-to-bottom). But how can I let Excel do this sort? Now I'm first sorting
all the numbers by hand.

I have a workbook with 50 sheets (1 for each employee).

How can I get Excel to sort these into alphabetical order ?

(From kyeoward@hotmail.com)

Two identical pivot tables referencing the same source file. However about
10% of the data is sorted differently i.e. the sort order varies. However,
the sum and count totals are identical. Why the variance in the sort order?
This can lead to big errors in the associated report, where the sort order is

I want to add information to a worksheet and then sort the data into
alphabetical order all using a macro.

Can not seem to be able to get this function to work.

Can it be done? If so how?

Morning all,

Can someone tell me if Excel 2000 has a maximum sort limit in terms of
number of rows? The reason I ask is that the spreadsheet we are currently
using is 17000 or so rows and when it is sorted there is still a large number
of rows that appear to be unsorted.

Thanks in advance for any assistance

Hey all,

I have a rather large spreadsheet in which column C is ID's. What I would like to be able to do is sort the spreadsheet numerically by frequency of occurrence of ID in column C. For example if ID 90765 only occurs once it should be the first one in the column. Hopefully I have explained my problem good enough. Any help will be greatly appreciated...

aka "bowfever"

I need to sort products by the horsepower. Horsepower can be a fraction,
decimal or whole number. For example, I need 1/100 to come before 1/10,
1/125 to come before 1/12, 1/10 to come before 1.0, 3/4 to come before

Hi, all!
I need macro that sort selected row and then jump to next row and so on
to the end of Sheet. I recorded this macro:

Sub Makro1()
Selection.Sort Key1:=Range("E2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=5, MatchCase:=False, Orientation:=xlLeftToRight
End Sub

This one sorts only one row, how can i solve my problem?
Thanks for any help

i have a worksheet that i need to sort A-Z. I have sorted it in the past but
now it will not sort. There are labels at the top of the worksheet but when
i click Data-->sort, the window that comes up shows "column A" instead of
"Name". Also the labels end up down in the worksheet as they are being
sorted also. there are no empty rows or columns.

I have three rows at the top of my Excel spreadsheet. One row is the column
names; the other two rows contain data used in formulae elsewhere in the
spreadsheet. Is there any way to do it without first selecting the data? I've
found this method is very prone to error.

How do I sort the data without sorting in the three header rows? Designating
"header row" in the sort dialogue only identifies one row.

I have a need to sort, a column, with cells that contain both
letters/characters and numbers. When the cell has a combination of
characters and numbers, I would like Excel to ignor any character that
is NOT a number and then sort the cells.

Example of data


The above example 'shows' the resulting sort that I am looking for. I
need to keep the data displayed with the extra characters.

Any help out there?

I was given a large spreadsheet (737 rows and 45 columns) to clean up before
transferring into a database file. Certain cells text, others numbers,
emails, etc. and some are color coded to identify a specific distribution

Whenever I try to sort the data, the following message shows up and it won't
allow me to sort. "The operation requires the merged cells to be identically

I have spent the better part of a day trying to find the merged cells, but
have failed. Is there some way to find merged cells with the sheet without
clicking on every cell individually? Is there some other way to accomplish
the task for sorting? Thank you.

We've recently made a very long spreadsheet with about 80 columns. BAD
things happen!

When people do a lazy sort - that is, ctrl home, Sort and use the populated
defaults, Excel only "selects" 64 columns. Of course this totally pooches
the data and creates disconnects.

No problems when the user selects the whole spreadsheet (by selecting the
upper lefthand corner), sorts and fills in the criteria themselves.

My question is, is this for real or am I imagining it? Is there a limit of
64 columns in Excel on that lazy sort? We're dealing with the workaround, I
just want to know I'm not crazy.

Karen F.

How do I sort area codes by time zones

I have a very large excel doc (well over 1,000 rows, by 30 something columns) that require formatting (resizing the row height) each row by hand. I have done all of that tideous work, but now I need to sort by multiple columns, and when I do that, the formatting of the cells is not maintained. (The size of the row stays, rather than moves with the data) Please tell me there is a remedy and I haven't just wasted my time formatting for nothing! Thanks for any and all advice.

How can I sort a worksheet in VBA on more than 3 columns?

I often sort a range of cells using the sort ascending arrows. Most of the
time the first row is not sorted due to Excel assuming that the first row is
a header.
How do I make the default "No header rows" so that the sort ascending works
without having to use Data, Sort

I've got chapter numbers in one column which goes: 4.1, 4.2, 4.3...., 4.11,
How can I sort it by the numbers on the right of the decimal point instead
of getting: 4.1, 4.10, 4.11, 4.12, ....4.2, 4.20, 4.21, ....

Is it possible to do an alphabetical sort while ignoring preceeding numbers
and symbols? That is, sort based on the first letters in a string of

Is there a formula that will allow me to sort a column based on the background color?


I have created a spread sheet with a numbering system. I would like it to be
permanently consecutive when I do a sort...is there a way to create a
permanent row that is not affected when I sort the rest of the document?

How can I make Excel 2003 (and Word 2003 too) to remember the sort order in
the File Open dialogue box?
Since a month ago or so, the File/Open dialog box keeps displaying files
sorted by date. I can sort them alphabetically then, but when I open Excel
again, say the next day, the files are sorted back by date.
Word is just not "remembering" my preferences.
Thanks for your responses to this Excel/Word nuisance rather than a problem.

After I select sort ascending from autofilter, is there anyway to sort back
to the original?

I have a shared file that is being used by some rather inexperienced Excel
users. This file will expand with extended use as each user enters their
data. In order to make it easier for them to use, I'm trying to automate some

Is there a way... and I'm sure there is, I'm just not experienced enough to
know how... that I can have my file automatically sort on a particular column
when the user goes to save it? I'm thinking that this must be either a macro
or VBA or whatever, but I am rather a novice in this area.

This may be a stupid question but I have a worksheet full of data and I
inserted a column a where I placed an x next to important data I wanted to be
able to pull to the top. I then sort my data by column a and excel pulls all
my x'd entries to the top. Nice, that's what I wanted. Now I play with my
data some more and print the first page with the important stuff and then I
want to go back to how my data was originally entered. I tried to go to data
-> sort -> sort by -> none but I get an error that I can't sort by none. So,
how do I get back to my original view of my data?

I have an Excel spreadsheet that contains 4,000 names and addresses of local
registered voters. The addresses are listed with the house number and street
(102 Oak Ave., eg) I want to sort the data so that all the people living on
the same street are together. Is there any way to do that in Excel, and if
not, is it possible to export the spreadsheet to Access and do it there. I
have tried several times to read the spreadsheet in as an Access data base,
but no luck. But I don't know if it is possible to do that even if I could
get the data base set up. Anyone got any suggestions?

How do I turn off the Sort Warning function in Microsoft Office 2003?

I know I have seen a similar question, but I did a search for "Sorting Multiple Criteria" and did not find what I am looking for.

Is there a way to use VBA to sort by more than three criteria?

Specifically four criteria...

Example: The Header row is row 2

Selection.Sort _
Key1:=Range("E2"), Order1:=xlAscending, _
Key2:=Range("H2"), Order2:=xlAscending, _
Key3:=Range("B2"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

What the operators would like is

Selection.Sort _
Key1:=Range("A2"), Order1:=xlAscending, _
Key2:=Range("E2"), Order2:=xlAscending, _
Key3:=Range("H2"), Order3:=xlAscending, _
Key4:=Range("B2"), Order3:=xlAscending, _
Header:=xlNo, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom

but this creates errors

I am trying to sort a very large list of email addresses, and I want to start
the sorts from the "@" symbol in each address. Is this possible?

Can Anyone Help?

Recently converted all data from an old program to Excel list of ex-students
for a reunion. New request is to sort by Maiden name for the women, and Last
Name for the men for the book. First sort - YearGrad; Second Sort - either
maiden or last name. Can I somehow say sort by maiden unless blank, then sort
by last name?
Working Mom (redundant wording)

I have 1 column with "item numbers" Which can be both numbers and

I wan't to sort them as numbers so that all numbers would come 1st and
then item numbers like KK1992991. The thing is that excel seems to
treat all cells like text fields, even thought I have formatted them as
number fields.

Now excel i.e. sorts 101 before numbers 11-99. I you get what I mean

Excel would sort cells ,1, 2, 10, 101, 111, 121, 399 like:

So how can I make excel understand I want it sort them like numbers :o


I have a pivot table and I have added new data to the data area. When I click
refresh the new data will not added to existing data rols on the pivot but
rather as a new number. For example of I have data showing sales in December
and I input new line items in the data source. I update my data source
parameters and hit refresh. When I do this two decembers are created. One
with the data already present and another with the new data. Why does it not
add all together and create one december?