0

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.

0

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.

  • BTW, a bit of trivia, this feature has often been credited as being the first killer app feature for PCs. en.wikipedia.org/wiki/VisiCalc – krowe Jan 7 '15 at 0:29
  • OK, so Ctrl-Enter is a more flexible alternative to drag-filling because unlike dragging the fill handle, it can fill discontiguous selections. But what about the self-referencing problem? If I want to suffix the contents of every selected cell in A with "-obs", using my 7-step method I give B1 the formula =A1&"-obs" then proceed through the rest of the steps to eventually "Paste Values" my results from B back into column A. How would I apply that formula directly so that each cell ends up not with a circular formula but with a value (only) from applying the formula to it once? – Timberline Jan 7 '15 at 9:08
  • OK, I'll see about trying your update tomorrow and get back. I guess ideally I'd want to create a popup that prompts for an expression and assigns Target = Evaluate(expr). – Timberline Jan 8 '15 at 2:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.