Excel - can I create multiple equations at once?

  madwab 15:11 30 Jul 03
Locked

If I have a function or equation I would like to apply to several rows, I can put it into the first row and then ctl-d or copy/paste it to the other rows. Is there any way of reducing this process from two easy steps to one easy step?

(Seems a bit lazy, but I do these 2 steps dozens of times a day and sometimes on sheets with 10s of 1000s of rows...)

Thanks

  seedie 15:24 30 Jul 03

Select the cell which you want to copy from.In the bottom right hand corner there is a small black square.Left click on that and drag along as far as you want.

  madwab 11:47 01 Aug 03

That only works when there's already a formula in the start cell. I want to highlight a range of cells, THEN type in the formula and have it applied to all selected cells.

  Peverelli 12:10 01 Aug 03

If I understand you correctly then maybe a macro would help.

Run the macro recorder


Type in cell A2 "=$A$1" (without quotes)

then copy A2

then highlight the cells you wish to fill and then paste.


Stop the macro recorder and give it a key combination (ie Ctrl + S).

Then you can put the formula in A1

I don't have Excel any more so I can't test if I've missed something out.

  stlucia 12:32 01 Aug 03

It's difficult to answer without knowing why you want to do it the way you've suggested -- as seedie says, you would normally type the formula in once, and then drag it to however many cells you apply it to.

The problem with the solution that peverelli has offered is, I think, that it copies exactly the same formula down without adjusting it for what row or column its in: What I mean is, if the formula in cell a2 is "=a6/2" for example, when you apply it to row b you normally want it to read "=b6/2". Seedie's method automatically changes the references. Peverelli's doesn't

  VoG II 14:23 01 Aug 03

You can do this by using an array formula.

Say you have numbers in A1:A5 and you want to take their square roots.

Select B1:B5 and type in

=SQRT(A1:A5)

Then press CTRL+SHIFT+ENTER together.

  Peverelli 01:52 02 Aug 03

I've assumed that madwab wanted the same formula in each cell. If not, then leave the '$' out. Better still is to take our old friend VoG's advice.

I Must reinstall excel, StarOffice just doesn't cut the mustard when it comes to spreadsheets.

  madwab 15:35 04 Aug 03

...VoG's solution, tho' not perfect, is the closest to what I want, and I'll certainly use it.

Further explanation for those who wondered what I'm trying to achieve. Imagine dealing with several ad hoc spreadsheets every day. Each one needs analysing in a different way - so I can't give details of formulae, end result etc, because there are any number. So several times a day I insert a column, type an equation into row 1 (if no header) or 2 (header), then scroll down to the bottom and ctrl-d (easier, for 1000s or rows, than click and drag).

The point is that although those 2 steps aren't exactly onerous, when done repeatedly the time taken is worth saving. Computers are good at saving time, but in this instance Excel doesn't seem to be quite good enough.

Maybe it will be clearer if I describe what I would LIKE to happen. I highlight a column, type in my formula at the top of the screen, and it is automatically inserted into every row which already has data in it. I don't have to know or care how many rows there are.

This thread is now locked and can not be replied to.

Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…

1995-2015: How technology has changed the world in 20 years

Here's what should be coming to Adobe Project Felix in 2017

Best iMovie for Mac tips 2017 | How to edit video with iMovie: 26 brilliant video editing tricks…