Not sure what you mean by this:
Do you mean you want to insert an entire row wherever you added the new value (i.e. across all columns on the sheet) or do you just mean you want this to apply to all columns in the sheet & just move that column down (where you made the change)? In either case, put the code in the worksheet module of the sheet in question by right-clicking the sheet tab name, select View Code, and place the code in the window that appears on the right of screen. You'll need to save your file as macro-enabled thereafter.
If you want all columns to move down use this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge = 1 Then
Dim OldVal As String, NewVal As String
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.EntireRow.Insert shift:=xlDown
Target.Offset(-1) = NewVal
Application.EnableEvents = True
End If
End Sub
If you just want that one column to shift down use this:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge = 1 Then
Dim OldVal As String, NewVal As String
Application.EnableEvents = False
NewVal = Target.Value
Application.Undo
OldVal = Target.Value
Target.Insert shift:=xlDown
Target.Offset(-1) = NewVal
Application.EnableEvents = True
End If
End Sub