×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Thought that I mastered Excel...
2

Thought that I mastered Excel...

Thought that I mastered Excel...

(OP)
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
www.gke.org
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...

RE: Thought that I mastered Excel...

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.

RE: Thought that I mastered Excel...

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.

RE: Thought that I mastered Excel...

Another possibility (depending on what you're trying to do).
Ctrl-A

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

RE: Thought that I mastered Excel...

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

RE: Thought that I mastered Excel...

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.

RE: Thought that I mastered Excel...

(OP)
Thanks! Was AFKB for a while. Will have a go on it. Isn't Eng-Tips just good?  smile

Gunnar Englund
www.gke.org
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...

RE: Thought that I mastered Excel...

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.
 

RE: Thought that I mastered Excel...

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: Eng-Tips.com Forum Policies  http://eng-tips.com/market.cfm?

RE: Thought that I mastered Excel...

(OP)
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
www.gke.org
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...

RE: Thought that I mastered Excel...

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.

RE: Thought that I mastered Excel...

electricpete's method works for me on the sheet he provided. And I'm using Excel 97...

-- MechEng2005

RE: Thought that I mastered Excel...

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

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Thought that I mastered Excel...

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.

RE: Thought that I mastered Excel...

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?
 

RE: Thought that I mastered Excel...

(OP)
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
www.gke.org
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...

RE: Thought that I mastered Excel...

2007 brought the ribbon, a 1048576 row x 16224 column playing field (annoyingly just shy of 30 sec samples over a year) and the awesomely more compact open office XML file format.
  

RE: Thought that I mastered Excel...

FWIW, around here we use a program called Origin instead of Excel when we are processing that much data.  We do that often.  

John D
 

RE: Thought that I mastered Excel...

(OP)
I have to stick with something that my customer has available. So I am limited to Excel and Matlab. Open Office wold probably work just as well. But this particular customer doesn't think it is any good if it doesn't cost.

Gunnar Englund
www.gke.org
--------------------------------------
100 % recycled posting: Electrons, ideas, finger-tips have been used over and over again...

RE: Thought that I mastered Excel...

If you need more than 64,000 rows Open Office won't do it (not without fiddling with the source code and re-compiling anyway).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Thought that I mastered Excel...

Purely out of curiosity, does the latest Excel allow scales for each data series?

JMW
www.ViscoAnalyser.com

 

RE: Thought that I mastered Excel...

I was going to suggest Matlab; I really liked it for large swaths of data back when I didn't have to pay for the software.

Hg

Eng-Tips policies:  FAQ731-376: Eng-Tips.com Forum Policies

RE: Thought that I mastered Excel...

jmw - I'm not sure what you mean.  You can plot selected data sets on a secondary Y axis, and also use a secondary X axis (but only if you have already selected a secondary Y axis).

The procedure is far from obvious, but it's reasonably well described in the help.

http://office.microsoft.com/en-au/excel/HP012341651033.aspx#SecondaryVerticalAxis

If that isn't what you meant, please let us know.

 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Thought that I mastered Excel...

HgTX - try Octave. Stable, free, very matlab like. It isn't quite as fast and lacks alot of the add-ons and toolboxes, but for basic number crunching it is fine. I develop most of my data handling and fft analysis at home using octave and then take it in to work and run it in matlab.

 

Cheers

Greg Locock


New here? Try reading these, they might help FAQ731-376: Eng-Tips.com Forum Policies  http://eng-tips.com/market.cfm?

RE: Thought that I mastered Excel...

I assume that jmw was asking about 5 different scales for 5 different data series.  Certain programs can do that; Excel is not one of them.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

RE: Thought that I mastered Excel...

IRstuff - I'm sure it could be done in Excel (with a bit of time and effort), but I'm not sure why you'd want to.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

RE: Thought that I mastered Excel...

Attached is an excel file I created to "work around" the limitation on scales.

Each plot can be "normalized" by dividing by a scale factor (row 29).  The scale factor is displayed in the legend.  The plot goes from –1 to 1 for all variables

There is also some features built in for easy browsing of a large pile of data... time axis can be changed easily by typing in the green cells "tmin" and "tmax".

http://home.comcast.net/~electricpete/eng-tips/PlotInteractiveRev4.xls
 

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources