Excel - can I create multiple equations at once?

  madwab 15:11 30 Jul 2003
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 2003

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 2003

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 2003

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 2003

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 2003

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 2003

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 2003

...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.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires