Article updated on January 09, 2018

awall asks:

Hey can you do the opposite of this - not random order but this is my situation.
i have 3 rows
A1 - Item Number
B1 - Description
C1 - Price
These will constantly be having new numbers put in. is there a way to have it when you add a number into the A coloum it will automatically sort into numerical order? Or can i create a button that after i input all my data click it and it updates all 3 rows into the numberical order based on row A

Create a table

  1. Select cell range A1:C9
  2. Go to tab "Insert"
  3. Click "Table" button
  4. Click OK

Recommended article

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

Sort values

The following animated image shows you how to sort values in an excel defined table.

The following article demonstrates how to sort a column using a formula:

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

Automatically sort values (vba)

The macro below sorts a table automatically (column A) when all cells in the current row have a value. The animated picture below explains it all.

VBA code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ACell As Range
Dim ActiveCellInTable As Boolean
Dim r As Single
Dim Value As Variant
Set ACell = Target
On Error Resume Next
ActiveCellInTable = (ACell.ListObject.Name = "Table1")
On Error GoTo 0
If ActiveCellInTable = True Then
    r = Target.Row - Target.ListObject.Range.Row + 1
    For c = 1 To ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Columns.Count
        If ActiveSheet.ListObjects(ACell.ListObject.Name).Range.Cells(r, c).Value = "" Then Exit Sub
    Next c
    With ActiveSheet.ListObjects(ACell.ListObject.Name).Sort
        .SortFields.Add _
            Key:=Range("Table1[[#All],[Item number]]"), SortOn:=xlSortOnValues, Order _
            :=xlAscending, DataOption:=xlSortNormal
    End With
End If
End Sub

Download excel *.xlsm file

Sort a table (vba).xlsm