Nintendo Switch review: Hands on with the intuitive modular console and its disappointing games…
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...)
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.
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.
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
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
Then press CTRL+SHIFT+ENTER together.
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.
...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.