Continue to Site

Eng-Tips is the largest engineering community on the Internet

Intelligent Work Forums for Engineering Professionals

  • Congratulations waross on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

Thought that I mastered Excel... 2

Status
Not open for further replies.

Skogsgurra

Electrical
Mar 31, 2003
11,815
My spreadsheets are usually small and easily handled. Copying a cell down a column is quick and easy, just mark the cell, put cursor on lower right corner and drag down. Nothing to it.

But, I now have what I thought impossible in Excel, a sheet with 12 columns and 195729 rows of data. The 'drag corner down the column' method is very awkward. And I cannot find any other way to do it. Surely, there is another way - but how?

Signed
Tired index finger

Gunnar Englund
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...
 
Replies continue below

Recommended for you

If you want to select a continuous block of data:
Hold down shift.
end - down (press and release end key, then press down arraow key)
end - right (press and release end key, then press right arrow key)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
Let me add a step in bold
Place cursor in upper left hand corner of the block of data
Hold down shift.
end - down (press and release end key, then press down arraow key)
end - right (press and release end key, then press right arrow key)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
Another possibility (depending on what you're trying to do).
Ctrl-A

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
The first column you just gotta do; but once you rows 1-195729 in column A, you can write a formula in B1 and then with B1 highlighted, double click on the lower right corner of B1 and it will fill itself in to the last row of column A.
 
I misread the question. My previous responses were for selecting a range. I see now you are looking to copy down a cell. I haven't tried David's approach.

What I usually do is copy down the first column as David said. Then navigate to bottom of the first column (end-down). Then place an X in that bottom row continuing accross as many columns as I think I'll need. Then when I want to copy, I use end-down to get the relevant portion of the column.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
Thanks! Was AFKB for a while. Will have a go on it. Isn't Eng-Tips just good? :)

Gunnar Englund
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...
 
Here's the other way:
Suppose you want to copy formulas in cells D1:H1 down all way to D195729:H195729.
1. Select range D1:H1 and copy it (ctrl C).
2. Press function key F5 (goto)
3. In reference window type: H195729
4. While pressing Shift key click Ok button. The Range D195729:H195729 will be selected.
5. Press Ctrl-V (or edit/paste) to paste the formulas into selected range.
 
control arrow takes you in that direction to the end of data, if the first cell has data in it, or the last blank cell before data if it is empty.

So a typical sequence is

ctrl c
ctrl shift down

enter

which copies the first cell in a column all the way down to the end of contiguous data.



Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376
 
Thanks a lot. Of the methods shown, I think that the one proposed by davidbeach is absolutely superior. If you haven't tried it, here is how it works:

Put something into the first box in the column that you want to copy all the way down to end of spreadsheet. Double-click lower right corner. Magic!

Thanks.

Gunnar Englund
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...
 
Not working for me (Excel 2003).

Hg


Eng-Tips policies: faq731-376
 
Works for me in Excel 2003. Look for the cursor to turn into a thin "plus" sign indicates cursor is in the right position.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
Did that. Does nothing.

Hg


Eng-Tips policies: faq731-376
 
electricpete's method works for me on the sheet he provided. And I'm using Excel 97...

-- MechEng2005
 
I wouldn't worry too much about mastery; most people probably use less than 10% of the total capabilities of Excel.

TTFN

FAQ731-376
 
MechEng - my spreadsheet, but David’s suggested method.

I agree there are a lot of things hidden within excel. Who'd of thunk you can double click on the corner of a cell. Or hit "shift" while pressing ok to a dialogue box.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.
 
Works. I must have set up a stupid test file.

Thanks!

Hg

Eng-Tips policies: faq731-376
 
You can use this for basic series in the first column too:

Home-fill-series... (at the end of the ribbon).
enter columns, and the increment, and 'to' 195729.

Then do the double click thing in the next column on your first cell formula.

PS. did you really need this many rows, or just wanted to enjoy the new real estate in Excel 2007?
 
Yes Kiwi, I needed it.

Am doing a test on kWh-meters and have sampled U and I in all three phases, then also recorded the metering pulse. Sampling rate is 3300 Sa/s on each channel and time is 1 minute. I am now letting Excel calculate instantaneous power and accumulated power and compare to the metering pulses.

It seems that the kWh meter does funny things when power is cycled on/off with short intervals during this minute. Can have as much as 15 percent error - too much, most of the customers think.

I was thinking of using Matlab, but decided to test Excel. Really surprised to see that there is room for all these data.

Gunnar Englund
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor