One of the best features of Excel is that it’s quick and easy to sort columns of data. From what I’ve seen, sorting is one of the first things most people learn to do, when they’re getting started in Excel.
Sort Across Row
Usually, we sort Excel data based on a column’s values, but there are other options available for you.
You can even sort data in an Excel row, left to right, by changing one of the sort options.
This method sorts the columns in the entire list of data, based on the values in the selected row.
To see an example, watch the short video in the next section.
Video: Sort Data By Row Values
In this example, I show how to sort a table of monthly sales, so the month with the largest sales total is at the left. To do this, I used a right-click popup menu, for a custom sort.
Sort Multiple Rows of Data With a Formula
In a comment on the Sort a Row in Excel 2010 blog post, Debbie asked about sorting 2000 rows, individually, left to right.
She didn’t say they were lottery numbers, but her example, shown below, sure looks like that to me.
Sort With Formulas
One way to sort the rows is to use a formula, in columns to the right.
In the screen shot below, cells H1:M1 are selected, and this SMALL formula is entered:
=SMALL(A1:F1,{1,2,3,4,5,6})
Then, to array-enter the formula, press Ctrl+Shift+Enter
The formula sorts the 6 numbers from A1:F1, in ascending order, and shows the results in the 6 cells where the formula was array entered — H1:M1
Copy Formula Down
After you enter the formula in row 1, select cells H1:M1
Then, point to the fill handle at the bottom right of the selected range
When the pointer changes to a black fill handle, like the one in the screen shot below, drag down to the last row of numbers
Then, release the mouse button, and each row’s formula shows the row’s original numbers in ascending order.
Change to Values
After the formulas have been copied down to the last row of data, you can leave them as is.
Or, as a final step, you could copy the columns of formulas, and paste them as values. This short video shows a different Excel workbook, where I used a mouse shortcut to copy formulas, and paste them as values.
Sort Multiple Rows with a Macro
If you don’t want to mess with formulas, you could use a macro to sort each row, left to right.
The following macro, from Dave Peterson, will sort all the rows on the active sheet, starting in row 1, and assumes there are 6 columns of numbers.
Make a copy of your original worksheet, before sorting with the macro.
Sub SortLotteryRows()
'posted by Dave Peterson
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long
With ActiveSheet
FirstRow = 1 'change to 2 if there are headings
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For iRow = FirstRow To LastRow
With .Cells(iRow, "A").Resize(1, 6)
.Sort Key1:=.Columns(1), _
Order1:=xlAscending, _
Header:=xlNo, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlLeftToRight
End With
Next iRow
End With
End Sub
Other Row Sorting Ideas
Do you have any other ideas for sorting lots of rows? Please share your ideas in the comments.
And for more Excel sorting tips, go to the Excel Sorting page on my Contextures site.
_____________
Debra,
It’s hard to go wrong with any code from Dave Peterson.
Also, your advice to:
“Make a copy of your original worksheet, before sorting with the macro.” should be heeded.
The following very similar code to Dave’s, allows one to sort rows in the selection.
(instead of a fixed location)
Also, the code might run a little faster as it eliminates the use of the Resize property.
It sorts ascending unless one specifies a descending sort.
Do that by adding the number 2 or the expression “xlDescending” to the GetMeStarted sub…
This sorts ascending… Call SortRowsAcross
This sorts descending… Call SortRowsAcross(xlDescending)
This also sorts descending… Call SortRowsAcross(2)
‘–code starts–
Sub GeMeStarted() Call SortRowsAcross End Sub Function SortRowsAcross(Optional ByRef lngDirection As Long = 1) Dim rngAll As Range Dim rngRw As Range Set rngAll = Selection For Each rngRw In rngAll.Rows rngRw.Sort key1:=rngRw, Order1:=lngDirection, header:=xlNo, _ MatchCase:=False, Orientation:=xlLeftToRight Next ‘rngRw End Function‘–code ends–
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
Thanks Jim!
Critical note. When sorting, make sure you sort the entire block of data. If you sort only some columns of a range, you’ll never be able to piece those rows together again.
There is an error in the array formula SMALL(A1:F1,{1,2,3,4,5,6})
The correct formula is SMALL(A1:F1,{1;2;3;4;5;6}) and Ctrl+Shift+Enter
Yes, if your regional settings use a semi-colon as the separator, you’ll have to change the formula to use those.
still do not work. sorry somebody knows why.?
Just a small comment on the procedure of using a formula to sort, I tried following instruction of using =SMALL(A1:F1,{1,2,3,4,5,6})
selecting multiple cells and Ctrl+shit+enter. It does not work.
Here is what does work, select cell “H” then add formula “small” select the range(A1:F1), then return 1, go to cell “I” and add formula and range, using return 2, same for J(3),K(4),L(5)&M(6). Each cell of the 6 is done separately and each returning a different position from 1-6, ie: 1st, 2nd, 3rd etc. for smallest values.
Then it will return the proper values and sort the numbers in order.
After that then you can drag the formula down the column and it will sort each set(row)in order.
And as mentioned, to keep the values, use select all and copy and paste special values when done for the array.
yes, this works perfectly
Yes.. indeed.. First Cell,SMALL(A1:F1,{1;2;3;4;5;6}), Second Cell,SMALL(A1:F1,{2;3;4;5;6}) Third,SMALL(A1:F1,{3;4;5;6}) etc… worked perfectly for me.. Thanks a whole bunch..
I used the sort formula as written in Excel 2010 and it worked like a charm. Excellent post and thanks so much!
Also, i am finding out the hard way that it will not sort using the formula if your array is stored in text format… i’m trying to find a way to convert all as numbers (it won’t let me by right button clicking and choose format) ie. even though it changes to ‘numbers’ format, it still won’t actually change to numbers… i’d have to click on each individual cell and actually convert each to numbers! grrrrr.
Hello.
Pls how can I sort list like this.
Item Unit Item Price
Pencil 2 Book $100
Pen 1 Pen $200
Book 3 Tape $240
Eraser 8 Eraser $50
Tape 1 Pencil $500
Expected result
Item Unit
Pencil 2 $100
Pen 1 $200
Book 3 $240
Eraser 8 $50
Tape 1 $500
Thanks.
Hi Debra
Your Row Sorting Formula Works Great.
Though I Have Numbers Like This To Sort.
22-34567, 333-7465, 876457689, 66-66970877 (A1,B1,C1,D1)
These Are Phone Numbers Which I Have To Short For Small To Large Or Large To Small
I Need Formula To Ignore (-)
Your Formula Works Only On Without (-) Numbers.
Could You Please Help Me
Thanks & Regards
Babulal Gandhi
I Have Phone Numbers Like This To Sort By Rows
66-66970877, 22-34567, 8764576890, 333-7465
(Column A1,Column B1,Column C1,Column D1)
These Are Phone Numbers Which I Have To Short For Small To Large Or Large To Small
Sorting Data In Row P1 To S1
22-34567, 333-7465, 66-66970877, 8764576890
(Column P1,Column Q1,Column R1,Column S1)
Column P1 = small($a1:$d1,1),
Column Q1 = small($a1:$d1,2),
Column R1 = small($a1:$d1,3),
Column S1 = small($a1:$d1,4)
These Formula Works Great On Without (-) Numbers.
I Need Formula To Ignore (-)
Numbers Before (-) Is Area Codes. If I Remove (-) With Find & Replace. I Will Get Confuse In Future As I Can’t Differentiate Area Code And Phone Number.
If There Is Solution In Macro
I Want To Short Telephone Numbers In Three Rows After Mobile Numbers In Other Three Rows.
P1-Telephone Number, Q1-Telephone Number, R1-Telephone Number, S1-Mobile Number, T1-Mobile Number, U1-Mobile Number
I Need A Macro
Ignores The (-) Value In All Phone Numbers
Check First Digit Of Phone Numbers.
If First Digit Start’s With Nine(9). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Nine(9)(It’s Mobile Number) Put Into S1. Else If There’s Two Numbers Starting With Nine (9) Place It In Row S1 And T1 In Ascending Or Descending Order.
If First Digit Start’s With Eight (8). If There Is Any Single Phone Number In A1, B1, C1, D1 Starting With Eight (8) Put Into Q1. Else If There’s Two Numbers Starting With Eight (8) Place It In Row Q1 And R1 In Ascending Or Descending Order.
Like Wise For Other Digits Too
Thanks & Regards
Babulal Gandhi
How to sort a rows of numbers (lottery)from one to another worksheet. For example sheet one would have the draw numbers, as new draws are entered, they would be sorted to the sheet corresponding to
the number in the first cell.
2/10/2021 1 7 16 31 34——sheet number 1
2/11/2021 4 10 18 30 36——sheet number 4
2/12/2021 13 20 31 34 35——sheet number 13