Excel – Apply one formula to all selected cells

microsoft excelworksheet-function

So in Excel 2013 (v15.x), is there still no one-step way to apply an arbitrary formula to all selected cells or am I just missing it? The closest thing I see here on search is the answer to this question:

How do you apply a function to all values in selected cells?

…in which you select a number in one cell, then Paste Special to a new range of cells, and within Paste Special you can select Add, Subtract, Multiply or Divide.

But often I need a complex formula in which the only variable may be the cell itself. One application is to prepare a received sheet for import to SQL or some other context.

So to modify a range of cells in Column A (say, swap first/last words within each cell and substitute punctuation), I do all of the following steps:

  1. Insert a new Column B next to A. (Or work in an existing blank area of the sheet.)

  2. Put my formula in B1 referencing A1, for instance.

  3. Drag-fill the formula as far down Column B as needed and verify results are as expected.

  4. Ctrl-C to select the now-already-highlighted range in B.

  5. Right-click the first cell of the corresponding range in A.

  6. Select Paste Special > Values and verify results.

  7. Delete Column B (or whatever intermediate range I used).

Now certainly the number 7 has several historically revered magical properties, but I keep thinking this can't possibly be the right or simplest way in 2015, in the market-leading spreadsheet, to do something I need so often. Doesn't everybody else? I've got to be overlooking a shortcut that everyone knows except me.

What if it's just a simple built-in function like UPPER()? Any way to do that to a selection without creating an intermediate copy or a macro?

If somehow it's not just me, do any of the alternatives like LibreCalc provide a straightforward shortcut?

ON EDIT: Re-reading my question after seeing one answer, I suspect I wasn't clear enough that the purpose of applying the formula to e.g. cell A14 (among many) is to compute a new value for A14 derived from the original value of A14 and leave only a value (not a formula) in the cell. For instance the value of each cell might become value&"-obs" i.e. ("ABC" becomes "ABC-obs") or something more complicated than that. But I guess the essence of the problem is handling intentional self-reference to 1 iteration, storing the value of a formula and not the formula itself.

Best Answer

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.

Related Question