Microsoft Excel – How to Apply a Function to All Values in Selected Cells

As the message indicates, you're trying to insert rows into a Table in your sheet. Typically, but not necessarily, tables will have banded formatting. When you click in a cell in a table, the Table tab will appear, like so:

The Insert and Delete commands get grayed out when there's more than one table intersecting the row you're trying to delete (and maybe other times):

I haven't figured out how to "Insert Copied Cells" into just a table row in one step. Instead I insert a row, or rows, into the table and then copy the content. To insert a row into the table, right-click a cell in the table and choose Insert:

You can turn the tables back into normal cells. Do it like this:

If the cells are selected already then just press CTRL + ENTER. You can also drag the square in the bottom right of the cell after you press ENTER if you forget.

Also, when specifying a cell. If you put a $ in front of either the column, the row, or both the column or row will remain the same for all items. If not then that row or column will be relative to the value in the current cell. For example, if you have A1:C4 selected and you enter =D1 into the formula for A1 and press CTRL + ENTER then the values in D1:F4 will be used in the corresponding cells. If you use =$D$1 then all cells will use the value in D1.

UPDATE

The only way that I'm aware of to do what you are wanting without using intermediate values is to use VBA. The basic function you want is simple. It will look something like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("A1:C100")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Target = UCase(Target)
    Application.EnableEvents = True
End Sub

Just replace the line Target = UCase(Target) with whatever it is that you want to do.